Sajid Ahmed
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.
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)
