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

In Microsoft Excel, you will encounter various situations where you will have to concatenate multiple cells. It is one of the crucial Excel tasks. Sometimes, our goal is to combine an Excel range with data. But, if the cells contain blank or empty cells, it may return unwanted results. In this tutorial, you will learn to concatenate multiple cells but ignore blanks in Excel with suitable examples and proper illustrations. So, stay with us.


Download Practice Workbook


5 Ways to Concatenate Multiple Cells but Ignore Blanks in Excel

In the upcoming sections, I will provide you with five effective methods that you can implement to your worksheet to concatenate multiple cells but ignore blanks in Excel. I recommend you learn and apply all these methods. It will undoubtedly develop your Excel knowledge. Letโ€™s get into it.


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

This method is an Excel formula to combine multiple cells but ignore blank cells. We are using the CONCATENATE function, the IF function, and the ISBLANK function to concatenate cells.

Have a look at the following dataset:

Here, you can see 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 them. So, we will fix this problem with our Excel formula.

Select Cell E4 and type the following formula:

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

Combine CONCATENATE, IF, and ISBLANK Functions to Concatenate Multiple Cells in excel

In the end, 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. But, if there is value it returns the cellโ€™s value.

Basically, the problem with this formula is, it is very lengthy. If you have too many cells and blanks, it will be hectic.

Read More: How to Concatenate Multiple Cells with Comma in Excel (4 Ways)


2. Use TEXTJOIN Function to Concatenate Multiple Cells Ignoring Blanks

If you ask me to suggest to you the easiest method to concatenate multiple cells and also ignore blanks easily, this is the method. In this method, we are using the TEXTJOIN function to concatenate multiple cells. The main benefit of this function is to combine numerous cells without any hazard. But, the problem with this function is, it is available in Excel 2019 and later versions. You can not use it on earlier versions.

The Generic Formula:

=TEXTJOIN(delimiter,TRUE,range of cells)

Here, by the delimiter, you can separate your concatenated cells. Use any delimiter according to your choice.

We are using the previous dataset here.

Select Cell E4 and type the following formula:

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

Concatenate Multiple Cells But Ignore Blanks in Excel

Here, we used a comma as a delimiter. You can use a dash(โ€œ-โ€) as a separator like the following:

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

Concatenate Multiple Cells But Ignore Blanks in Excel

This function will concatenate multiple cells and ignore blank cells without any trouble. Letโ€™s give this a try if you have Excel 2019 or a later version.

Related Content: How to Bold Text in Concatenate Formula in Excel (2 Methods)


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

Before we discuss this, 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:

As you can see, we have space in the output. To avoid this, we are using a formula in this method. This formula will contain the IF function, the LEN function, and the TRIM function.

This method will combine cells from the column and ignore those blank cells that have a usual value in them.

Select Cell E4 and type 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)

Concatenate Multiple Cells But Ignore Blanks in Excel

As you can see, our Excel formula will concatenate multiple cells and also ignore blanks.

๐Ÿ”Ž Breakdown of the Formula

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

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

IF(LEN(TRIM(B8))=0,โ€โ€,B8): Finally,ย  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.

Related Content: How to Concatenate Multiple Cells With Space in Excel (7 Methods)


Similar Readings:


4. Concatenate Multiple Cells but Ignore Blanks Using Formula

In this Excel formula, we are incorporating the IF function with the ampersand(&). It is also a lengthy formula. But, it will also work fine to concatenate multiple cells and ignore blanks. We previously showed you the ampersand formula. You saw if there is any hidden space, our output will also contain space. If you are sure there are no spaces, you can use this formula.

Select Cell E4 and type the following formula:

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

Concatenate Multiple Cells But Ignore Blanks in Excel

๐Ÿ”Ž Breakdown of the Formula

B5&IF(B6<>โ€โ€,โ€-โ€œ&B6,โ€โ€): Basically, 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.

In this way, you can combine multiple cells and ignore blank cells in Excel.

Read More: Concatenate Multiple Cells Based on Criteria in Excel (4 Methods)


5. Using VBA Codes to Ignore Blanks and Concatenate Cells in Excel

Now, if you love to solve Excel problems using the VBA codes, try this. This code will also combine cells and it will ignore blanks. We will create a user-defined function here that will take the input and show you the concatenated texts.

๐Ÿ“Œ Steps

  • First, press Alt+F11 on your keyboard to open the VBA editor.
  • Then, select Insert>Module.

  • After that, type 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
  • Then, save the file.
  • After that, select Cell E4 and type the following formula:

=combine_cells(B5:B9)

Concatenate Multiple Cells But Ignore Blanks in Excel

  • Next, press Enter.

Concatenate Multiple Cells But Ignore Blanks in Excel

As you can see, this VBA code will effortlessly concatenate those multiple cells and ignore blanks in your Excel worksheet. Give this a try.

Read More: How to Concatenate String and Variable in Excel VBA (A Detailed Analysis)


๐Ÿ’ฌ 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.


Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge of Excel on how to concatenate multiple cells but ignore blanks. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.

Donโ€™t forget to check our website Exceldemy.com for various Excel-related problems and solutions.

Keep learning new methods and keep growing!


Related Articles

Shanto

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo