How to combine three columns into one and leave out empty cells?-CodePudding

Hello I need to advice some formulas on my problem:

How can I combine two, three or more columns into single one column?
And if in columns are "empty" cells I want to skip these cells inside of that single one column.

But be aware! This is the problem. All columns are contains another formulas. So these "empty" cells are in fact contains my another formulas with result ="".

Here is example of what I want to get - in column E:

EDIT:

New functions like TOCOL are not available in my Microsoft Office 365 MSO: 16.0.14326.21092 (32 bit).

CodePudding user response：

You can use:

Formula in E2:

=TOCOL(IF(A2:C10="",NA(),A2:C10),3,1)

Note that just =TOCOL(A2:C10,3,1) is not going to cut it if these cells hold an empty string "".

CodePudding user response：

If your data is not very huge use:

=FILTERXML("<t><s>"&TEXTJOIN("</s><s>",1,TRANSPOSE(A2:C10))&"</s></t>","//s")