Regex

[Alteryx_user_123456]

Hi All,

Seeking your help to build a regex workflow to extract 9 digit numbers from a column of text string. I've attached my sample workbook with data in 1 column. The end output should have 2 columns, 1 original column and 1 column with only 9 digit numbers. Thanks in advance for your solutions. 

[gawa]

hi @Alteryx_user_123456 

If you want to extract 9 digits number, you can specify \d{9}. In the attached example, I change field type to INT64 in Regex tool, but that can be string depending on your demand.

If you want a quick reference on REGEX, please visit the below post.

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Sharing-RegEx-Cheat-Sheet/m-p/...

[Alteryx_user_123456]

Hi Gawa, 

Thanks for the quick response. This has worked for my test set, but failed on a larger test data set. Please refer to attached for the results. How can I ensure that only 9 digit numbers are picked up if:

- The 9 digit number series begins with either "8" or "9"

- The number series is only a 9 digit series, not 10 or 11 digit series for which the regex only returns the first or last 9 digits. 

Thanks in advance!

[gawa]

@Alteryx_user_123456 (please try to mention by adding @ so as not miss notification)

You can modify Regex expression to .*([89]\d{8}).* 

[Alteryx_user_123456]

Hi @gawa (your user name does not pop up, not sure if this works)

This solution still gives me results where I'm not expecting them. Is there a way to modify such that a 9 digit series is picked up only if the original text included a 9 digit series (and only a 9 digit series)?

[gawa]

@Alteryx_user_123456  Now I see your requirement, that should be conditional Regex is needed; extract data only when the exact 9 digit number starting from 8 or 9.

Then, please try the attached WF instead. My WF involves not only using single Regex tool but also other basic tools so it is easier to understand, rather than writing complicated regex expression.

Please try to run my WF with your real data, and see it works or not properly.

I apologize for not having reaching out what you wanted to do soon.

[Alteryx_user_123456]

Hi @gawa 

Thanks, this addresses most of my use cases! However it's still not fool proof. I've added 1 more line of sample data that has failed the WF. Can you help to modify if possible?

[gawa]

@Alteryx_user_123456 

Additional data "4862760834" has 10 digit number. As per your above statement, you want to parse data having the 9 digit number, and ignore numbers with 10 or more digits/8 or less digits. Now I'm totally confused about your requirement.

I think there remains tiny adjustment of regex, so please try to finish WF by yourself so as to meet your real data:)