Extract number with specific format

[ShaKey]

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?

[BenMoss]

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]

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 · Accepted solution]

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]

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]

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

[MarqueeCrew · Accepted solution]

@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:  

Cheers,

Mark