How to select certain string from a field. Please help.

[florayaoyao]

Hi Alteryx gurus, 

I have a comment field which also include Sales Order Number,  I only want to keep the SO number and get rid of the rest.     

some are like "WEB-SO1767639 REL 071217 KS" , but some other not start with "WEB", like "2 - Inv WEB-SI1333765-3 closed" .  (The sample file is attached) 

I need two  fields as results,  "WEB-SO1767639" and "1767639".       

I tried  TRIM  Function, but not return what I need... Anyone please advise.  

Thanks,

Flora 

[NicoleJohnson · Accepted solution]

Try the following RegEx string in the RegEx tool (Parse method):

.*(WEB-SO\d+).*

This ignores anything before "WEB-SO followed by numbers", and then ignores anything after the last number. (NOTE: This assumes that your SO numbers are always numbers, let me know if that's not the case.)

To get just the numbers, you could do another RegEx tool that just uses the following formula in a RegEx tool (Parse method) on the results of the first one. (You could also use RegEx_Replace formula in the formula tool, same RegEx string)

WEB-SO(\d+)

That will isolate just the numbers for you within the marked group (\d+).

Does that work for you?

Cheers!

NJ

[florayaoyao]

Thank you Nicole!  

This is exactly what I need..   However,  there are about 22 items not returning any values, but they do have "WEB-S/O" format....  what would cause this?  Any thought?  

Thank you!! 

Flora 

[florayaoyao]

Thanks   Nicole,  

you could disregard my second question, I figured it out.   It is caused by SI instead of SO, so I just use two Reg Ex, and then union the data together. 

Best,

Flora

[NicoleJohnson]

Glad it's working! This might be captured in your new modified formula, but it looks like there might be a few that are in other "rogue" formats too - such as a 0 (zero) instead of the letter O, and spaces in between the dash separating WEB and SO.

You might give this one a try to capture all those scenarios in one formula, assuming you're wanting to capture basically anything that starts with WEB (i.e. WEB-SO, WEB - SO, WEB-SI, etc.) and ends with numbers:

.*(WEB\s*-\s*S\/*[O0I]\d+\>)\s*.*$

This one will look for something that starts with WEB (ignoring anything before it), may or may not have spaces around the dash (\s-\s), then the letter S, then may or may not have a /, then either O, 0 or I... then some digits, then may or may not be additional characters (\s*.*$).

I think this will give you more results for those slightly varied situations? Good luck! :)

NJ

[florayaoyao]

Thank you Nicole, this is even more helpful.   

By chance,  if you know,  could you share some resources for me to learn more about the Reg Exp function?    The whole thing looks so complicated to me...  

Thanks,

Flora 

[NicoleJohnson]

Absolutely! I was a RegEx novice like 3 months ago, but once you start to crack the code, you start seeing how it could be used everywhere!! VERY dynamic... http://regex101.com is great for testing out strings and getting explanations for how different segments are working. The others are great resources for learning what all the expressions do!

- https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Tool-Mastery-RegEx/ta-p/37689

- http://www.rexegg.com/regex-anchors.html

- http://regexr.com/

- http://regex101.com

- http://www.rexegg.com/regex-quantifiers.html

- https://regexone.com/

Hope that helps! Happy RegEx-ing! :)

NJ

[florayaoyao]

Thank you!! Wish I could give you thousands of stars!  ^v^ 

Flora