Parse digits from the right side of string
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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 |
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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+).*?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@KrishnaChithrathil , thank you. This seems to work. For my own knowledge, can you please describe what each section of the expression does?
.*[\s\_](\d+)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@christine_assaad this appears to also work. Can you describe what each section of the expression is doing?
.*\W.*?([^\(\)_]\d+).*?$
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hey @BonusCup
There is a Regex website that can help you with understanding it - https://regex101.com/ which has a handy explanation section:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
