Find more than one number in a string with RegEx

Question

I am trying to get a list of 10 digit numbers from a comment field.  My challenge is that there could be 0-3 instances in the comment box and I need all of them.

I tried RegEx with the following but it only gave me the ones that had exactly three 9 digit numbers and skipped the others

.*(\d{9}).*(\d{9}).*(\d{9}).*

The first two columns are my input and the last 3 would be my desired output.

AccountCommentLinked account #1Linked account #2Linked account #3661648894No other accounts   173190475linked to account 309448085309448085  685015727Other Accounts include 787170533, 473792300, &132115945787170533467887389132115945312170997No other accounts   467887389Other Accounts include 297712578-1 and 286230987-1 and 553231083-1297712578286230987553231083765610022Account at address 123 Main, Springfield, CO, 12345-6789   361338019640092055770291080676829215640092055770291080676829215924011524Linked to account 30345 23 345   848188639Two Additional Accounts are 963570212279321937.  None else noted963570212279321937

Is this something that can be done with a single tool or will I need to pull out the first instance and then run the remaining through to get the next instance and so on?

I appreciate any help I can get!

Steve

Thableaus · Accepted Answer

Hi @stephenslaughter

If you use Tokenize method with the Regex Tool, you might have some luck

Look at the image below.

Cheers,

Jonathan-Sherman · Accepted Answer

Hi @stephenslaughter,

I would use a tokenise to rows in conjunction with a crosstab to achieve the desired result and keep it fully dynamic (if the number of account numbers in the string ever changed)

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan