Regex with Replace in an Excel cell - excluding string in brackets

Question

Hello, ich have a question regards replacing commas in Excel 365 cell with ### except in brackets.

With Regex? But how?

Bsp.:
Hello (bla, bla, blubb), This is a test, but hopefully you have a solution (this would be great, awesome and super), I hope to get an answer :-)

After replacing it should look like: Replace „,“ with „###“, BUT not in the brackets (…)

Hello (bla, bla, blubb)### This is a test### but hopefully you have a solution (this would be great, awesome and super)### I hope to get an answer :-)

Any idea?

Thank you!

Greetings peach777

Answer 1

Hi @Peach777

Welcome to Q&A forum ~

Please check whether the following formual is helpful.
=SUBSTITUTE(LEFT(A1,FIND("(",A1)-1),",","###")&MID(A1,FIND("(",A1),FIND(")",A1)-FIND("(",A1)+1)&SUBSTITUTE(RIGHT(A1,LEN(A1)-FIND(")",A1)),",","###")

• LEFT(A1,FIND("(",A1)-1) is to get the content on the left of "(".
  SUBSTITUTE(LEFT(A1,FIND("(",A1)-1)),",","###") is to replace "," with "###" for the result above.
• MID(A1,FIND("(",A1),FIND(")",A1)-FIND("(",A1)+1) is to the brackets and the content between them.
• RIGHT(A1,LEN(A1)-FIND(")",A1))" is to get the content on the right of ")".
  SUBSTITUTE(RIGHT(A1,LEN(A1)-FIND(")",A1)),",","###") is to replace "," with "###" for the result above.

-------- Update ---------

First of all, I found a piece of code about custom function from lecxe's sharing on the thread "Remove commas if they are between brackets". (Please Note: Since the web site is not hosted by Microsoft, the link may change without notice. Microsoft does not guarantee the accuracy of this information.)

Then I change the "" in "!" as following to replace all commas in all brackets with exclamation mark. If needed, you could use other symbols you like.

Function DelCommaBrk(s As String) As String  
  
With CreateObject("VBScript.RegExp")  
    .Pattern = ",(?=[^(]*\))"  
    .Global = True  
    DelCommaBrk = .Replace(s, "!")  
End With  
End Function  

You could press Alt + F11 to open VBA Editor, insert a new module, then put this code into this module.
Go back to Excel worksheet, use this custom function DelCommaBrk to change all commas in all brackets to "!".

And then use the "SUBSTITUTE" function to replace all commas to "###".

At last, you could still use "SUBSTITUTE" function to replace all "!" in brackets to "," back.

Hope the information is helpful.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---