Extract numbers from a string if they match a given list

[bluelizard]

Hello,

Is there a way to pull values from a string if they match a list? I’m using regex_replace in the formula tool to extract these values, but the string isn't consistent (ie, preceding and proceeding text/characters may be different, per below example) and sometimes there's spacing/character issues so it doesn't always give me an output. so was wondering if I'd have a better chance if I provided the exact values, and have the workflow go through the whole list to find a match and extract it from the string. hope this makes sense. thank you in advance!

I'm on Alteryx Designer version 2024.1.1.93 if that helps.

sample list:

583920174
917463205
264851739

Sample Strings:

"ORIG: ACCOUNT NAME, INC. ID: 264851739BNF: RECIPIENT NAME LLC 123 SESAME ST ELMO PARK, NEW YORK 12345 US ID:583920174BNF BK: COOKIE MONSTER BANK"

"B/O BANK=/00917463205123 SESAME ST ELMO PARK, NEW YORK 12345 USCUSTOMER=/583920174 RECIPIENT NAME LLC 123 SESAME ST ELMO PARK, NEW YORK 12345 US BANK=/COOKIE MONSTER BANK"

[dreldrel]

You could use key identification terms to help extract the values (e.g. ID: or BANK=). The exact details will still rely on your actual data, but this is a good way to get started. Overall, I'd still suggest standardizing the pattern from the source if possible.

[jrlindem]

I agree with @dreldrel that you'll need some standardization in your strings.

In your first example, you have ":" as delimiters, which could help you parse by Text-To-Column and then Transpose/Cross-Tab'ing to grab the right values; but in the second example you don't have a way to differentiate between leading zero's or trailing address components.  So, even REGEX isn't going to help there without some level of consistency.

Do you have an opportunity to normalize upstream?  Happy to provide a workflow example if you can force a delimiter or separation from other numeric components to the string.

-Jay

[jrlindem]

@bluelizard 

Okay, so I experimented around a bit.  If you absolutely had to brute-force this and you could account for a reasonable small amount of variables...  Then you could do something like the attached.

Here's the workflow:

Here's the formula's.  They are broken out for illustrative purposes:

Once you have the values pulled from the strings, it's a simple JOIN to find the matches.

Not sure if this will totally cover your derivatives, but I think this might get you started!  Hope this helps,  -Jay

[danettedavis]

If you have a list of specific values that you are looking for you could use the find/replace tool to match off the string (your numbers would need to be converted to strings) but this should work to pull them out. 

[Qiu]

@bluelizard 
How about we first use Tokenize feature in RegEx tool to get all the Strings composited by Number then match with your sample list as below.