CODEWITHSUNDEEP
excel-formula
SRK
SRK

Reputation: 141

How to ignore blank cells while concatenating cells in Excel

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?

Upvotes: 14

Views: 137516

Answers (7)

Arun
Arun

Reputation: 770

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. enter image description here

Upvotes: 2

Elliott
Elliott

Reputation: 380

=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.

excel table

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.

Upvotes: 7

Leo
Leo

Reputation: 415

= 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)),
)

Upvotes: 0

Nada
Nada

Reputation: 311

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)

Upvotes: 21

Mehran Vahedi - WisdombyData
Mehran Vahedi - WisdombyData

Reputation: 11

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.

Upvotes: 1

teylyn
teylyn

Reputation: 35915

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))

Upvotes: 0

Erica
Erica

Reputation: 2457

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)
)

Upvotes: 11

Related Questions