Replace Every Nth Delimiter in Google Sheets

Sometimes we get delimited data that is difficult to format. This article will explain how we can replace every nth delimiter in a given string in Google Sheets.

Data that should be separated may all be found in a single string with delimiters such as a space or a comma. We can use some Google Sheets functions to manipulate the text to clean it up further.

Let’s look at a scenario where we might have to replace a delimiter. 

For example, we might have a string that looks like this:

“Term1 Term2 Term3 Term4 Term5”

In this example, we want to convert each of the spaces into the operators ‘+‘ and ‘*‘. Specifically, we want every second term to be a ‘*‘ and the rest will be a ‘+‘. How do we go about this?

With the REGEXREPLACE and SUBSTITUTE function, we can specify exactly which characters to convert. For example, we might have a string of text separated by spaces that we want to convert into a table. Or we might want to edit an equation so that every other operator is a multiplication ‘*’.

Let’s learn how to manipulate these strings ourselves in Google Sheets and later test the formulas with some sample strings. Later on, we’ll show you a step-by-step example of how we can achieve these results.

A Real Example of Replacing Every Nth Delimiter

Let’s look into how we can convert our terms example into the desired input.

In the table below, we can see each conversion step to get the final result. In cell A3, we used the SUBSTITUTE function to replace all spaces with the ‘+’ sign. Next, we used REGEXREPLACE to get every third ‘+’ symbol and replace it with a ‘*’. 

To get the result in cell A3, we just need to use the following formula:

=SUBSTITUTE(A2," ",'+')

For the final result, we used the following formula:

=REGEXREPLACE(REGEXREPLACE(A3,"(\+.*?){2}","$0*"),"\+\*",'*')

A regular expression is a sequence of characters that specifies a particular search pattern. The REGEXREPLACE function allows us to use regular expressions to tell Google Sheets what parts of a string to replace. It might be a bit difficult to read these expressions, but we can have a brief explanation of how the expressions work.

As you may have noticed, we used a nested strategy with our REGEXREPLACE function. The inner function finds every second ‘+’ character and appends a ‘*’. The outer function replaces every instance of “+*” with simply ‘*’.

The inner function has the expression "\+.*?){2}" that allows us to find every second instance of our ‘+’ character. Modifying the number between the brackets can replace every nth match for any integer n. 

Let’s look at a more complex example that involves replacing every nth delimiter in Google Sheets.

In this example, we received a string that has data on attendees of an event. Unfortunately, these values are not formatted properly. We can’t directly import this data as a CSV since it will still give us one long row. After some time, you realize that every three entries make up one row.

For the final input, we want to convert the string into a range of values. How can we do this?

To get the table seen above, we just need to use the following formula:

=ARRAYFORMULA(SPLIT(TRANSPOSE(SPLIT(REGEXREPLACE(A2,"([^,]+,[^,]+,[^,]+)","$1|"),'|')),","))

Since this formula is a bit tricky, let’s break it down.

The first thing that we needed to do was use the REGEXREPLACE button. In this example, we used REGEXREPLACE to find the “end” of our row. In the sheet below, you can see what the function did to our original string. We replaced every third comma with a ‘|’ character. This character will be the delimiter for the next function, which is the SPLIT.

The SPLIT function allows us to place each row into its own cell. Now that each row is separated, we still face the issue of transposition. We want these cells transposed so that they are in order vertically. We can use the TRANSPOSE function to make this happen.

Finally, we can do one more split so that each of the three values per row is expanded on. Since we want to use the SPLIT function over several rows, we must wrap the formula inside an ARRAYFORMULA function.

Afterward, we finally arrived at our result. 

You can make a copy of this spreadsheet using the link ‌I’ve attached below. 

If you’re ready to try out the REGEXREPLACE technique in Google Sheets, let’s begin writing it ourselves!

How to Replace Every Nth Delimiter in Google Sheets

In this section, we will go through each step needed to start using the REGEXREPLACE function in Google Sheets to split by every nth delimiter.

1. First, select the cell that will hold the result of our formula.
   
2. Next, we can use the REGEXREPLACE function to replace every nth delimiter into another symbol, like an underscore or a hyphen. In this example, let’s convert every third comma in our string into an underscore.
   
3. In the next step, we can use the SPLIT function to split the string by the underscore delimiter. We now have each row in separate cells.
   
4. We can use the TRANSPOSE function to transpose the output of the split function.
   
5. Finally, we can use both the ARRAYFORMULA and SPLIT to split each row into separate columns.
   
   

This step-by-step guide shows you an easy way to replace every nth delimiter of a string in Google Sheets. Working with text is always a chore to do manually. Luckily, there are many useful functions in Google Sheets that can help you get the job done.

You can now use the REGEXREPLACE function in Google Sheets together with the various other Google Sheets formulas available to create great worksheets that can handle any type of input.

Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

Subscribe