Addresses With 4th Line (within a table, not a block)

Kelly_Green · ‎Jun 17, 2018

Scenario —

Tables are listed as such:
Address
Address 2 (for Suite number, Apartment, etc.)
City
ST
Zip

I’m making a formula in a table. If I make the formula

{Address} & “,” & {Address 2} & & " | {City} & “,” & {ST} & " "

Result:
123 Any St., Suite 2 | City, ST 12345

Which is what I wanted. But…

I’m struggling with the result if there is no Address 2.

When there is NO Address 2 I get

123 Any St., | City, ST 12345

That comma after the Address is incorrect, but I can’t figure out how to IF/THEN BLANK/NOTBLANK it away.

If I use

{Address} & " " & {Address 2} & " | " & {City} & ", " & {ST} & " " & {Zip}

Result: 123 Any St. Suite 2 | City, ST 12345 with Address 2. It has no comma between the St. and the Suite when there is a Suite, and it is needed.

Any suggestions appreciated!

Jeremy_Oglesby · ‎Jun 17, 2018

{Address} & IF({Address 2}, “, “ & {Address 2} & “ “, “ “) & “| “ & {City}...

Kelly_Green · ‎Jun 17, 2018

Sadly it’s still not working. I’m not sure what’s going on on my end. “Sorry, there was a problem saving this field. Invalid formula. Please check your formula text.” It looks perfect… THANK you for trying.

Jeremy_Oglesby · ‎Jun 17, 2018

If you copied and pasted it, make sure all the quotation marks in your pasted formula are straight up and down (dumb quotation marks) rather than curved (smart quotation marks). Airtable formulas only accept dumb quotation marks. If they appear curved, replace them alll with your quotation mark keystroke.

Secondly, I presume you are filling in the “…” in my formula with the rest of your formula, yes?

Other than that, I see no reason why it wouldn’t work.

Kelly_Green · ‎Jun 17, 2018

I dumbed down the quotes to straight up an down, completed the formula… IDK why it’s not working. It’s a conundrum.

Kelly_Green · ‎Jun 17, 2018

Happy Father’s Day, btw. Your pic shows you probably have a great reason to celebrate.

Jeremy_Oglesby · ‎Jun 17, 2018

Thank you! :grinning:

I tried this out and it worked just fine for me. You must have some small typo somewhere:

Double check your formula for smart quotes, missing commas, missing ampersands, etc.