Text to column and then delete all between two characters

Question

Hi All ,

I have the following text field

E.G : $0-$100 (1)|$101-$250 (17)|$251-$500 (17)|Men's (35)|Yellow Gold (35)|20.00 Inch (35)

I am trying to achieve the following

* Parse everything after |
* Delete everything that starts with '(' and ends with ')'

I tried the following :

* Text to column - delimiter is | - WORKS 
* Multi Field Formula and apply Regex  (.*)\(.* - DOES NOT WORK 
* RegEx Tool with expression (.*)\(.* - WORKS ONLY ON THE LAST - Eg Only removed (35) from above example

Can someone point me in the right direction.

danrh · Accepted Answer

Try this for your RegEx:

$.*?$

In the attached it looks like it's working as needed.  I put the RegEx before the Text to Columns just so I could do it on a single column instead of using the Multi-Field.