Delimiters, such as commas, pipes, or spaces, are used to separate individual parts in a text string. Sometimes, you may encounter repeated delimiters within the string. So, how can you remove these extra delimiters in Google Sheets?

The solution depends on whether the string with extra delimiters is generated by a formula that joins values or not. Based on this, the approach varies.

If it’s a formula output, you should adjust the formula to avoid generating extra delimiters. Otherwise, you can substitute the additional delimiters with an empty string using a function. In this tutorial, we’ll explore both scenarios.

Remove Extra Delimiters When Joining Values in Google Sheets

To remove extra delimiters in Google Sheets, you need to use a suitable join function.

You can use JOIN, TEXTJOIN, or the ampersand (&) operator to join values with a delimiter to separate them. However, when using JOIN or the ampersand, extra delimiters may appear if one or more of the cells being joined are empty.

Here’s what happens when we join columns using the JOIN function.

Assume you are joining the values in cells A1:C1 using either of these formulas:

=JOIN(",", A1:C1)
=A1&","&B1&","&C1

If A1 contains “apple” and C1 contains “mango”, but B1 is empty, the formulas will return the following string:

apple,,mango

To remove the extra delimiter, replace the formula with TEXTJOIN, which automatically skips empty cells:

=TEXTJOIN(",", TRUE, A1:C1)

This will return the following string:

apple,mango

Here’s another example where we use a combination of the ampersand and JOIN functions to join values:

="Availability: "&JOIN(", ", A3:E3)

With the sample data, this would return:

Availability: Cement, 5, , 5, 10

You can replace it with:

="Availability: "&TEXTJOIN(", ", TRUE, A3:E3)

This ensures there are no extra delimiters in the output.

Important Note:

TEXTJOIN is not always a direct replacement for JOIN or the ampersand. When joining a single row or column, they may produce similar results. However, in scenarios where TEXTJOIN is not suitable, you can use the REGEXREPLACE method to remove extra delimiters.

Remove Extra Delimiters Using the REGEXREPLACE Function

Consider the following example. The sample text in cell A1 is:

apple,,mango,orange

You can remove the extra delimiters (commas in this case) with the following formula:

=REGEXREPLACE(A1, "\s*\,\s*(\,\s*)*", ",")

This will replace multiple consecutive commas with a single comma.

If your text contains spaces after commas, like:

apple, , mango, orange

You can use this formula instead:

=REGEXREPLACE(A1, "\s*\,\s*(\,\s*)*", ", ")

Here’s what it does:

• \s*\,\s* – matches a single comma with optional spaces around it.
• (\,\s*)* – matches additional pipes with optional spaces, i.e., extra empty fields.

To handle different delimiters, replace the comma (,) in the formula with the desired delimiter.

Resources

• Split Numbers from Text Without Delimiters in Google Sheets
• Insert Delimiter into a Text After N or Every N Characters in Google Sheets
• Substitute Nth Match of a Delimiter from the End of a String in Google Sheets
• Extract Last N Values from a Delimiter-Separated String in Google Sheets
• How to Replace Every Nth Delimiter in Google Sheets
• Query to Combine Columns and Adding Separators in Google Sheets