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

Extract number with specific format

ShaKey
5 - Atom

Hi All, been going around many posts in the last few days but cannot find exactly what I need so maybe someone on here can assist.

 

I want to extract a specific number format from a string field.

 

e.g. CR 56648 Update the mis-match emailT2PRJ0001022

 

In the above I want to extract only 56648.  The number is always 5 digits and starts with a 5.

 

Any suggestions?

6 REPLIES 6
BenMoss
ACE Emeritus
ACE Emeritus
Could you share more examples of the strings we should expect! We want to ensure you get a solution that is repeatable for all variations.

Based on the one you sent you could just use the text to columns tool on space delimiter and go for three columns. Your value field would then be in your 2nd field.

But I don't think this will be a solution given further strings.
ShaKey
5 - Atom

Thanks Ben, here are some additional examples (the number I want in bold)

 

CR 52710 :Additional user fields (Free Text) at WBS level​T2PRJ0011154
CR 54938:Wave 3 - rollout of Checklist ​T2PRJ0021324
CR 58695 - Billing report ​T2PRJ0101071
BAU CR 57676 - CDMS Finance interface change FSSI1484  - ​T2PRJ0801021
CR 52637 -   Marine- Distributor cost model​T2PRJ0101031
BAU CR 54812 Create new TP role ​T2PRJ4001054

vishwa_0308
11 - Bolide

Hi @ShaKey,

 

There are many ways you can findout the 5 digits starting with 5.

Just parse it using any one of the regular expression in regex tool:

1. (5....)   --Starting with 5 and then four dots for remaining 4 letters.

2. CR (\d{5}) -- to look out for 5 digit after letters "CR".

 

Thanks,

Vishwa

ShaKey
5 - Atom

Thanks Vishwa

 

Both of your suggestions work.  I would prefer the (5....) as there is no guarantee that CR will exist before the number but using that also collects some wrong entries (e.g. "5 TAB").  Can I replace the .... with another character to specify numbers only?

vishwa_0308
11 - Bolide

use this only : (\d{5}) - for only 5 digits in a string.

MarqueeCrew
20 - Arcturus
20 - Arcturus

@ShaKey,

 

New favorite:

\b5\d{4}\b

.... read the requirements and found a better solution.

 

I like:

 

\b\d{5}\b

This will ensure that what you find is only 5 digits.  \b is a word boundry and is useful for your data.  Using Regex101.com, I can show you the difference:  

 

capture.jpg

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Labels