jeffdolton
Board Regular
 Joined
 Dec 21, 2020
 Messages
 85
 Office Version

 2010
 Platform

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