Concatenate Multiple Cells but Ignore Blanks in Excel (5 Ways)

Method 1 – Combine CONCATENATE, IF, and ISBLANK Functions to Concatenate Multiple Cells

Consider the following dataset. In column B, we have multiple texts, and we want to concatenate them, but there are some empty cells. If we try to concatenate them, our output will contain blanks in between. We will fix this problem with our Excel formula.

Select Cell E4 and use the following formula:

=CONCATENATE(IF(ISBLANK(B5),"",B5),IF(ISBLANK(B6),"",B6),IF(ISBLANK(B7),"",B7),IF(ISBLANK(B8),"",B8),IF(ISBLANK(B9),"",B9))

Our Excel formula will concatenate these multiple cells and ignore blanks.

Breakdown of the Formula

ISBLANK(B5): Here, the ISBLANK function checks if the cell is blank or not.

(IF(ISBLANK(B5),””,B5): If the cell is blank, the IF function returns nothing. Otherwise, it returns the cell’s value.

This formula is lengthy to modify if you have a lot of cells to work with.

Method 2 – Use the TEXTJOIN Function to Concatenate Multiple Cells Ignoring Blanks

This function is available in Excel 2019 and later versions.

The Generic Formula:

=TEXTJOIN(delimiter,TRUE,range of cells)

You can separate your concatenated cells with the delimiter argument.

Select Cell E4 and use the following formula:

=TEXTJOIN(",",TRUE,B5:B9)

We used a comma as a delimiter. You can use a dash(“-”) as a separator like the following:

=TEXTJOIN("-",TRUE,B5:B9)

Method 3 – Join IF, LEN, and TRIM Functions to Ignore Blanks and Concatenate Multiple Cells

Look at the following formula and its result:

=B5&B6&B7&B8&B9

Looks like we concatenate multiple cells and ignore blank cells, right? But, here is a catch. Our empty cells were completely blank. That means there were no values there. But, some cells may contain hidden spaces or any characters. If you enter a space in one cell like cell C6, it will return the following output:

Here’s how you can fix this:

Select Cell E4 and use the following formula:

=IF(LEN(TRIM(B5))=0,"",B5) & IF(LEN(TRIM(B6))=0,"",B6) & IF(LEN(TRIM(B7))=0,"",B7)& IF(LEN(TRIM(B8))=0,"",B8)& IF(LEN(TRIM(B9))=0,"",B9)

Breakdown of the Formula

TRIM(B5): The TRIM function removes all the spaces from the cell.

LEN(TRIM(B5): The LEN function will return the length of that cell.

IF(LEN(TRIM(B8))=0,””,B8): The IF function will check if the cell length is 0 or not. If cell length is 0, it will return nothing. Otherwise, it will return the cell value.

Method 4 – Concatenate Multiple Cells but Ignore Blanks Using a Formula

Select Cell E4 and use the following formula:

=B5&IF(B6<>"","-"&B6,"")&IF(B7<>"","-"&B7,"")&IF(B8<>"","-"&B8,"")&IF(B9<>"","-"&B9,"")

Breakdown of the Formula

B5&IF(B6<>””,”-“&B6,””): The IF function checks if the cell is empty or not. If it is not blank, it will concatenate cells by a separator dash (-) with the ampersand.

Method 5 – Using VBA Code to Ignore Blanks and Concatenate Cells in Excel

Steps

Press Alt + F11 on your keyboard to open the VBA editor.
Select Insert and then choose Module.

Insert the following code:

Function combine_cells(range_of_cells As Range) As String
  For Each c In range_of_cells: cc = IIf(c = "", cc & "", cc & c & "-"): Next
  combine_cells = Left(cc, Len(cc) - 1)
End Function

Save the file.
Select Cell E4 and use the following formula:

=combine_cells(B5:B9)

Press Enter.

Things to Remember

✎ If your cells have any space hidden in them, you will see them in output.

✎ These formulas are quite lengthy. If it bothers you, I recommend you use the VBA code.

✎ If you are sure that cells don’t contain blanks, just use the ampersand to concatenate multiple cells. It will ignore those blank cells that have nothing in them.

Download the Practice Workbook

Concatenate Multiple Cells but Ignore Blanks.xlsm