Removing a comma inside a parenthesis or parentheses

[jeffdolton]

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]

I believe this more compact macro will also work...

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.

 

[Rick Rothstein]

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" ?

 

[Fluff]

Did you see Fluff's "I'm a little late..." post (Message #23)

That was Peter's post not mine.

 

[Peter_SSs]

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]

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]

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]

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]

@KP117
The OP is using Xl 2010 & therefore does not have the Textjoin function.

 

[Peter_SSs]

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.

=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,"%","("),"^",")")))

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

jeffdolton_1.xlsm
	A	B
2	3 x Hogs Back TEA 500ml, 2 x Coffee (Instant, Papercup), Adult Polo Shirt (Large, Navy), shirt (red, large, one), Hogs Back TEA	3 x Hogs Back TEA 500ml, 2 x Coffee (Instant Papercup), Adult Polo Shirt (Large Navy), shirt (red large one), Hogs Back TEA
3	4 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 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
5	one, two, three, four, five	one, two, three, four, five
6	(one, two, three), four, five	(one two three), four, five
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	=REPLCOMMA(A2)

 

[jeffdolton]

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.

=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
	A	B
2	3 x Hogs Back TEA 500ml, 2 x Coffee (Instant, Papercup), Adult Polo Shirt (Large, Navy), shirt (red, large, one), Hogs Back TEA	3 x Hogs Back TEA 500ml, 2 x Coffee (Instant Papercup), Adult Polo Shirt (Large Navy), shirt (red large one), Hogs Back TEA
3	4 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 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
5	one, two, three, four, five	one, two, three, four, five
6	(one, two, three), four, five	(one two three), four, five
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	=REPLCOMMA(A2)

Way over my head but thanks anyway.