When you join values in different cells or columns, you may face a common issue. That’s the unwanted delimiter like commas, semicolons and pipe symbols in the joined text. You can intelligently remove such extra delimiter in Google Sheets.
I think I should better say how to avoid extra delimiter characters in Google Sheets that happens due to joining texts, numbers or any values in columns.
We normally use Comma or Semicolon as the delimiter. Appearing additional commas or semicolons in the joined text make it poorly formatted and affect the overall appearance of your Google Sheets Project.
To remove extra delimiter in Google Sheets what you want to do is to choose a suitable join function.
What we are going to do is not removing extra delimiter by using either of the functions SUBSTITUTE or REGEXREPLACE. Then? I am going to tell you how to properly join columns and avoid additional delimiters like repeated commas.
Note: I’ve provided one REGEXREPLACE formula too in this tutorial to help you remove extra comma delimiter.
Remove Extra Delimiter in Google Sheets
To concatenate values in arrays, normally I am using the CONCATENATE function. But it doesn’t support ‘properly’ placing a delimiter between the joined text. This function just appends strings to one another.
Then the most commonly used one is the JOIN function. This function can join texts using a specified delimiter.
Must Check: Google Sheets Functions Guide
The specified delimiter can be any character and normally we use the Comma or Semicolon for this purpose. The pipe symbol and the Caret is also common.
See what happens when we join columns using the Join function.
See one Join formula that uses Comma as the delimiter.
Formula:
="Availability: "&join(", ",A3:E3)
Result:
Availability: Cement, 5, , 5, 10
You can see one additional Comma in this result which is due to the blank cell C3 in the range that we joined.
Then how to remove extra delimiter in Google Sheets? As I’ve said above, we can avoid such additional delimiters by using another function.
Before going to that, as promised, let me share you one REGEXREPLACE formula.
Suppose I have the above formula result in the cell F6. The below formula can remove the extra comma.
=REGEXREPLACE(F6,", ,",",")
You can replace the above cell reference, i.e. F6, with the above JOIN formula itself.
=REGEXREPLACE("Availability: "&join(", ",A3:E3),", ,",",")
But all this is not required if you use another Google Sheets function to Join the columns. Yup! I am talking about the TEXTJOIN function in Google Sheets.
From the above example, you can understand that the additional delimiters are occurring due to the blank cells in the range.
In the function TEXTJOIN, you can decide whether to include blank cells or not.
Formula:
="Availability: "&textjoin(", ",TRUE,A3:E3)
Result:
Availability: Cement, 5, 5, 10
The Boolean TRUE in this formula directs the formula to ignore blank cells in the range. If you change it to the Boolean FALSE value, then there won’t be any difference in the output of this formula with the output of the JOIN formula.
So please use the function TEXTJOIN instead of the join function to avoid additional delimiters.
Must Read: The Flexible Array Formula to Join Columns in Google Sheets
Conclusion
Please understand one important thing. The functions JOIN and TEXTJOIN are an entirely different type of functions. The former only supports one-dimensional arrays and it’s not applicable to the latter.
Here is the comparison of the popular text join functions in Google Sheets – Difference Between JOIN, TEXTJOIN, CONCATENATE Functions in Google Sheets.
The TEXTJOIN function can be compared to the CONCATENATE function. The only difference is in the use of delimiter. The former can include separators (delimiters) but the latter can’t.
