Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Change column values of similar patterns

wenjuanchen
8 - Asteroid


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.

3 REPLIES 3
jdunkerley79
ACE Emeritus
ACE Emeritus
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
ACE Emeritus
ACE Emeritus

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
8 - Asteroid

Thank you so much!

 

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

 

Labels