Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Parse digits from the right side of string

BonusCup
10 - Fireball

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

 

 

sourcedesired
2019 Test & Affiliates 2020 Letter -  12345678 GREENVILLE12345678
2020 Test - TEST & test 123456123456
Test Partners LP & Affiliates 2020 Letter - 87654321 Greenville87654321
2019Test - TEST & test 654321654321
TEST & Affiliates 2020 Letter -   23456789 Raleigh23456789
Test_ Inc. 2020 Letter - 8327 VA BEACH8327
2021TEST - Test Partners & Affiliates 80208020
Test test 2020 Letter - 8368 VA BEACH8368
11 REPLIES 11

Hi @BonusCup 

 

I did it as described below

 

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

 

 

christine_assaad_0-1668458838680.png

Hope this helps. Cheers!

 

 

BonusCup
10 - Fireball

@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
10 - Fireball

@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

 

sourcedesired.*-.*?(\d+).*? OUTPUT
Test_LLC & Test 2020 Letter _ 80161790 Greenville80161790NULL
Test & Test 2020 Letter _ 450791 VA BEACH450791NULL
Test, Inc. Letter 2921504 AUGUSTA2921504NULL
Test Electric_ Inc 2020 Letter 80134459 Tampa80134459NULL
Test Group & Affiliates 2021 Test Letter _  80149235 ROCKVILLE80149235NULL
Test Test 2020 Letter_80111447 Raleigh80111447NULL
Test Test Ltd 2022 Test Letter – 80184089 AUGUSTA80184089NULL
Test Test Dtd 9-14-11 & Affiliates 2020 Test Letter _ 80115188 Raleigh8011518811
236-238 Test 52nd Street_ LLC & Affiliates 2020 Test Letter _  80126588 CORAL GABLES (4)80126588238
Test Test Tr DTD 12-15-1993 & Affiliates 2020 Test Letter _ 80166853 Austin801668531993
Test Test Tr 12-15-1993 & Affiliates 2021 Test Letter _  80151116  ROCKVILLE801511162015
1040 Test Test - Test Test (2018 Extension) & Test Name 80152406801524062018
Test Group & Affiliates 2020 Test Letter _ 80165634 AUSTIN80165634NULL
Item TE-T Test Group 2020 Test Letter 80177931 AUGUSTA801779312020
KrishnaChithrathil
11 - Bolide

@BonusCup 

How does this look? 

KrishnaChithrathil_0-1668527604777.png

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

 @BonusCup 

 

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

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

 

christine_assaad_0-1668530241920.png

 

Hope this helps :)

BonusCup
10 - Fireball

@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
10 - Fireball

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

 

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

DavidSkaife
13 - Pulsar

Hey @BonusCup 

 

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

 

DavidSkaife_3-1668548221406.png

 

DavidSkaife_2-1668548184965.png

 

KrishnaChithrathil
11 - Bolide

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+)

 

KrishnaChithrathil_0-1668573781087.png

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.

 

 

 

 

Labels