Replacing commas inside or outside brackets in Google Sheets can be tricky because there’s no built-in function to do this selectively. With a combination of REGEXREPLACE, SUBSTITUTE, and ArrayFormula, you can achieve it efficiently.
Why Replace Commas Inside or Outside Brackets?
Sometimes you have a long text string with tasks and sub-tasks. You may want to split main tasks by commas but keep sub-tasks grouped within brackets.
Example (cell A1):
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 7Expected Output (after replacing commas inside brackets):
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 7Notice that outer commas split the main tasks, while inner commas inside brackets are replaced with a pipe (|) to preserve grouping.
1. Replace Commas Inside Brackets
Use this formula in B1:
=ArrayFormula(
LET(
text, SPLIT(SUBSTITUTE(A1, ")", ")🐟"), "🐟"),
fnl, REGEXREPLACE(text, "\(([^)]*)\)",
"(" & SUBSTITUTE(REGEXEXTRACT(text,"\(([^)]*)\)"), ",", "|") & ")"),
JOIN(" ", IFNA(fnl, text))
)
)Output:
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 7Formula Breakdown
SUBSTITUTE(A1, ")", ")🐟")– Marks closing brackets to split the text.SPLIT(..., "🐟")– Splits the text into chunks for each bracketed section.
REGEXREPLACE(..., "(" & SUBSTITUTE(REGEXEXTRACT(...), ",", "|") & ")")–- Finds the first bracketed section
( … )in each chunk. REGEXEXTRACTcaptures the content inside the parentheses.SUBSTITUTE(..., ",", "|")replaces commas inside the brackets with a pipe (|)."(" & … & ")"wraps the modified content back in parentheses.- Because of the split, all bracketed sections in the original text are processed, one per chunk.
- Finds the first bracketed section
IFNA(fnl, text)– Keeps chunks without brackets unchanged.JOIN(" ", …)– Combines all chunks back into a single string.
2. Replace Commas Outside Brackets
Use this formula in B1 if you want to replace commas outside brackets instead:
=ArrayFormula(
LET(
text, SPLIT(SUBSTITUTE(A1, ")", ")🐟"), "🐟"),
fnl, REGEXREPLACE(SUBSTITUTE(text, ",", "|"), "\(([^)]*)\)",
"(" & SUBSTITUTE(REGEXEXTRACT(text,"\(([^)]*)\)"), "|", ",") & ")"),
JOIN(" ", IFNA(fnl, SUBSTITUTE(text, ",", "|")))
)
)Output:
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 7How This Works (Difference from “Inside Brackets” Formula)
SUBSTITUTE(text, ",", "|")– Replaces all commas in the chunk with|.REGEXREPLACE(..., "(" & SUBSTITUTE(REGEXEXTRACT(...), "|", ",") & ")")–- Restores commas inside parentheses by replacing the temporary
|back with,.
- Restores commas inside parentheses by replacing the temporary
JOIN(" ", IFNA(fnl, SUBSTITUTE(text, ",", "|")))– Combines all chunks into a single string, ensuring chunks without parentheses still have outer commas replaced.
3. Split Tasks into Rows
After replacing commas, you can split your text into separate rows:
- Choose the delimiter based on your replacement:
","if you replaced inside brackets."|"if you replaced outside brackets.
- Use SPLIT and TRIM to remove extra spaces:
=ArrayFormula(TRANSPOSE(TRIM(SPLIT(B1, "DELIMITER"))))Replace "DELIMITER" with the appropriate character: , or |.
Result:
- Each task or sub-task appears in its own row, neatly organized.
