Removing a comma inside a parenthesis or parentheses

jeffdolton

Board Regular
Joined
Dec 21, 2020
Messages
69
Office Version
  1. 2010
Platform
  1. Windows
I receive a weekly csv extract from an electronic point of sale system. All purchases made under one transaction are combined in one cell and are separated by a comma. However, a variant of a purchase is shown inside parenthesis. If there is more than one variant then these are separated by a comma within parentheses.

For example 2 x Coffee (Instant, Papercup), Adult Polo Shirt (Large, Navy)

I have a formula to split each purchase type wherever a comma appears but I now need a formula or function to remove a comma wherever it appears inside parentheses. All commas outside parentheses are to remain.

After applying the formula the output should look like this 2 x Coffee (Instant Papercup), Adult Polo Shirt (Large Navy)

Thanks for your help.
 

jeffdolton

Board Regular
Joined
Dec 21, 2020
Messages
69
Office Version
  1. 2010
Platform
  1. Windows
I believe this more compact macro will also work...
VBA Code:
Function DelCommaInParen(S As String) As String
  Dim X As Long, Arr() As String
  If S Like "*(*)*" Then
    Arr = Split(Replace(S, ")", ")("), "(")
    For X = 1 To UBound(Arr) Step 2
      Arr(X) = Application.Trim(Replace(Arr(X), ",", " "))
    Next
    DelCommaInParen = Replace(Join(Arr, "("), ")(", ")")
  Else
    DelCommaInParen = S
  End If
End Function
Thanks Rick, I've used the earlier code but thanks for taking the time on this.
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,690
Office Version
  1. 2010
Platform
  1. Windows
Thanks Rick, I've used the earlier code but thanks for taking the time on this.
Did you see Fluff's "I'm a little late..." post (Message #23) where he points out that Jim's code mishandled this text string... "abc(de,fg" ?
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Jim's code mishandled this text string... "abc(de,fg"
I'm guessing that most likely such a text string will not occur with the OP's data so Jim's code will probably be fine.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,690
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

I'm guessing that most likely such a text string will not occur with the OP's data
You are probably right, but it can't hurt to make sure the OP is alerted to it... just in case.
 

jeffdolton

Board Regular
Joined
Dec 21, 2020
Messages
69
Office Version
  1. 2010
Platform
  1. Windows
You are probably right, but it can't hurt to make sure the OP is alerted to it... just in case.
Thank you all for making me aware of the circumstances when Jim’s Code may not work. I think it will be fine for the data I’m handling.
 

KP117

New Member
Joined
Oct 22, 2020
Messages
24
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

Data in A1, try this

=SUBSTITUTE(TEXTJOIN("",TRUE,IFERROR(IF(FIND("(",TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A$1," (","-("),")","),-"),"-",REPT(" ",LEN($A$1))),1+(ROW($A$1:$A$10)-1)*LEN($A$1),LEN($A$1)))),SUBSTITUTE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A$1," (","-("),")","),-"),"-",REPT(" ",LEN($A$1))),1+(ROW($A$1:$A$10)-1)*LEN($A$1),LEN($A$1))),",","")),TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A$1," (","-("),")","),-"),"-",REPT(" ",LEN($A$1))),1+(ROW($A$1:$A$10)-1)*LEN($A$1),LEN($A$1))))),"("," (")
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,033
Office Version
  1. 365
Platform
  1. Windows
@KP117
The OP is using Xl 2010 & therefore does not have the Textjoin function.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Data in A1, try this
Apart from Fluff's valid point, there are two other potential issues with this suggestion even for users who may have TEXTJOIN
  • If rows are subsequently inserted at the top of the sheet, the formula will return incorrect results
  • It may well not happen with this OP's data but if the text strings being processed get fairly long, the various substitutions of as many spaces as the length of the original string can cause the character limit of the TEXTJOIN function to be exceeded and an error is returned.

However, the idea of a worksheet formula solution without vba got me thinking about the new LAMBDA function (see Announcing LAMBDA and Excel LAMBDA Functions ) and its ability to use recursion. The LAMBDA function is not available to many users yet but in due course should become available to all 365 subscribers at least. So I though I would give it a try on this exercise.

The following formula is entered into the Name Manager as the 'Refers to:' for the name REPLCOMMA as shown here.
I have assumed that
- "%" and "^" characters will not be used in the original text strings.
- parentheses in the text strings will be evenly matched and not nested.

