We can use nested Regex formulas to replace commas within or outside brackets in Google Sheets.

Why should we want to replace part of the commas within a long string?

In my case, I want to split a long string based on the comma delimiter.

For example, consider the following string, which is in cell A1 in one of my Google Sheets files – “Task 1, Task 2 (Sub Task 1, Sub Task 2), Task 3, Task 4.”

I want to split each main task while keeping the sub-tasks associated with it.

I’m expecting the below output.

I want to get the above result using the following SPLIT formula.

=split(A1,",")

To get that, you should first replace the commas within/outside the brackets/parenthesis.

To match commas excluding those within or outside the parenthesis, we can use RE2 regular expressions.

Formulas and Explanations

Formula to Match and Replace Commas within Brackets/Parenthesis (B1):

=regexreplace(A1,regexextract(A1,"\((.+)\)"),regexextract(REGEXREPLACE(A1,",","|"),"\((.+)\)"))

Formula to Match and Replace Commas outside Brackets/Parenthesis (B2):

=regexreplace(regexreplace(regexreplace(A1,regexextract(A1,"\((.+)\)"),regexextract(REGEXREPLACE(A1,",","🐠"),"\((.+)\)")),",","|"),"🐠",",")

Can you explain the above formulas as it’s a nested Regex or combination formula?

Yep! I’ll first explain the B1 formula.

B1 Formula Explanation

There are two parts =regexreplace(A1,part_1,part_2,"((.+))"))

Part 1:

regexextract(A1,"\((.+)\)")

The above REGEXEXTRACT extracts the strings within the parenthesis.

Part 2:

=regexextract(REGEXREPLACE(A1,",","|"),"\((.+)\)")

The REGEXREPLACE replaces the commas within the parenthesis, and the REGEXEXTRACT extracts them.

The B1 formula matches part_1 and replaces it with part_2.

That means the formula matches “Sub Task 1, Sub Task 2”, and replaces it with “Sub Task 1| Sub Task 2”.

B2 Formula Explanation

We have used a variation of the B1 formula to match and replace commas outside brackets/parenthesis.

Here we have wrapped the B1 formula with two more REGEXREPLACE formulas.

Syntax: =regexreplace(regexreplace(B1_formula,"((.+))")),",","|"),"🐠",",")

What are their roles?

I’ll come to that. Before that one important thing.

In the B1 formula, we have used the Pipe (|) delimiter.

Here we have used a Fish (🐠) delimiter instead as the former may make issues in the outer REGEXREPLACE formulas.

B1_Formula Here:

=regexreplace(A1,regexextract(A1,"\((.+)\)"),regexextract(REGEXREPLACE(A1,",","🐠"),"\((.+)\)"))

Its Result: “Task 1, Task 2 (Sub Task 1🐠 Sub Task 2), Task 3, Task 4.”

Now here are the roles of the outer two REGEXREPLACE formulas.

The first one replaces the commas with Pipe symbols.

The second one replaces the Fish symbols with Commas.

The above Google Sheets formulas in B1 and B2 won’t work if you have values in multiple brackets in A1.

Replace Commas within or outside Multiple Brackets

In the above examples, I have only sub-tasks to Task 2. Assume I have sub-tasks to Task 5 also.

The long string in A1 is as follows – “Task 1, Task 2 (Sub Task 1, Sub Task 2), Task 3, Task 4, Task 5 (Sub Task 1, Sub Task 2, Sub Task 3), Task 6, Task 7.”

Here are the expected outputs.

Match and Replace Commas within Multiple Brackets

When you want to match and replace commas within multiple brackets, follow the below steps.

Note:- You will get the formula in the final step only.

There are three steps involved.

Step 1

In cell D1, let’s split the A1 string (please see image # 3 below).

=ArrayFormula(TRIM(flatten(split(regexreplace(A1,".\z","(🐟)"),")")))&")")

The above Array Formula does the following things.

1. It adds a (🐟) at the end of the string in cell A1.
2. Split the string then based on the closing bracket, i.e. ), as the delimiter.
3. Added back the closing bracket, which got removed while splitting.
4. Flatten to make the output in rows (vertical) instead of columns (horizontal).

Step 2

In E1, use our earlier B1 formula. The only change here is in the cell reference. Here we should use D1:D3 (the processed data) instead of A1.

=ArrayFormula(regexreplace(D1:D3,regexextract(D1:D3,"\((.+)\)"),regexextract(REGEXREPLACE(D1:D3,",","|"),"\((.+)\)")))

Please see the above image (range E1:E2).

As you can see, we have already replaced commas within multiple parenthesis/brackets with pipes.

Step 3

In cell B1, use the below formula.

=textjoin("",true,ArrayFormula(regexreplace(E1:E3,"\(🐟\)","")))

It replaces the one extra fish delimiter in the step 2 output and then TEXTJOIN the split strings.

You can combine all the steps 1 to 3 and use them as below in cell B1.

Final Formula:

=textjoin("",true,ArrayFormula(regexreplace(regexreplace(TRIM(flatten(split(regexreplace(A1,".\z","(🐟)"),")")))&")",regexextract(TRIM(flatten(split(regexreplace(A1,".\z","(🐟)"),")")))&")","\((.+)\)"),regexextract(REGEXREPLACE(TRIM(flatten(split(regexreplace(A1,".\z","(🐟)"),")")))&")",",","|"),"\((.+)\)")),"\(🐟\)","")))

Match and Replace Commas outside Multiple Brackets

In the above example in Google Sheets, we have written the formula in three steps.

To replace commas outside multiple brackets in a long string in Google Sheets, you can use steps 1 and 3.

In step 2 above, we have used the B1 formula after modifying A1 with D1:D3.

Here, instead, we should use the B2 formula after modifying A1 with D1:D3.

That’s all.

In the below sample sheet, you will get all the formulas.

Example Sheet 191021