How to extract currency ISO using RegEx?

Learning Hub
1

Hi all,

I try to extract the currency ISO (e.g. CAD in the example below) using RegEx.
I was thinking to use a positive lockbehind, but seems that I can’t use a wildcard inside of it.
How do you suggest to do it? It works in the case below if I try only \p{Lu}{3}
but it’s too generic.
Do you have a better idea?

image
image897×741 39.1 KB

here is text sample:
INVOICE
TO ACME Systems Inc.
RO012326
Somewhere Road, 59
Building A
Bucharest
Romania
Payment Terms are seven days from receipt of invoice with authorised timesheet
Vendor: Megatronic Incorporated
Address: Strada Sperantei la parter
City: Bucuresti,
Country: Romania
Invoice ID: 356147
Date: 2017-07-15
Item #
1
Item Description
IT Support
Unit Price
217496 CAD
Units
1
Total
217496 CAD
Subtotal: 217496 CAD
Tax: 43499.2 CAD
Total: 260995 CAD

2

Hello

I believe all currencies have three letters so you could then use the below regex options. Then get the third match/group.

image
image1242×799 170 KB

You could the following Regex:
(Total: )(\d* )(\D*)
(Total: )(\d* )(\D\D\D)

The first option will grab all letters.
The second option will grab three letters only

Then Use an ‘Assign’ Activity to get the 3rd match/group.
For example:
INSERTCURRENCYVARIABLE = INSERTREGEXOUTPUTVARIABLE(0).Groups(3).ToString

Just replace capitals with the output variable from the Matches activity.

1 Like
3

Hi Steven,
Thank’s for your reply. I need only CAD

4

So you need the just CAD or the amount in CAD?

For just ‘CAD’ use Match 3 and insert this Regex:
(Total: )(\d* )(CAD)

If you need the amount and currency, try Match 2 and the following Regex:
(Total: )(\d* CAD)

image
image1057×677 112 KB

I hope this helps :smiley:

1 Like
5

I need just CAD, but there are invoices in different currencies, so I can’t hardcode CAD

6

Okay, do you need the amount before CAD?

Regardless, I recommend this Regex:
(Total: )(\d* )(\D*)

And to get the amount after using the above regex you will need get match/group 2 separated, using an ‘assign’ activity:

INSERTCURRENCYVARIABLE = INSERTREGEXOUTPUTVARIABLE(0).Groups(2).ToString

Just replace capitals with the output variable from the Matches activity.

And to get the three letters (CAD, USD or XXY) for the currency you will need match 3 separated, using an ‘assign’ activity:

INSERTCURRENCYVARIABLE = INSERTREGEXOUTPUTVARIABLE(0).Groups(3).ToString

Just replace capitals with the output variable from the Matches activity.

It will work with all currencies (eg: CAD, USD, AUS).

If you just need CAD amounts you will need to add logic after the matches activity. Like, an ‘If’ activity, so if = CAD then do sequence otherwise do nothing.

3 Likes
7

Did I resolve this problem for you? If so, please mark as solution.

Thanks

8

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.