Skip to main content

Hi all,



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 🙂


Mike

Try this:


{Address Line 1} & 

IF({Address Line 2}, ", " & {Address Line 2}) &

IF({Town/ City}, ", " & {Town/ City}) & 

IF({County}, ", " & {County}) & 

IF({Postcode}, ", " & {Postcode})

Try this:


{Address Line 1} & 

IF({Address Line 2}, ", " & {Address Line 2}) &

IF({Town/ City}, ", " & {Town/ City}) & 

IF({County}, ", " & {County}) & 

IF({Postcode}, ", " & {Postcode})

Thank you so much Kamille! Works perfectly.


Now I can spend tomorrow figuring how that works 🙂


Thanks again - very much appreciated.


Mike

Thank you so much Kamille! Works perfectly.


Now I can spend tomorrow figuring how that works 🙂


Thanks again - very much appreciated.


Mike


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(rscenario], Fvalue if true], lvalue if false]). When the hvalue 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.



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(rscenario], Fvalue if true], lvalue if false]). When the hvalue 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.




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

Reply


Terms & Conditions