Parse digits from the right side of string

[BonusCup]

Hi,

I have some text coming in with no real pattern.  I'm trying to parse the digits from the right side that very in length from 2+ digits. Is there a regex or formula I could use to achieve this?  TIA

source	desired
2019 Test & Affiliates 2020 Letter -  12345678 GREENVILLE	12345678
2020 Test - TEST & test 123456	123456
Test Partners LP & Affiliates 2020 Letter - 87654321 Greenville	87654321
2019Test - TEST & test 654321	654321
TEST & Affiliates 2020 Letter -   23456789 Raleigh	23456789
Test_ Inc. 2020 Letter - 8327 VA BEACH	8327
2021TEST - Test Partners & Affiliates 8020	8020
Test test 2020 Letter - 8368 VA BEACH	8368

[christine_assaad]

Hi @BonusCup 

I did it as described below

.*-.*?(\d+).*?

Hope this helps. Cheers!

[BonusCup]

@christine_assaad thanks so much for your help.  I added onto your regex with some 'or' statements. I found there were some missed due to an underscore instead of a hyphen and a space with neither a hyphen or an underscore.

.*-.*?(\d+).*?|.*_.*?(\d+).*?|.*\s.*?(\d+).*?

[BonusCup]

@christine_assaad I pulled in more data and found some that did not come across correct.  We are trying to get this field standardized but for now are having to deal with a field that is manually entered.  Thanks again for your help

source	desired	.*-.*?(\d+).*? OUTPUT
Test_LLC & Test 2020 Letter _ 80161790 Greenville	80161790	NULL
Test & Test 2020 Letter _ 450791 VA BEACH	450791	NULL
Test, Inc. Letter 2921504 AUGUSTA	2921504	NULL
Test Electric_ Inc 2020 Letter 80134459 Tampa	80134459	NULL
Test Group & Affiliates 2021 Test Letter _  80149235 ROCKVILLE	80149235	NULL
Test Test 2020 Letter_80111447 Raleigh	80111447	NULL
Test Test Ltd 2022 Test Letter – 80184089 AUGUSTA	80184089	NULL
Test Test Dtd 9-14-11 & Affiliates 2020 Test Letter _ 80115188 Raleigh	80115188	11
236-238 Test 52nd Street_ LLC & Affiliates 2020 Test Letter _  80126588 CORAL GABLES (4)	80126588	238
Test Test Tr DTD 12-15-1993 & Affiliates 2020 Test Letter _ 80166853 Austin	80166853	1993
Test Test Tr 12-15-1993 & Affiliates 2021 Test Letter _  80151116  ROCKVILLE	80151116	2015
1040 Test Test - Test Test (2018 Extension) & Test Name 80152406	80152406	2018
Test Group & Affiliates 2020 Test Letter _ 80165634 AUSTIN	80165634	NULL
Item TE-T Test Group 2020 Test Letter 80177931 AUGUSTA	80177931	2020

[KrishnaChithrathil · Accepted solution]

@BonusCup 

How does this look? 

the column desired is the one which you've provided.

[christine_assaad · Accepted solution]

 @BonusCup 

I'm not a Regex expert but this seems to work.

.*\W.*?([^\(\)_]\d+).*?$

Hope this helps :)

[BonusCup]

@KrishnaChithrathil , thank you.  This seems to work.  For my own knowledge, can you please describe what each section of the expression does?

.*[\s\_](\d+)

[BonusCup]

@christine_assaad this appears to also work.  Can you describe what each section of the expression is doing?

.*\W.*?([^\(\)_]\d+).*?$

[DavidSkaife]

Hey @BonusCup 

There is a Regex website that can help you with understanding it - https://regex101.com/ which has a handy explanation section:

[KrishnaChithrathil]

Hey @DavidSkaife. thanks for sharing this useful information. That link gonna go straight to my bookmarks. 😄

@BonusCup you can refer the explanation provided by @DavidSkaife.

Lemme also explain it from my end.

.* - some random things appearing in our string.

[\s\_] - this will check the presence of any whitespace or underscore before the digits we want.

(\d) - this will return a single digit like in the image below.(if we don't give \d+). That's why I gave (\d+)

You can also try with more other inputs and please modify the regex accordingly if it's not working.

*Please mark all the post you seemed working, as a solution. This will help others as well.