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

Get FREE Advanced Excel Exercises with Solutions!

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.


How to Concatenate Multiple Cells but Ignore Blanks in Excel: 5 Simple Ways

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 in Excel


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.

Read More: Combine Multiple Cells into One Separated by Comma in Excel


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.

Read More: How to Merge Cells Using Excel Formula


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


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 Combine Cells with Same Value in Excel


💬 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 Practice Workbook


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. Keep learning new methods and keep growing!


Related Articles


<< Go Back To Excel Concatenate Multiple Cells | Concatenate Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
A.N.M. Mohaimen Shanto
A.N.M. Mohaimen Shanto

A.N.M. Mohaimen Shanto, a B.Sc. in Computer Science and Engineering from Daffodil International University, boasts two years of experience as a Project Manager at Exceldemy. He authored 90+ articles and led teams as a Team Leader, meticulously reviewing over a thousand articles. Currently, he focuses on enhancing article quality. His passion lies in Excel VBA, Data Science, and SEO, where he enjoys simplifying complex ideas to facilitate learning and growth. His journey mirrors Exceldemy's dedication to excellence and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo