There are some built-in functions like the CONCATENATE function to concatenate cells in Google Sheets when all the cells have some values. But occasionally, things might be changed. There may be values in some of the cells and emptiness in others. And we have to join only the non-empty cells. Therefore we can not use those functions directly. We have to modify them a little bit. So in this article, we’ll see 7 useful ways to concatenate if the cell is not blank in Google Sheets with clear steps and images.

**Table of Contents**hide

**A Sample of Practice Spreadsheet**

You can download Google Sheets from here and practice very quickly.

**7 Useful Ways to Concatenate If Cell Is Not Blank in Google Sheets**

Let’s get introduced to our dataset first. Here we have some products in **Column B**. if you notice, you may find some **blank cells** in **Column B** among those products. Now we want to join these products and put them in a single cell with some separator like comma or dash and also want to ignore the blank cells. So, I’ll show you 7 useful examples to concatenate if the cell is not blank in Google Sheets by using this dataset.

**1. Using TEXTJOIN Function**

First and foremost, we can use **the ****TEXTJOIN function** to concatenate if the cell is not blank in Google Sheets. This function directly ignores the empty cells and only connects those cells which have any values in them. We can also put a separator like a comma into this function by which we want to join our desired values. Let’s see the steps.

**Steps:**

- Firstly, type the following formula in
**Cell B13**–

`=TEXTJOIN(",",1,B5:B10)`

- Secondly, hit
**Enter**to get the result. - Finally, you’ll see all the products have concatenated in
**Cell B13**with the separator comma and ignored the blank cells.

**Read More: How to Concatenate Values for IF Condition in Google Sheets**

**2. Combining JOIN, FILTER and NOT Functions**

Apart from the previous method, we can combine the **JOIN****, ****FILTER** and **NOT** functions together to concatenate if the cell is not blank in Google Sheets. These functions together serve the same purpose as the previous method does. Let’s see how to do it.

**Steps:**

- At first, select
**Cell B13**and write the following formula there-

`=JOIN(",",FILTER(B5:B10,NOT(B5:B10="")))`

- Then, press
**Enter**to get the output. - At last, we neglect the blank cells and concatenate all the products in
**Cell B13**with the separator comma.

**Formula Breakdown**

**NOT(B5:B10=””)**

Initially, this function returns the **non-blank cells** from **Cells B5** to **B10**.

**FILTER(B5:B10,NOT(B5:B10=””))**

Moreover, this function filters the values it gets from the **NOT **function.

**JOIN(“,”,FILTER(B5:B10,NOT(B5:B10=””)))**

Lastly, this function joins the values from the non-blank cells with the separator comma from **Cells B5** to **B10.**

**3. Merging JOIN and SPLIT Functions**

We can further merge the **JOIN **and **SPLIT** functions instead of the **FILTER **function to connect the non-empty cells in Google Sheets. As well as we’ll add the separator comma into our formula to separate the connected values.

**Steps:**

- First of all, activate
**Cell B13.** - Next, insert the following formula there-

`=JOIN(",",SPLIT(JOIN("%",B5:B10),"%",0))`

- After that, click
**Enter**to obtain the result. - In the end, we ignore the empty cells and combine all the products in
**Cell B13**with a comma as the separator.

**Formula Breakdown**

**JOIN(“%”,B5:B10)**

Firstly, this function joins all the cells from **Cells B5** to **B10**.

**SPLIT(JOIN(“%”,B5:B10),”%”,0)**

Then, this function **splits** all the values it gets from the **JOIN **function and removes the empty values.

**JOIN(“,”,SPLIT(JOIN(“%”,B5:B10),”%”,0))**

Finally, this function again connects the non-empty values that it gets from the **SPLIT **function within **Cells B5** to **B10.**

**Read More: How to Add Space with CONCATENATE in Google Sheets**

**4. Using Nested IF Functions**

At this moment I’ll use multiple **IF** functions together which are called nested **IF** functions to join the non-blank cells. We know that **IF** is a logical function. We normally use it to get some result by putting a condition. So the nested **IF** functions can be used to insert multiple conditions one by one. The problem with using this method is that we have to insert every cell separately into our formula. That is quite irritating and lengthy.

**Steps:**

- In the beginning, put the formula below in
**Cell B13**–

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

- Consequently, hit
**Enter**to get the output. - Ultimately, by concatenating all the products in
**Cell B13**with a comma as a separator, we ignore the blank cells as you can see in the picture.

**Similar Readings**

**How to Get Opposite of Concatenate in Google Sheets (2 Ways)****How to Concatenate Double Quotes in Google Sheets (3 Ways)****Google Sheets QUERY Function to Concatenate Two Columns**

**5. Combining IF, AND, NOT, ISBLANK and CONCATENATE Functions**

Now, we have a different dataset. Here we have some products in **Column B** and some prices in **Column C**. If you look closely, you could see some empty cells in **Columns B** and **C**. Now we want to concatenate the product and price with a separator dash in **Column D** by ignoring the blank cells. We can do this by combining the **IF, ****AND****, NOT, ****ISBLANK**, and **CONCATENATE** functions together. Below you’ll see the steps for doing so.

**Steps:**

- Before all, start by entering the formula below in
**Cell D5**–

`=IF(AND(NOT(ISBLANK(B5)),NOT(ISBLANK(C5))),CONCATENATE(B5,"-",C5),"")`

- Again, press
**Enter**to obtain the result.

**Formula Breakdown**

**ISBLANK(B5), ISBLANK(C5)**

Firstly, these functions will check if **Cells B5** and **C5 **are blank or not.

**NOT(ISBLANK(B5)), NOT(ISBLANK(C5))**

Then, these functions will check whether **Cells B5** and **C5 **have values or not.

**AND(NOT(ISBLANK(B5)),NOT(ISBLANK(C5)))**

Next, this function returns **TRUE** when all these 2 conditions are right, otherwise **FALSE**.

**CONCATENATE(B5,”-“,C5)**

This function connects **Cells B5** and **C5 **with the separator dash.

**IF(AND(NOT(ISBLANK(B5)),NOT(ISBLANK(C5))),CONCATENATE(B5,”-“,C5),””)**

Finally, the IF function will give the output as a connected cell if the logic is **TRUE** otherwise it’ll ignore the cell.

- Then, apply the
**Fill****Handle**tool to use the formula in the rest of the cells of**Column D.**

- Last but not least, you’ll see the combination of all the products and prices in
**Column D**with a separator dash. You’ll also see that the formula has ignored those products or prices that have corresponding blank cells.

**Read More: How to Concatenate Two Columns in Google Sheets**

**6. Uniting ARRAYFORMULA and IF Functions**

Unlike the previous example, we can unite the **ARRAYFORMULA** and **IF** functions to concatenate if the cell is not blank in Google Sheets. This method is faster than the previous method because here we are getting the output with just a single click. We don’t have to use any **Fill** **Handle** tool here.

**Steps:**

- Earlier on, begin by typing the following formula in
**Cell D5**–

`=ARRAYFORMULA(IF((B5:B10<>"")*(C5:C10<>""), B5:B10&"-"&C5:C10,))`

- Next, hit
**Enter**to see the outcome at once. - Now you are getting the result with just a single click.

**Formula Breakdown**

**IF((B5:B10<>””)*(C5:C10<>””), B5:B10&”-“&C5:C10,)**

Firstly, this function checks the values from **Cells B5** to **B10** and **Cells C5** to **C10** are empty or not. If there are values at the cells in both **Columns B** and **C**, then it joins them in **Column D** with a separator dash.

**ARRAYFORMULA(IF((B5:B10<>””)*(C5:C10<>””), B5:B10&”-“&C5:C10,))**

Finally, this function makes the formula an array and expands the results quickly throughout the range.

**Read More: How to Concatenate With Separator in Google Sheets (3 Ways)**

**7. Applying CONCATENATE Function**

At this moment we have the year, date of birth, project code, joining date, and serial number of a certain employee of a company in **Column C**. Some pieces of information are missing as you can see in the picture. We want to make an employee id with these pieces of information. So we have to join these values in a single cell. We can use the **CONCATENATE** function for this purpose. This function **concatenates multiple cells** without any separator. The only problem with using this function is that we can not use any separator among the values. All the values will connect one by one.

**Steps:**

- Initially, select
**Cell C10**. - After that, insert the following formula-

`=CONCATENATE(C4:C8)`

- Again, to get the output, press
**Enter**. - You’ll get the employee id in
**Cell C10**. If we add the missing information such as the date of birth, then it will automatically add to the employee id in**Cell C10**.

**Read More: How to Use CONCATENATE Function in Google Sheets**

**Conclusion**

That’s all for now. Thank you for reading this article. In this article, I have discussed 7 useful ways to concatenate if the cell is not blank in Google Sheets. Please comment in the comment section if you have any queries about this article. You will also find different articles related to google sheets on our **officewheel.com****.** Visit the site and explore more.

## Related Articles

**How to Concatenate Strings with Separator in Google Sheets****How to Concatenate in Google Sheets (6 Suitable Ways)****How to Concatenate Text and Formula in Google Sheets (7 Ways)****How to Append Text in Google Sheets (An Easy Guide)****How to Concatenate Number and String in Google Sheets****How to Concatenate Strings in Google Sheets (2 Easy Ways)**