Excel Concatenate with line breaks but ignore blank cells


Joined
Feb 23, 2019
Messages
2
Reaction score
1
Hi,

My worksheet has 3 columns, with numbers. I'm trying to concatenate all 3 columns into a 4th column.

Row A column B column C column
1 11111 33333 (blank)
2 2222 (blank) 55555
3 (blank) 44444 66666

The final column D should look like
D1 11111
33333
D2 22222
55555
D3 44444
66666

In essence, I'm trying to combine the values in first three cells with a line break in between the values. However, I want to ignore the blank cells. My research says it works with Textjoin function but our systems use 2013 excel - so thats not an option. I've tried several workarounds with substitute and trim functions, but I'm not getting the desired result.

Please help.
 
Ad

Advertisements

Becky

Webmistress
Joined
Mar 25, 2003
Messages
7,424
Reaction score
1,508
Welcome to the forum :)

You could combine CONCATENATE with IF functions and the ISBLANK formula to do this. For example:

=CONCATENATE(IF(ISBLANK(A1),"",A1)," ",IF(ISBLANK(B1),"",B1)," ",IF(ISBLANK(C1),"",C1))

The formula looks at each cell in turn and asks whether it is blank; if it is then nothing is returned, if it contains data then the data is returned. The " " just inserts the blanks between the items. Hope that helps!
 
Ad

Advertisements

Joined
Feb 21, 2018
Messages
209
Reaction score
85
alternatively

replace all your blank cells with a unique symbol(s) say "⅙⅙"
now use Becky's formula with slight modification/simplification.

=replace(CONCATENATE(A1," ",B1," ",C1), "⅙⅙","")
 

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