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]

here is a quick attempt which appears to satisfy the 500ml example above, it tests each character in a loop and switches the deletion of a comma ON when it finds a ( and OFF when it find a closing )

Function CleanMyString(OldString As String) As String

Dim KeepComma As Boolean
KeepComma = True

Dim NewString As String
NewString = ""

Dim NextChar As String

For j = 1 To Len(OldString)
    NextChar = Mid(OldString, j, 1)
    If NextChar = "(" Then
        KeepComma = False
    End If
    If NextChar = ")" Then
        KeepComma = True
    End If
    If NextChar = "," And KeepComma = False Then
        NextChar = ""
    End If
    NewString = NewString & NextChar
Next j

CleanMyString = NewString

End Function

used as =CleanMyString(A1)

That is perfect, works like a dream - thanks so much!

 

[jimrward]

Thanks for the feedback,
Let me know if your data throws up any bogey ones

 

[Peter_SSs]

I'm a little late but this is my revised UDF. It does require less looping than the previous suggestion which would probably only be an issue if the strings are very long and/or the data is very large.
It is also may well not be possible to have data like in row 6 below, but our functions do return different results for that case where the comma is not contained within parentheses so my code does not remove it.

Anyway, it is here for you to consider.

Function DelCommaInParen(s As String) As String
  Dim posStart As Long, PosOpen As Long, PosClose As Long, PosComma As Long
  
  PosOpen = InStr(posStart + 1, s, "(")
  Do Until PosOpen = 0
    PosClose = InStr(PosOpen, s, ")")
    If PosClose > PosOpen Then
      PosComma = InStr(PosOpen, Left(s, PosClose), ",")
      Do Until PosComma = 0
        Mid(s, PosComma, 1) = " "
        PosOpen = PosComma
        PosComma = InStr(PosOpen, Left(s, PosClose), ",")
      Loop
    Else
      Exit Do
    End If
    PosOpen = InStr(PosClose + 1, s, "(")
  Loop
  DelCommaInParen = Application.Trim(s)
End Function

jeffdolton_1.xlsm
	A	B	C
1		Peter	Jim
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 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 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	one, two, three, four, five
6	(one, two, three), four, five	(one two three), four, five	(one two three), four, five
7	abc(de,fg	abc(de,fg	abc(defg
8	abc, def)	abc, def)	abc, def)
Sheet1

Cell Formulas
Range	Formula
B2:B8	B2	=DelCommaInParen(A2)
C2:C8	C2	=CleanMyString(A2)

 

[sandy666]

just for fun

