Extracting Specific Format number from String

Hi Everyone, I ran into a problem when cleaning up data from a PDF file.

Say if I have this string in a cell and I want the output to get the first part of the string and the big number with comma in the middle. How can I tell Alteryx to do that? Thanks!

Input:

"QUARTER WAGE RECAP: EMPLOYEES: TOTAL MALE FEM NEW SUI SUBJECT WAGES 1,999,999.70 MONTH 1 91 53 38 HIRE"

Output:

QUARTER WAGE RECAP:

1,999,999.70

Best Practices

Transformation

Preparation

Accepted answers

Thableaus

Hi @zoeuno

Not so fancy solution, but this one worked with your numbers.

I tested in some scenarios, but you should look at more variations of your data.

REGEX_Replace([Field1], "^(.*?:).*?((\d+,)+)(\d+)(\.\d+)?.*", "$1$2$4$5")

Cheers,

All comments

Thableaus

Hi @zoeuno

Not so fancy solution, but this one worked with your numbers.

I tested in some scenarios, but you should look at more variations of your data.

REGEX_Replace([Field1], "^(.*?:).*?((\d+,)+)(\d+)(\.\d+)?.*", "$1$2$4$5")

Cheers,

zoeuno

Hi! Just tried and it worked for that line. I do have another variation and it's

"EXCESS SUI WAGES 8,160,812.87 MONTH 2 96 56 40". In this case I want

EXCESS SUI WAGES

8,160,812.87

Also just curious, what does "$1$2$4$5" mean in the previous formula? Thanks again!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703