Excel Concatenate with line breaks but ignore blank cells

[deepakhshahani]

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.

 

[Becky]

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!

 

[deepakhshahani]

Hi Becky,
Thanks for the welcome. Also appreciate your help in replying.

 

[AmjiBhai]

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