Change column values of similar patterns

[wenjuanchen]

Hello everyone,

I am changing the value of about 15 string fields with similar patterns. They look like the following:

WG - LI6 - Chinese Walnut
WG - LI5 - Walnut
WG - LI4 - Nut
WG - LI3 - Fruit
WG - LI2 - Plant
WG - LI1 - Object

I want to trim the WG-LIx - part and only leave the last word(s), so the final result will look like:

Chinese Walnut
Walnut
Nut
Fruit
Plant
Object

I can use the TrimLeft Function to trim them one by one in the following format: TrimLeft([Column Name], "WG - LI6 - "). However, this led to a couple of issues:

1) After trimming, some columns are missing one character at the beginning. For example, Chinese Walnut ends up with hinese Walnut.
2) I have to do it 15 times for 15 columns;

 Is there a way to fix the missing characters? In addition, is there a better way to do it? Can anyone help?

Thank you so much in advance.

[jdunkerley79]

Of course.

It identifies the first marked group in the regular expression. In other the first part surrounded by brackets - in this case (.*) which is the part after the WG - LIx

[jdunkerley79 · Accepted solution]

Try using a formula tool with an expression:

REGEX_Replace([Field1],"WG - LI\d+ - (.*)", "$1")

That should remove the WG - Lix part

Sample attached

[wenjuanchen]

Thank you so much!

Can you explain what "$1" does in the formula?