Concatenate with line break but only if text exists

[jamiek]

Hi,

I am trying to combine shipping address information but some cells are blank e.g. some addresses have a company name, some don't which leaves an empty line.

I am using the below formula.

=A2 &CHAR(10)& B2 &CHAR(10)& C2 &CHAR(10)& D2 &CHAR(10)& E2 &CHAR(10)& F2

How can I make it so if a cell is empty that it doesn't include it resulting in an empty line?

Cheers,
J

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have xl2019 or newer you could use textjoin

 

[Snakehips]

If you do have TEXTJION then. like.

=TEXTJOIN(CHAR(10),TRUE,A2,B2,C2,D2,E2)

Otherwise in older Excel versions you could try

=SUBSTITUTE(SUBSTITUTE(A2&"^"&B2&"^"&C2&"^"&D2&"^"&E2,"^^",""),"^",CHAR(10))

Hope that helps.

 

[jamiek]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have xl2019 or newer you could use textjoin

Ok thanks updated my profile it is 16.34.

 

[Fluff]

Thanks for that, you can either use the textjoin formula provided by Snakehips, or this one

=TEXTJOIN(CHAR(10),,A2:E2)

 

[jamiek]

If you do have TEXTJION then. like.

=TEXTJOIN(CHAR(10),TRUE,A2,B2,C2,D2,E2)

Otherwise in older Excel versions you could try

=SUBSTITUTE(SUBSTITUTE(A2&"^"&B2&"^"&C2&"^"&D2&"^"&E2,"^^",""),"^",CHAR(10))

Hope that helps.

Thanks Snakehips! The TEXTJOIN formula is working a treat