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.

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:

In the end, our Excel formula will concatenate these multiple cells and ignore blanks.

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:

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:

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

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:

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:

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

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:

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:

• Next, press Enter.

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

• How to Combine Cells into One with Line Break in Excel
• How to Combine Two Cells in Excel with a Dash
• How to Merge Multiple Cells without Losing Data in Excel
• How to Merge Cells Vertically Without Losing Data in Excel