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

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

Comment.xlsx

Preparation

Accepted answers

NicoleJohnson

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!

SelectCertainString.yxmd

All comments

NicoleJohnson

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

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

WEB-SO(\d+)

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

Does that work for you?

Cheers!

SelectCertainString.yxmd

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703