Extract number with specific format

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?

Regex

Accepted answers

vishwa_0308

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

MarqueeCrew

@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

All comments

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 levelT2PRJ0011154
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 modelT2PRJ0101031
BAU CR 54812 Create new TP role T2PRJ4001054

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703