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?
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)
You need to include a conditional to check whether the cell has a value or is empty. If the cell is empty (via ISBLANK()), then return nothing; otherwise, return the value of the cell plus a carriage return.
IF(ISBLANK(A3),"",A3&CHAR(10))
So, for your five column formula (linebreaks added for readability):
CONCATENATE(
IF(ISBLANK(A1),"",A1&CHAR(10)),
IF(ISBLANK(B1),"",B1&CHAR(10)),
IF(ISBLANK(C1),"",C1&CHAR(10)),
IF(ISBLANK(D1),"",D1&CHAR(10)),
IF(ISBLANK(E1),"",E1)
)
=TEXTJOIN(",",TRUE,A1:E1)
The new TEXTJOIN function in Excel 2019 and Office 365 versions can accomplish this easier without all of the IF statements.
The TEXTJOIN function combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.
In particular to the original question about ignoring blanks, setting the ignore_empty parameter to TRUE will automatically ignore blanks.
The CHAR(10) trick Erica suggested doesn't work with TEXTJOIN however. The formula =TEXTJOIN(",",TRUE,A1:E1) yields Text 1,Text 2,Text 3 which requires an extra step to stack into a single column. Just copy the result, then paste below as values, then copy the values, then paste with transpose option.
Here is a formula that will be useful if any of the cells in the data set contain more than one word (e.g. New York):
= CONCATENATE(
IF(COUNTBLANK(B2)=1,"",B2&CHAR(10)&","),
IF(COUNTBLANK(C2)=1,"",C2&CHAR(10)&","),
IF(COUNTBLANK(D2)=1,"",D2&CHAR(10)&","),
IF(COUNTBLANK(E2)=1,"",E2&CHAR(10)&","),
IF(COUNTBLANK(F2)=1,"",F2&CHAR(10))
)
Notice that I have used the COUNTBLANK() argument in the formula because unlike ISBLANK(), COUNTBLANK() considers spaces (i.e. ) as empty and returns 1. By applying the formula the words that are separated with space in each cell do not become comma delimited anymore.
Another option: Empty cells will result in two linefeed characters in a row. Wrap a Substitute around your formula to remove them. Do that twice to take care of all possible occurrences.
=SUBSTITUTE(SUBSTITUTE(A1&CHAR(10)&B1&CHAR(10)&C1&CHAR(10)&D1&CHAR(10)&E1&CHAR(10)&F1,CHAR(10)&CHAR(10),CHAR(10)),CHAR(10)&CHAR(10),CHAR(10))
= CONCATENATE(
IF(COUNTBLANK(I2)=1,"",I2&CHAR(10)),
IF(COUNTBLANK(J2)=1,"",","&J2&CHAR(10)),
IF(COUNTBLANK(K2)=1,"",","&K2&CHAR(10)),
IF(COUNTBLANK(L2)=1,"",","&L2&CHAR(10)),
IF(COUNTBLANK(M2)=1,"",","&M2&CHAR(10)),
)
If you have Office 365 subscription, this can be easily done using TEXTJOIN() function. Sample example below. Formula used is:
=TEXTJOIN(",",TRUE,B1,C1,D1,E1,F1)
the first parameter is Delimiter (in this case ,)
the second parameter is "ignore_empty_set" - TRUE or FALSE (in this case, I have set it to TRUE)
Rest of the parameters are the list of cells that you want to join.
