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.

 

[jimrward]

Ok thinking out loud as I am not near my pc at the moment I would try the following in stages
assuming your data is in A1
in B1 use SUBSTITUTE(A1,....) to change ), for a ~
in C1 use SUBSTITUTE(B1....) to change , to a space
in D1 use SUBSTITUTE(D1....)to change ~ back to ),

look up the SUBSTITUTE function and replace the ....) above with the correct syntax if I use double quotes on my iPad it produces an error on a pc

doing it in separate columns will allow you to see intermediate results to check allis well at each stage

 

[jeffdolton]

Ok thinking out loud as I am not near my pc at the moment I would try the following in stages
assuming your data is in A1
in B1 use SUBSTITUTE(A1,....) to change ), for a ~
in C1 use SUBSTITUTE(B1....) to change , to a space
in D1 use SUBSTITUTE(D1....)to change ~ back to ),

look up the SUBSTITUTE function and replace the ....) above with the correct syntax if I use double quotes on my iPad it produces an error on a pc

doing it in separate columns will allow you to see intermediate results to check allis well at each stage

Many thanks for your quick response. I applied your formula exactly as specified. Unfortunately, the inside brackets were left intact and only one of the outer brackets changed.

 

[EXCEL MAX]

Try this...
=SUBSTITUTE(SUBSTITUTE(A1;", ";" ");") ";"), ")

 

[jeffdolton]

Try this...
=SUBSTITUTE(SUBSTITUTE(A1;", ";" ");") ";"), ")

Thanks, I've tried this and a 'You've entered too few arguments for this function' message is returned.

 

[jimrward]

my suggestion works for me

Book1
	A	B	C	D
1	2 x Coffee (Instant, Papercup), Adult Polo Shirt (Large, Navy)	2 x Coffee (Instant, Papercup~ Adult Polo Shirt (Large, Navy)	2 x Coffee (Instant Papercup~ Adult Polo Shirt (Large Navy)	2 x Coffee (Instant Papercup), Adult Polo Shirt (Large Navy)
Sheet1

Cell Formulas
Range	Formula
B1	B1	=SUBSTITUTE(A1,"),","~")
C1	C1	=SUBSTITUTE(B1,",","")
D1	D1	=SUBSTITUTE(C1,"~","),")

 

[jeffdolton]

my suggestion works for me

Book1
	A	B	C	D
1	2 x Coffee (Instant, Papercup), Adult Polo Shirt (Large, Navy)	2 x Coffee (Instant, Papercup~ Adult Polo Shirt (Large, Navy)	2 x Coffee (Instant Papercup~ Adult Polo Shirt (Large Navy)	2 x Coffee (Instant Papercup), Adult Polo Shirt (Large Navy)
Sheet1

Cell Formulas
Range	Formula
B1	B1	=SUBSTITUTE(A1,"),","~")
C1	C1	=SUBSTITUTE(B1,",","")
D1	D1	=SUBSTITUTE(C1,"~","),")

My apologies, I've copied your three formulae and it works perfectly! Would you do me one last favour and combine the three formulae into one.

Many thanks, Jeff

 

[jimrward]

The reason I did in stages was so you can see each stage to check for errors it will be an hour or so before I get back to my pc

 

[jeffdolton]

The reason I did in stages was so you can see each stage to check for errors it will be an hour or so before I get back to my pc

Okay, many thanks.

 

[Fluff]

Could you have something like "2 x Coffee (Instant, Papercup), Adult Shorts, Adult Polo Shirt (Large, Navy)" where the comma after shorts should remain?