Extract one number from large text column

[moemoney]

I have a column that have text and number. there multiple sets of number, I would like to get on set that is preceded by $,

Example:

JED/in amount of $12.20 charged to the account 769990000.

I only would like to extract the "12.20".

could you please help me?

[kat]

Hi @moemoney

This will require some regex to pull out the numbers. There are many ways to do this - I've attached an example.

Hope this works for you!

Kat

[ponraj]

Here is the sample workflow for your case. Hope this is helpful. 

Workflow

[MarqueeCrew]

In a formula tool:

Regex_replace([field],".*\$(\d+\.?\d{0,2})\s.*",'$1')

Will get you the amount.

Cheers,

Mark

[moemoney]

Thank you Mark,

it worked on most of the row, except on the ones with the thousand separator ",".

[danrh]

Minor change to @MarqueeCrew's solution to allow thousand separators:

Regex_replace([field],".*\$([\d,]+\.?\d{0,2})\s.*",'$1')

[MarqueeCrew]

@danrh,

Thanks for the regex update.  Here's another view:

Regex_replace([field],".*\$([\d\,\.]+)\s.*",'$1')

Cheers,

Mark

[moemoney]

when there is period at the end, it doesn't quite get the proper value, is there a way to ignore anything after the two decimal points. Please the original text on the right under comment:

Reject_Amount	Comment
7,166.00	%% ACH ECC PPD\nIn the amount $7,166.00 IRS\n
279.61	%% ACH ECC PPD\nIn the amount $279.61 COMCAST MARYLAND\n
106.10	%% ACH ECC WEB\nIn the amount $106.10 WASHINGTON GAS\n
In the amount $140.00.\n	In the amount $140.00.\n
100.00	%% ACH ECC WEB\nIn the amount $100.00 PAYPAL\n
373.98	%% ACH ECC WEB\nIn the amount $373.98 VERIZON\n
In the amount $3,818.53.\n	In the amount $3,818.53.\n
205.70	%% ACH ECC PPD\nIn the amount $205.70 GEICO\n
643.48	%% ACH ECC PPD\nIn the amount $643.48 WELLS FARGO BANK\n
58.78	%% ACH ECC PPD\nIn the amount $58.78 FGLIFEINSCO\n
In the amount $123.14.\n	In the amount $123.14.\n

[MarqueeCrew · Accepted solution]

@moemoney,

Regex_replace([field],".*\$([\d,]+\.?\d{0,2})[\s\.].*",'$1')

modified search for a space to include another decimal.

Cheers,

Mark

[moemoney · Accepted solution]

thank you Mark!!!! 

this worked perfectly!!