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]

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.

 

[Fluff]

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]

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]

It's also removed the comma after 500ml

 

[jeffdolton]

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]

Fraid not, it's beyond my knowledge of formulae.

 

[jimrward]

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]

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]

Edit: Sorry, incorrect code. I will be back.

 

[jimrward]

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)