raw
3 x Hogs Back TEA 500ml, 2 x Coffee (Instant, Papercup), Adult Polo Shirt (Large, Navy), shirt (red, large, one), Hogs Back TEA
Custom
3 x Hogs Back TEA 500ml, 2 x Coffee (Instant Papercup), Adult Polo Shirt (Large Navy), shirt (red large one), Hogs Back TEA

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Left = Table.ReplaceValue(Source,"(","@(",Replacer.ReplaceText,{"raw"}),
    Right = Table.ReplaceValue(Left,")",")@",Replacer.ReplaceText,{"raw"}),
    Split = Table.ExpandListColumn(Table.TransformColumns(Right, {{"raw", Splitter.SplitTextByDelimiter("@", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "raw"),
    IF0 = Table.AddColumn(Split, "Custom", each if Text.Contains([raw], "(") then [raw] else null),
    ReplaceComma = Table.ReplaceValue(IF0,", "," ",Replacer.ReplaceText,{"Custom"}),
    IF1 = Table.AddColumn(ReplaceComma, "Custom.1", each if Text.Contains([raw], "(") then [Custom] else [raw]),
    Base = Table.AddColumn(IF1, "Custom.2", each 1),
    Group = Table.Group(Base, {"Custom.2"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Custom", each [Count][Custom.1]),
    Extract = Table.TransformColumns(List, {"Custom", each Text.Combine(List.Transform(_, Text.From), ""), type text}),
    TSC = Table.SelectColumns(Extract,{"Custom"})
in
    TSC

 

[jimrward]

Parentheses are always a pain when parsing data where there is an imbalance as making a guess is sometimes not the correct call
the only thing I might change in my code is make the substitution of the comma to a space instead of a NULL then TRIM the string before returning to remove double spaces

 

[jeffdolton]

Thanks for the feedback,
Let me know if your data throws up any bogey ones

No problems at all, many thanks.

 

[jeffdolton]

just for fun

raw
3 x Hogs Back TEA 500ml, 2 x Coffee (Instant, Papercup), Adult Polo Shirt (Large, Navy), shirt (red, large, one), Hogs Back TEA
Custom
3 x Hogs Back TEA 500ml, 2 x Coffee (Instant Papercup), Adult Polo Shirt (Large Navy), shirt (red large one), Hogs Back TEA

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Left = Table.ReplaceValue(Source,"(","@(",Replacer.ReplaceText,{"raw"}),
    Right = Table.ReplaceValue(Left,")",")@",Replacer.ReplaceText,{"raw"}),
    Split = Table.ExpandListColumn(Table.TransformColumns(Right, {{"raw", Splitter.SplitTextByDelimiter("@", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "raw"),
    IF0 = Table.AddColumn(Split, "Custom", each if Text.Contains([raw], "(") then [raw] else null),
    ReplaceComma = Table.ReplaceValue(IF0,", "," ",Replacer.ReplaceText,{"Custom"}),
    IF1 = Table.AddColumn(ReplaceComma, "Custom.1", each if Text.Contains([raw], "(") then [Custom] else [raw]),
    Base = Table.AddColumn(IF1, "Custom.2", each 1),
    Group = Table.Group(Base, {"Custom.2"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Custom", each [Count][Custom.1]),
    Extract = Table.TransformColumns(List, {"Custom", each Text.Combine(List.Transform(_, Text.From), ""), type text}),
    TSC = Table.SelectColumns(Extract,{"Custom"})
in
    TSC

Wow! many thanks but I've now incorporated the earlier code into my programme.

 

[jeffdolton]

I'm a little late but this is my revised UDF. It does require less looping than the previous suggestion which would probably only be an issue if the strings are very long and/or the data is very large.
It is also may well not be possible to have data like in row 6 below, but our functions do return different results for that case where the comma is not contained within parentheses so my code does not remove it.

Anyway, it is here for you to consider.

Function DelCommaInParen(s As String) As String
  Dim posStart As Long, PosOpen As Long, PosClose As Long, PosComma As Long
 
  PosOpen = InStr(posStart + 1, s, "(")
  Do Until PosOpen = 0
    PosClose = InStr(PosOpen, s, ")")
    If PosClose > PosOpen Then
      PosComma = InStr(PosOpen, Left(s, PosClose), ",")
      Do Until PosComma = 0
        Mid(s, PosComma, 1) = " "
        PosOpen = PosComma
        PosComma = InStr(PosOpen, Left(s, PosClose), ",")
      Loop
    Else
      Exit Do
    End If
    PosOpen = InStr(PosClose + 1, s, "(")
  Loop
  DelCommaInParen = Application.Trim(s)
End Function

jeffdolton_1.xlsm
	A	B	C
1		Peter	Jim
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 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 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	one, two, three, four, five
6	(one, two, three), four, five	(one two three), four, five	(one two three), four, five
7	abc(de,fg	abc(de,fg	abc(defg
8	abc, def)	abc, def)	abc, def)
Sheet1

Cell Formulas
Range	Formula
B2:B8	B2	=DelCommaInParen(A2)
C2:C8	C2	=CleanMyString(A2)

Thank you very much. I've used the earlier code given to me and I'm happy with that but thank you for taking the time to put this togther.

 

[sandy666]

Wow! many thanks but I've now incorporated the earlier code into my programme.

no problem, have a nice day

 

[Rick Rothstein]

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