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

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
 

Becky

Webmistress
Joined
Mar 25, 2003
Messages
7,424
Reaction score
1,511
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
Joined
Mar 25, 2003
Messages
7,424
Reaction score
1,511
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

Joined
Jul 22, 2016
Messages
3
Reaction score
2
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
Joined
Mar 25, 2003
Messages
7,424
Reaction score
1,511
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),""))

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top