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.

 

[Peter_SSs]

Way over my head but thanks anyway.

Sorry, my comments really were not meant for you, since you are using Excel 2010, but for KP117 (or other readers who may have the latest versions of Excel 365) which is why I quoted KP117 at the top of my post.

At least you have a working solution for what you wanted.