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.

Sort by Field Labels Using the SORT and XMATCH Combo in Excel

Want to sort your Excel data by column names instead of column positions? Learn...

How to Sort Pie Slices in Google Sheets

To sort pie slices in a pie chart, you need to sort the data...

Filter Items Unique to Groups in Google Sheets

In this tutorial, we'll learn how to filter items unique to groups in Google...

Find Common Items Across Multiple Columns in Google Sheets

This tutorial explains how to find common items across multiple columns in Google Sheets....

More like this

How to Sort Pie Slices in Google Sheets

To sort pie slices in a pie chart, you need to sort the data...

Filter Items Unique to Groups in Google Sheets

In this tutorial, we'll learn how to filter items unique to groups in Google...

Find Common Items Across Multiple Columns in Google Sheets

This tutorial explains how to find common items across multiple columns in Google Sheets....

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.