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
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!
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

jimrward

Well-known Member
Joined
Feb 24, 2003
Messages
1,782
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
Thanks for the feedback,
Let me know if your data throws up any bogey ones
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,488
Office Version
  1. 365
Platform
  1. Windows
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)
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,391
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
 

jimrward

Well-known Member
Joined
Feb 24, 2003
Messages
1,782
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Dec 21, 2020
Messages
69
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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.
 

jeffdolton

Board Regular
Joined
Dec 21, 2020
Messages
69
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,569
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,522
Messages
5,572,633
Members
412,475
Latest member
JaredNAU
Top