How to Remove Extra Delimiters in Google Sheets

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)
Removing extra delimiters in Google Sheets using TEXTJOIN

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, ",+", ",")

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, "[, ]+", ", ")

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

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Filter the Bottom 10 Items in a Pivot Table in Google Sheets

This tutorial explains how to filter the bottom 10 items in a Pivot Table...

Hyperlink to Jump to the Last Used Row in Excel

In a vertical range, you can create a hyperlink to jump to the last...

Find the Last Used Row’s Last Value Address in Excel

In a large vertical dataset in Excel, how do you find the cell address...

Find the Last Used Row Number in Excel

When working with large datasets such as sales records, purchase data, or bills of...

More like this

Filter the Bottom 10 Items in a Pivot Table in Google Sheets

This tutorial explains how to filter the bottom 10 items in a Pivot Table...

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.