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
Could you have something like "2 x Coffee (Instant, Papercup), Adult Shorts, Adult Polo Shirt (Large, Navy)" where the comma after shorts should remain?
I've allowed for up 15 different products under one transaction. I do need to get rid of just the commas within the brackets but the brackets to remain. So, yes, the comma after Shorts would remain but not the comma between Large and Navy.
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,549
Office Version
  1. 365
Platform
  1. Windows
In that case the suggestions made so far will not work, as they will remove the comma after Shorts.
If all items had variants as shown in your op it would be simple, but with items without variants, not so.
 

jeffdolton

Board Regular
Joined
Dec 21, 2020
Messages
69
Office Version
  1. 2010
Platform
  1. Windows
In that case the suggestions made so far will not work, as they will remove the comma after Shorts.
If all items had variants as shown in your op it would be simple, but with items without variants, not so.
Hi, I used the three formulae previously given on the following:
3 x Hogs Back TEA 500ml, 2 x Coffee (Instant, Papercup), Adult Polo Shirt (Large, Navy), shirt (red, large, one), Hogs Back TEA
and I got the following result:
3 x Hogs Back TEA 500ml 2 x Coffee (Instant Papercup), Adult Polo Shirt (Large Navy), shirt (red large one), Hogs Back TEA
it seems the formula has worked. Am I missing something?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,549
Office Version
  1. 365
Platform
  1. Windows
It's also removed the comma after 500ml
 

jeffdolton

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

ADVERTISEMENT

It's also removed the comma after 500ml
Good spot, thanks. I can't see any other wrong deletions. Do you have a formula that works?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,549
Office Version
  1. 365
Platform
  1. Windows
Fraid not, it's beyond my knowledge of formulae.
 

jimrward

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

ADVERTISEMENT

Looks like a custom function will be required to get around the logic not possible with regular formulas
the function should be simple enough will see what I can put together
 

jeffdolton

Board Regular
Joined
Dec 21, 2020
Messages
69
Office Version
  1. 2010
Platform
  1. Windows
Looks like a custom function will be required to get around the logic not possible with regular formulas
the function should be simple enough will see what I can put together
Thanks so much 👍
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
Edit: Sorry, incorrect code. I will be back.
 
Last edited:

jimrward

Well-known Member
Joined
Feb 24, 2003
Messages
1,784
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
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)
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,127,585
Messages
5,625,656
Members
416,124
Latest member
DeMoNloK

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
Top