How to ignore blank cells while concatenating cells in Excel

Question

I am trying to concatenate multiple cell values in one cell as below:

Column A  Column B  Column C  Column D  Column E  Column F
Blank     Text 1    Text 2    Blank     Text 3

I am concatenating values from A to E

My formula is

A1&CHAR(10)&B1&CHAR(10)&C1&CHAR(10)&D1&CHAR(10)&E1

What I am getting in Column F is

Blank
Text 1
Text 2
Blank
Text 3

What I am looking for is

Text 1
Text 2
Text 3

Can anyone help me with the right formula?

Answer 1

I was just struggling with a similar issue caused by CONCATENATE() erroring out on blank cells. Instead, you can use TEXTJOIN([delimeter],[ignore blanks],[text1], [text2]...)

Like:

=TEXTJOIN(CHAR(10),TRUE,A1:E1)