Description
Converts currency-formatted text to a double-precision number. Use it instead of chainingREPLACE calls and a (double) cast to clean up amounts from spreadsheets, accounting exports, and billing systems.
The function:
- Reads the decimal separator from the optional second argument: a period (
'.', the default, as in1,234.56) or a comma (',', as in1 234,56or1.234,56, used across most of Europe and Latin America). - Accepts thousands separators that separate groups of three digits: commas, periods (when the decimal separator is a comma), spaces including non-breaking spaces, or apostrophes, as in
1'234.56. A value uses one kind. With the default'.'separator, Indian grouping, such as1,23,456.78, also works. - Ignores one currency marker before or after the number. The accepted markers are listed in the table below.
- Treats these as negative: parentheses (
(12.50),($12.50)or$ (12.50)), a leading minus (-$12or$-12), or a trailing minus (12-). The minus can also be the Unicode minus sign−. Only one negative marker is allowed. A dash right after the decimal separator, as in199,- Kč(with',') orCHF 20.-, marks a whole amount, not a negative. - Returns numeric input (integer, long, float, double or decimal) unchanged as a double.
',' argument, '12,50' and '1 234,56' return null, and with it, '1,234.56' returns null. A value that fits both readings follows the separator you pass: without ',', '6,499' returns 6499.0 and '1.500' returns 1.5. So pass ',' whenever the data uses comma decimals.
Currency markers
The former ISO codes it accepts are ADP, AFA, ANG, ATS, AYM, AZM, BEF, BGL, BGN, BYB, BYR, CSD, CUC, CYP, DEM, EEK, ESP, FIM, FRF, GHC, GRD, GWP, HRK, IEP, ITL, LTL, LUF, LVL, MGF, MRO, MTL, MZM, NLG, PTE, ROL, RUR, SDD, SIT, SKK, SLL, SRG, STD, TMM, TPE, TRL, USS, VEB, VEF, XFO, XFU, YUM, ZMK, ZWD, ZWL, ZWN and ZWR. Other former codes, such as
CSK, return null.
Other letters next to a symbol, such as CAN$ or a magnitude in 150 k€, return null.
Syntax
ParseMoney(string_expression[, decimal_separator])
Arguments
Examples
Return value datatype
DoubleImpact of null value
If input is null, returns null, and so does text with a null or unrecognizeddecimal_separator. Returns null, without failing the job, for empty text and for any value that can’t be parsed as an amount, including:
NaNandInfinity- exponents, such as
'1e3' - type suffixes, such as
'12d' - more than one decimal separator, such as
'1.2.3' - thousands separators that don’t separate groups of three digits, such as
'12,50'without',', or'10 20' - a symbol or sign with no digits, such as
'()'or'$ -' - letters next to a symbol that are neither a listed prefix nor an ISO code in capitals directly before it, such as
'CAN$ 12', or a magnitude, such as'150 k€'or'1,2 Mio €' - an ISO code that isn’t in capitals directly before the symbol, such as
'usd $12','$12 USD'or'$USD 12'
FAQ
Why does ParseMoney return null for ‘12,50’? With the default'.' separator, a comma only separates thousands, so '12,50' fits neither reading and returns null. Pass the comma as the separator: ParseMoney('12,50', ',') returns 12.5.
How do I get 0 instead of null?
Wrap the call in COALESCE: COALESCE(ParseMoney(amount), 0.0). A number as the second argument is not a default. ParseMoney(amount, 0.0) fails validation.
Can each row use a different separator?
Yes. The second argument can be a field or an expression, such as ParseMoney(amount, separator_field). A row whose separator isn’t '.' or ',' returns null; it doesn’t fail the job.
Does ParseMoney convert between currencies?
No. It drops the currency marker and returns the amount as written, so '€10' and '$10' both return 10.0. If your data mixes currencies, keep the currency in a separate field.
Related functions
- COALESCE: replace null with a default value.
- ParseDoubleOrDefault: parse plain numeric text, with a default when parsing fails.
- REPLACE: regular-expression replacement, for cleanup that ParseMoney doesn’t cover.