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 sourcedesired2019 Test & Affiliates 2020 Letter - 12345678 GREENVILLE123456782020 Test - TEST & test 123456123456Test Partners LP & Affiliates 2020 Letter - 87654321 Greenville876543212019Test - TEST & test 654321654321TEST & Affiliates 2020 Letter - 23456789 Raleigh23456789Test_ Inc. 2020 Letter - 8327 VA BEACH83272021TEST - Test Partners & Affiliates 80208020Test test 2020 Letter - 8368 VA BEACH8368

Parse digits from the right side of string

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

Developer

Regex

Parse

Accepted answers

KrishnaChithrathil

@BonusCup

How does this look?

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

Parse digits from the right side of string.yxmd

Christine_Assaad

@BonusCup

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

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

Hope this helps

All comments

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

@BonusCup

How does this look?

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

Parse digits from the right side of string.yxmd

Christine_Assaad

@BonusCup

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

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

Hope this helps

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.

atcodedog05

Hi @davidskaife

I thought www.regex101.com had only light theme mode. I didn't know it had dark mode too in settings 😎. I guess dark-mode people will always find ways to change it to dark mode 😁

Thanks : )

Cheers and have a nice day!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703