RegExp to parse currency value

Question

Hi,

I would need to write a RegExp in AS3 which parses an Excel formatted currency value into a number: E.g. RegExp($35,600.00) = 35600

And checks if it is correctly formatted (with the "," as the thousands separator, and the "." as the decimal point. The currency symbol could be any (not just $) and could stand at the beginning or the end.

So I would just need to strip every non digit from the number and check if is valid.

Thanks! Martin

Answer 1

You will need two cases, one for comma as separator and another for decimal separated whole numbers.

If a whole number, remove everything after the comma or decimal point(depending on your format). Then run the following Regex:

This will remove all non-digit characters:

s/\D+//g;

If you do not have a whole number, you will need to include an exception for the whole number separator:

Decimal separator:

 s/[^\d.]+//g

Comma separator:

 s/[^\d,]+//g

*Disclaimer: I only parsed these regexes in my head, so my syntax could be slightly off.

Answer 2

[$|£|<insert more pipe sepatared options here>]?(\d)*(,)?(\d)*.\d\d[$|£|<insert more pipe sepatared options here>]?

Might work.

Answer 3

After stripping of leading and trailing currency symbols and spaces, you can use the following expression.

[1-9][0-9]{1,2}(,[0-9]{3})*(.[0-9]{2})+

That is

(
    [1-9][0-9]{0,2}  One to three digits, no leading zero, followed by
    (,[0-9]{3})*     a thousands separator and three digits, zero or more times,
  |                  or
    0                a leading zero.
)
(.[0-9]{2})?         Optional a decimal point followed by exactly two digits.

Handling the currency symbol nicely is not the easiest thing because you have to avoid inputs with a leading and a trailing currency symbol. A solution would be using a look ahead assertion.

(?=$(([$€] ?)?[0-9,.]+|[0-9,.]+( ?[$€]))^)[$€ ]+<ExpressionFromAbove>[$€ ]+

This does the following.

(?=                   Positive look ahead assertion.
  $                   Anchor start of line.
  (                   Begin options.
    ([$€] ?)?         Optional leading currency symbol followed by an optional space
    [0-9,.]+          and one or more digits, thousand separators, and decimal points
  |                   or
    [0-9,.]+          one or more digits, thousand separators, and decimal points
    ( ?[$€])          followed by an optional space and and a currency symbol.
  )                   End options.
  ^                   Anchor end of line.
)
[$€ ]+                Leading currency symbol and optional space.
<ExpressionFromAbove> Match the actual number.
[$€ ]+                Trailing optional space and currency symbol.

If you know, that the format is correct, strip out everything that is not a digit or a decimal point and parse it into a decimal (in C# this would be using Decimal.Parse()), or, if there is no suitable parsing method, just split at the decimal point, parse into to integers, and combine both numbers.