Removing a comma inside a parenthesis or parentheses

jeffdolton

Board Regular
Joined
Dec 21, 2020
Messages
100
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.
 
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 )
Code:
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!
 
Upvote 0

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Thanks for the feedback,
Let me know if your data throws up any bogey ones
 
Upvote 0
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.

VBA Code:
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

Cell Formulas
RangeFormula
B2:B8B2=DelCommaInParen(A2)
C2:C8C2=CleanMyString(A2)
 
Upvote 0
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

Power Query:
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
 
Upvote 0
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
 
Upvote 0
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

Power Query:
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.
 
Upvote 0
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.

VBA Code:
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

Cell Formulas
RangeFormula
B2:B8B2=DelCommaInParen(A2)
C2:C8C2=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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

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
Back
Top