How To Enable Gaming Mode on Android 12

Back in Februry, folks at XDA had spotted an unreleased Gaming Dashboard in the works for Android 12. But up until now, there were no clear...


How to Replace Every Nth Delimiter in Google Sheets

Posted by   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.


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

No comments:
Write Comments

Hello Friends, welcome to we Hope You'll like it - COntact US