How to extract specific amount from row with multiple number sets

PDempsey

Hello,

I need to extract the amount 32,765.00 from the string below.

Sometimes it will be preceded by EUR, other times USD etc.

I tried using Regex but it only extracted the "121212121" at the end of the text.

Appreciate any help, thanks in advance.

Convert Please Pay EUR 32,765.00 Inv No: DCB121212121

Common Use Cases

Accepted answers

All comments

binu_acs

@PDempsey One way of doing this

Parse_190623.yxmd

tristank

Hello,

This wasn't the simplest approach because my regex isn't great but if you're like me in that sense it's good to play around and look for easy alternatives. The workflow is attached below.

Have a good day!

Tristan

sampleregexflow.yxmd

tristank

This also worked if the strings before the currency stay the same: "Convert Please Pay \u\u\u (.*) Inv"

And the plus side it just requires one tool (still need to clean up white spaces tho)

PDempsey

Thanks all, however, it now seems I have to allow for other words appearing after the amount. Given the words before the 3 digit currency code and the amount will always be the same "Convert Please pay" (e.g. Convert Please pay USD 32,750) followed by the 3 character Currency code and the amount, can anyone suggest how to allow for different Currency codes appearing and just extract the amount on each row each time? Much appreciated.

binu_acs

@PDempsey If the "Convert Please pay" is constant you can try the below regex

Parse_190623.yxmd

danilang

Hi @PDempsey

In future, please try to include a sample input file that includes all variations of your data. This will allow us to build a generic solution without any unnecessary back and forth.

Thanks

Dan

PDempsey

Thanks, still struggling to break out the amount on every row. See attached file for a few variations. Appreciate if someone can help me to always pick up the amount after the 3 character currency code.

Input.xlsx

nagakavyasri

Try this

Screenshot 2023-06-20 134412.png

Parse.yxmd

binu_acs

@PDempsey Updated workflow attached

Parse_190623.yxmd

PDempsey

This really helped, thank you. However, it only picks up amounts with comma's in them, i.e. it does not pick up amounts less than 1,000 is there a way to also have it pick up amounts les sthan 1,000 please?

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335