The REGEXREPLACE helps us substitute or replace every nth delimiter in Google Sheets. I have recently used it in a real-life scenario, and let me explain that first.

I wanted to perform a dynamic query within the QUERY function.

Syntax: QUERY(data, query, [headers])

First, I have used a TEXTJOIN + SEQUENCE combo in cell A2 as below to generate the dynamic query.

=ArrayFormula(
     textjoin("*",true,"Col"&sequence(1,10))
)

Output: Col1*Col2*Col3*Col4*Col5*Col6*Col7*Col8*Col9*Col10.

But what I have required is something different, and it’s as below.

Required Output: Col1*Col2+Col3*Col4+Col5*Col6+Col7*Col8+Col9*Col10.

Here comes the substitution part.

To substitute/replace every nth delimiter as above in Google Sheets, I have used the REGEXREPLACE function. Here is how.

Replace Every Nth Delimiter Using REGEXREPLACE in Google Sheets

Example 1 – Substitute Every Nth Asterisk with Plus Signs

Assume the above query output is in cell A2.

To substitute the required every nth delimiter in it and get the required output, use the following formula in cell B2.

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

Formula Explanation

I have used a nested REGEXREPLACE formula here. I mean one regex within another.

The first one only adds the required delimiter (+ sign) at every nth (2nd) position, not replaces.

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

Let’s call it regex_1.

Output: Col1*Col2*+Col3*Col4*+Col5*Col6*+Col7*Col8*+Col9*Col10.

The second formula, the outer REGEXREPLACE, substitutes all the occurrences of *+ with +.

=regexreplace(
     regex_1,
     "\*\+","+"
)

Note:- Replace {2} with {3} to substitute or replace every 3rd delimiter.

How to replace every nth delimiter in a column of values in Google Sheets?

In the above example, the formula refers to cell A2. If you have values in A2:A, you can use an array formula.

For that, make just two changes in the formula.

1. Use the ArrayFormula function.
2. Change cell reference A2 with cell range reference A2:A.

=ArrayFormula(
     regexreplace(
        REGEXREPLACE(A2:A,"(\*.*?){2}","$0+"),
        "\*\+","+"
     )
)

Example 2 – Replace Every Nth Comma with Tilde Delimiters

I have a few names in cell A2 that are separated by commas.

I want to replace every nth (here 2nd) occurrence of the comma delimiter with a tilde delimiter.

The end goal is to split the output and make pairs of names in two columns.

The below formula replaces every nth (2nd) occurrence of the comma delimiter with a tilde.

=regexreplace(
     REGEXREPLACE(A2:A,"(\,.*?){2}","$0~"),
     "\,\~","~"
  )

To reach our end goal, we should use SPLIT (twice), TRIM, and TRANSPOSE with the ArrayFormula.

=ArrayFormula(
     TRIM(
        split(
           transpose(
              split(
                 regexreplace(
                    REGEXREPLACE(A2:A,"(\,.*?){2}","$0~"),
                    "\,\~","~"
                 )
              ,"~")
           ),
        ",")
     )
)

That’s all. Thanks for the stay. Enjoy!

Related Resources

• Substitute Nth Match of a Delimiter from the End of a String.
• Split a Text after Every Nth Word in Google Sheets (Using Regex and Split).
• Insert Delimiter into a Text After N or Every N Character in Google Sheets.
• How to Match | Extract Nth Word in a Line in Google Sheets.
• Extract Last N Values from a Delimiter Separated String in Google Sheets.
• Regex to Get All Words after Nth Word in a Sentence in Google Sheets.