# ExcelConcatenate addresses in Excel but avoiding blanks, and line breaks

#### NHT

I have a list of addresses, in different columns but some columns are blank, how do I concatenate the addresses and use line breaks too.

E.g.

A1: 2 Smith Close
A2: lslington
A3: London
A4: SE1 4BQ

So it would end up looking like this in a new column:

2 Smith Close
lslington
London
SE1 4BQ

B2: 3 Jones Street
B2: London
B3:
B4: SE2 3BQ

Then I would want it to look like this in new column

3 Jones Street
London
SE2 3BQ

Or if column 4 was blank, so it would also look like:

B2: 3 Jones Street
B2: London
B3: SE2 3BQ
B4:

3 Jones Street
London
SE2 3BQ

Thanks

#### Becky

##### Webmistress
Welcome to the forum!

In excel, CHAR(10) returns a line break, so all you need to do is insert it into your formula where you want it.

For example, =CONCATENATE(A1,CHAR(10),A2,CHAR(10),A3,CHAR(10))

If you want it to only do a line break following text, then you could perhaps use an IF function (ie IF there is text in the cell the result is CHAR(10)).

Hope this helps!

#### Becky

##### Webmistress
P.S. You also need to make sure you have the formatting set to 'Wrap text' otherwise it won't appear on different lines.

#### NHT

Thanks Becky for your reply. Would you be able to tell me what the formula is to combine the 'IF' function with the concatenate function in this instance? Sorry, I don't have much knowledge with Excel.

#### Becky

##### Webmistress
Sorry for the late reply, been busy doing DIY!

Would you be able to tell me what the formula is to combine the 'IF' function with the concatenate function in this instance?

No worries, here's an example:

=CONCATENATE(IF(A1<>"",A1&CHAR(10),""),IF(A2<>"",A2&CHAR(10),""),IF(A3<>"",A3&CHAR(10),""),IF(A4<>"",A4&CHAR(10),""),IF(A5<>"",A5&CHAR(10),""))

Basically, each item to be concatenated is an IF function. An IF function basically has a test, and if the test is TRUE it returns one thing, if the test is FALSE it returns something else. The test part of each one (eg A1<>"") means "if A1 is not equal to blank", or to put it differently, "if there is something in the cell".

Therefore if the answer is TRUE, it will return the next part of the IF function - eg A1&CHAR(10) - which means it will return the value of cell A1 and add a line return after it.

If the cell is blank (ie the test is FALSE) then it returns "" - ie, blank.

Hope this helps! Let me know if it works for you

NHT

#### NHT

Thanks for your help Becky. It worked!