Excel Formula:
=LAMBDA(s,IF(ISNUMBER(FIND("(",s)),REPLCOMMA(SUBSTITUTE(s,REPLACE(LEFT(s,FIND(")",s)),1,FIND("(",s)-1,""),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(REPLACE(LEFT(s,FIND(")",s)),1,FIND("(",s)-1,""),",",""),"(","%"),")","^"))),SUBSTITUTE(SUBSTITUTE(s,"%","("),"^",")")))
1609112416994.png


The function is then used in the worksheet as a normal function

jeffdolton_1.xlsm
AB
23 x Hogs Back TEA 500ml, 2 x Coffee (Instant, Papercup), Adult Polo Shirt (Large, Navy), shirt (red, large, one), Hogs Back TEA3 x Hogs Back TEA 500ml, 2 x Coffee (Instant Papercup), Adult Polo Shirt (Large Navy), shirt (red large one), Hogs Back TEA
34 x Hogs Back TEA 500ml, 2 x Coffee (Instant, Papercup, with sugar, soy milk), Adult Polo Shirt (Large, Navy), shirt (red, large, one), Hogs Back TEA4 x Hogs Back TEA 500ml, 2 x Coffee (Instant Papercup with sugar soy milk), Adult Polo Shirt (Large Navy), shirt (red large one), Hogs Back TEA
4 
5one, two, three, four, fiveone, two, three, four, five
6(one, two, three), four, five(one two three), four, five
Sheet1
Cell Formulas
RangeFormula
B2:B6B2=REPLCOMMA(A2)
 

jeffdolton

Board Regular
Joined
Dec 21, 2020
Messages
69
Office Version
  1. 2010
Platform
  1. Windows
Way over my head, but thanks anyway!
Apart from Fluff's valid point, there are two other potential issues with this suggestion even for users who may have TEXTJOIN
  • If rows are subsequently inserted at the top of the sheet, the formula will return incorrect results
  • It may well not happen with this OP's data but if the text strings being processed get fairly long, the various substitutions of as many spaces as the length of the original string can cause the character limit of the TEXTJOIN function to be exceeded and an error is returned.

However, the idea of a worksheet formula solution without vba got me thinking about the new LAMBDA function (see Announcing LAMBDA and Excel LAMBDA Functions ) and its ability to use recursion. The LAMBDA function is not available to many users yet but in due course should become available to all 365 subscribers at least. So I though I would give it a try on this exercise.

The following formula is entered into the Name Manager as the 'Refers to:' for the name REPLCOMMA as shown here.
I have assumed that
- "%" and "^" characters will not be used in the original text strings.
- parentheses in the text strings will be evenly matched and not nested.

Excel Formula:
=LAMBDA(s,IF(ISNUMBER(FIND("(",s)),REPLCOMMA(SUBSTITUTE(s,REPLACE(LEFT(s,FIND(")",s)),1,FIND("(",s)-1,""),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(REPLACE(LEFT(s,FIND(")",s)),1,FIND("(",s)-1,""),",",""),"(","%"),")","^"))),SUBSTITUTE(SUBSTITUTE(s,"%","("),"^",")")))
View attachment 28630

The function is then used in the worksheet as a normal function

jeffdolton_1.xlsm
AB
23 x Hogs Back TEA 500ml, 2 x Coffee (Instant, Papercup), Adult Polo Shirt (Large, Navy), shirt (red, large, one), Hogs Back TEA3 x Hogs Back TEA 500ml, 2 x Coffee (Instant Papercup), Adult Polo Shirt (Large Navy), shirt (red large one), Hogs Back TEA
34 x Hogs Back TEA 500ml, 2 x Coffee (Instant, Papercup, with sugar, soy milk), Adult Polo Shirt (Large, Navy), shirt (red, large, one), Hogs Back TEA4 x Hogs Back TEA 500ml, 2 x Coffee (Instant Papercup with sugar soy milk), Adult Polo Shirt (Large Navy), shirt (red large one), Hogs Back TEA
4 
5one, two, three, four, fiveone, two, three, four, five
6(one, two, three), four, five(one two three), four, five
Sheet1
Cell Formulas
RangeFormula
B2:B6B2=REPLCOMMA(A2)
Way over my head but thanks anyway.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,767
Messages
5,597,987
Members
414,199
Latest member
GDtoBMe

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top