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.
 
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.
 
Upvote 0

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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.
 
Upvote 0
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?
 
Upvote 0
It's also removed the comma after 500ml
 
Upvote 0
Fraid not, it's beyond my knowledge of formulae.
 
Upvote 0
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
 
Upvote 0
Edit: Sorry, incorrect code. I will be back.
 
Last edited:
Upvote 0
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)
 
Upvote 0
Solution

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

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