How to Replace Every Nth Delimiter in Google Sheets

Sometimes you may need to replace every nth delimiter in a string in Google Sheets — whether it’s a comma, an asterisk, or another separator. This can be handy for grouping items, formatting text for better readability, or even creating dynamic formulas like QUERY strings.

Fortunately, Google Sheets offers powerful functions like REGEXREPLACE that let you do this without manual edits.

Table of Contents

Why Replace Every Nth Delimiter?

Here are a few scenarios where replacing every nth delimiter can be useful:

Grouping data — e.g., grouping every 2 or 3 names together.
Formatting formulas — like turning a repetitive multiplication pattern into an addition formula.
Data preparation — breaking or marking sections in a long delimited string for later splitting.

Example 1: Replace Every Nth Delimiter for Grouping

Let’s start with a basic use case — grouping every 2 names from a comma-delimited string.

Sample data (A2):

Ben, John, Samuel, Megan, Joel, Edith, Diane, Teresa, Helen, Frank, Pauline, Mike

We want to create groups of two names separated by |:

Formula:

=REGEXREPLACE(
  REGEXREPLACE(A2,"(\,.*?){2}","$0|"),
  "\,\|"," |"
)

Output:

Ben, John | Samuel, Megan | Joel, Edith | Diane, Teresa | Helen, Frank | Pauline, Mike

Note: In the formula, the number inside { } (here {2}) represents the nth delimiter to replace. For example, use {3} to target every 3rd delimiter, {4} for every 4th, and so on.

Now, if you want to split these groups into rows:

=ARRAYFORMULA(TRIM(TRANSPOSE(SPLIT(
REGEXREPLACE(
  REGEXREPLACE(A2,"(\,.*?){2}","$0|"),
  "\,\|"," |"
), " |", FALSE))))

Output:

Ben, John
Samuel, Megan
Joel, Edith
Diane, Teresa
Helen, Frank
Pauline, Mike

Pro Tip:
For grouping specifically, you can also skip regex and use WRAPROWS like this:

=ARRAYFORMULA(
 LET(group, WRAPROWS(SPLIT(A2, ", "), 2),
 CHOOSECOLS(group, 1)&", "&CHOOSECOLS(group, 2))
)

Example 2: Replace Every Nth Delimiter to Create a Dynamic QUERY String

Now let’s look at a more advanced example — creating a dynamic aggregation part of a QUERY formula.

Sample Data

Item	Vendor A Price	Vendor A Qty	Vendor B Price	Vendor B Qty	Vendor C Price	Vendor C Qty
Apple	$4.00	10	$4.00	5	$4.00	5
Orange	$3.75	20	$3.75	20	$3.75	10
Mango	$4.00	30	$4.00	30	$4.00	15

The standard query might look like:

=QUERY(A1:G, "SELECT Col1, Col2*Col3+Col4*Col5+Col6*Col7 LABEL Col2*Col3+Col4*Col5+Col6*Col7 'Total'", 1)

Google Sheets formula showing dynamic SEQUENCE adjustment for varying vendor price and quantity pairs

Instead of typing Col2*Col3+Col4*Col5+Col6*Col7 manually, we can generate it dynamically.

Step 1: Generate a Multiplication String

=ARRAYFORMULA(TEXTJOIN("*",TRUE,"Col"&SEQUENCE(1,6,2)))

Output:

Col2*Col3*Col4*Col5*Col6*Col7

Step 2: Replace Every Second `*` with a `+`

Here’s where we use REGEXREPLACE to replace every nth delimiter (in this case, every 2nd asterisk) with a plus sign:

=REGEXREPLACE(
  REGEXREPLACE(
    ARRAYFORMULA(TEXTJOIN("*",TRUE,"Col"&SEQUENCE(1,6,2))),
    "(\*.*?){2}","$0+"
  ),
  "\*\+","+" 
)

Output:

Col2*Col3+Col4*Col5+Col6*Col7

Step 3: Embed in the QUERY

=LET(
  string,
  REGEXREPLACE(
    REGEXREPLACE(
      ARRAYFORMULA(TEXTJOIN("*",TRUE,"Col"&SEQUENCE(1,6,2))),
      "(\*.*?){2}","$0+"
    ),
    "\*\+","+" 
  ),
  QUERY(A1:G, "SELECT Col1, "&string&" LABEL "&string&" 'Total'", 1)
)

Now you’ve got a fully dynamic formula — just update the sequence range, and it will work even if the number of vendor price/quantity pairs changes.

Conclusion

Using the Replace Every Nth Delimiter technique in Google Sheets opens up a lot of automation possibilities.

For grouping, it makes long lists much easier to process.
For dynamic formulas, it saves time and reduces the chance of manual errors.
With REGEXREPLACE and functions like TEXTJOIN, and SEQUENCE, you can tackle almost any delimiter manipulation task.

Whether it’s commas, pipes, or asterisks, the approach is flexible and reusable — and once you master it, you’ll find yourself applying it in all sorts of creative ways.