Excel Concatenate addresses in Excel but avoiding blanks, and line breaks

N

NHT

Joined
Jul 22, 2016
Messages
3
Reaction score
2
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

But if I had:

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
 
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!
 
P.S. You also need to make sure you have the formatting set to 'Wrap text' otherwise it won't appear on different lines.
 
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.
 
Sorry for the late reply, been busy doing DIY!

NHT said:
Would you be able to tell me what the formula is to combine the 'IF' function with the concatenate function in this instance?
Click to expand...

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),""))

Where your address data is in cells A1 to A5.

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 :)
 
Back
Top