using regex to exclude specified text to extract certain numbers like left()/Right() functions in excel

user_8774395 0
Viewed 36

i have a list of transactions here:

Transaction REGEX(Amount)
Amount: 0.99 USD , 02:04:2021
Amount: 73 SAR , 02:04:2021
Amount: 133 SAR , 02:04:2021
Amount: 100 SAR , 02:04:2021
Amount: 101 SAR , 02:04:2021
Amount: 6 SAR , 02:04:2021
Amount: 104 SAR , 02:04:2021
Amount: 57 SAR , 02:04:2021
Amount: 61 SAR , 02:04:2021
Amount: 12 SAR , 02:04:2021
Amount: 12888.9 SAR , 02:04:2021
Amount: 144 SAR , 02:04:2021
Amount: 75 SAR , 02:04:2021
Amount: 904 SAR , 02:04:2021
Amount: 13.99 USD , 02:04:2021
Amount: 90 SAR , 02:04:2021
Amount: 10 USD , 02:04:2021
Amount: 106 SAR , 02:04:2021
Amount: 66 SAR , 02:04:2021
Amount: 508 SAR , 02:04:2021
Amount: 21.95 SAR , 02:04:2021

and I want to use regex to extract the amounts only without the Currency

so far I've used this "(\d+.\d+ (?:USD)|\d+.\d+ SAR|\d+ USD|\d+ SAR)"

and this gave me the amount with the currency.

I'm new at using Regex is there a way to have it exclude the currency?

regex
3 Answers

I found that this works (\d+.\d+[ USD]|\d+.\d+[ SAR]|\d+[ USD]|\d+[ SAR]) but im wondering if there is a simpler expression

user_87743950

You probably want (\d+(?:.\d+)?)\s(SAR|USD) if it's only to match one of those two... otherwise (\d+(?:.\d+)?)\s[A-Z]{3} might be good if you want to match any 3 uppercase-characters there.

This will extract digits and any decimals into the first group.

user_22664160

To get the currency only in capture group 1:

(\d+(\.\d+)?) (SAR|USD)
  • ( Capture group 1
    • \d+(\.\d+)? Match 1+ digit with an optional part (note to escape the dot \. to match it literally)
  • ) Close group 1
  • (SAR|USD) Match a space and either SAR or USD

Regex demo

If the amount is always preceded by a space, you can match it before group 1 (the square brackets are for clarity only)

[ ](\d+(\.\d+)?)\s(SAR|USD)

Regex demo

user_54249880
Related