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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,569
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,488
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,569
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
23
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
50,750
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,488
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,118,522
Messages
5,572,633
Members
412,475
Latest member
JaredNAU
Top