How to Replace Every Nth Delimiter in Google Sheets

Published on

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+"),
     "\*\+","+"
)
Regex to Replace Every Nth Delimiter in Google Sheets

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~"),
                    "\,\~","~"
                 )
              ,"~")
           ),
        ",")
     )
)
Substitute Every Nth Delimiter and Split to Make Pairs

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

Related 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.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

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.