👋 Hello and thank you! 😊

looking for a calculation to properly concatenate address/suite/city/state/zip fields with punctuation on one line
ie: 1235 Alley Way, Suite A, Dreamcity, 95991 CA

also looking to avoid goofy results if there is any missing data
ie: ,, Suite A, Dreamcity, 95991, 

with my limited knowledge I'm currently using:

Case(not IsEmpty(address); address & ", ") & Case(not IsEmpty(suite); suiteTitle & " " & suite & ", ") & Case(not IsEmpty(city); city & ", ") & Case(not IsEmpty(state); state & " ") & Case(not IsEmpty(zip); zip)

which may work if I could trim off the ", " at the end if ever it is hanging out at the end.

Try something along the lines of:

Substitute ( List ( StreetAddress ; Suite ; City ; Trim ( State & " " & ZIP ) ) ; ¶  ; ", " )

 

Works beautifully! Thank you!.. tho I do not fully understand how its working, lol.

I understand the Substitute and List function, but on the Trim, is the function only looking at fields State and Zip?

...also, I have fields suite and suiteTitle, so what if I did not want result Suite, H, but rather Suite H

I like the idea of breaking down the address2 into selectable titles for the user. But
if too messy, I can convert to using an Address2 field where user will have to type the entirety of the address2 in, ie: APT/SUITE/BLD/ETC 123

1 hour ago, Dr. Evil said:

I do not fully understand how its working

It's taking advantage of the fact that the List() function skips empty values.

1 hour ago, Dr. Evil said:

on the Trim, is the function only looking at fields State and Zip

Yes. If both fields are filled, you get both, separated by a space. If only one is filled, the space is trimmed away. If both are empty, the entire value is empty and therefore skipped.

1 hour ago, Dr. Evil said:

I have fields suite and suiteTitle

Do the same thing with Trim().

 

Thank you Comment, working like a dream!