Remove blank fields lines in CONCATENATE Address formula

[Mike_Ashton]

Hi all,

[Spoiler: I’m not an IT professional and am pretty new to AT, so thank you in advance for bearing with me :slightly_smiling_face: ]

I have made a ‘contact’ base to hold client addresses, with the following fields (these need to be split out this way for other operations elsewhere);

Address 1
Address 2
Town/City
County
Postcode

Very often, one or two of these are vacant (usually Address 2 and/or County). Postcode might be, too.

Ideally I also need to show an address as a single line, with the fields separated by commas. When I use the formula;

CONCATENATE({Address Line 1}, ", ", {Address Line 2}, ", ",{Town/ City}, ", ",(County), ", ",(Postcode))

I get commas between the empty fields;

Is there a way to skip the empty fields to give the output format;
Addr1, City, Postcode, if those are the only fields populated?

Thanks for any help in advance :slightly_smiling_face:

Mike

[Kamille_Parks]

Try this:

{Address Line 1} & 
IF({Address Line 2}, ", " & {Address Line 2}) &
IF({Town/ City}, ", " & {Town/ City}) & 
IF({County}, ", " & {County}) & 
IF({Postcode}, ", " & {Postcode})

[Mike_Ashton]

Thank you so much Kamille! Works perfectly.

Now I can spend tomorrow figuring how that works :slightly_smiling_face:

Thanks again - very much appreciated.

Mike

[Kamille_Parks]

Explanation:

• Every line except the last one ends with an ampersand to append the rest of the formula “steps” in the same way that CONCATENATE() appends each comma-separated argument together.
• Each IF() statement asks “does this particular field have a value?”
  • If the field does have a value it appends ", " and then the field value.
  • If the field does not have a value it appends “nothing”. This is because the IF() statements don’t have the third argument declared: IF([scenario], [value if true], [value if false]). When the [value if false] argument is omitted from an IF() statement, the formula uses blank space as the value, which in this case is what you want.

[Mike_Ashton]

That is so kind of you Kamille, thank you :thumbs_up: :clap: