Numbers "VALUE" function not working correctly with non-local currency string $, £, € etc.

Question

Level 1

4 points

Numbers "VALUE" function not working correctly with non-local currency string $, £, € etc.

Value("$4,567.89") and Value("4,567.89") give an error VALUE requires a string specifying a number, but found “$4,567.89” instead.

In File > Advanced > Language & Region I see I have my currency set to €4 567.89 so NO $ sign and NO , thousands separator.

VALUE only recognises your local/system currency format.

To turn a non-local currency format into a number so I can do calculations I did this:

=VALUE(SUBSTITUTE(REPLACE(B2,1,1,""),",","")) where cell B2 contains $4,567.89 for example.

This REPLACEs the first character ($) with "" and then SUBSTITUTEs all "," characters for " " (space) to match my currency settings. VALUE then correctly converts "4 567.89" to a numerical value so it can be used numerically.

iMac 27″

Posted on Jan 24, 2025 8:02 AM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

30,366 points

Jan 27, 2025 7:59 PM in response to pogle44

I believe it is working correctly. If the regional setting is €4 567.89 then 4,567.89 is not a number because "," is not an acceptable character in a number in that region. $4 567.89 is likely also not a number because $ is not the currency symbol Numbers uses for US dollar in that region, it is probably US$ (with the US capitalized).

You can try this:

=VALUE(SUBSTITUTE(B2,REGEX("[^0-9.]"),""))

This removes all characters (including currency symbols) that are not 0-9 or a dot, then gets the value.

Reply

Answer 2

Badunit

Level 7

30,366 points

Jan 27, 2025 8:12 PM in response to Badunit

One thing to be aware of is that SUBSTITUTE uses the text as it is displayed in the cell. If the number in B2 is an actual number (like 4 567.89 not a malformed "number" like 4,567.89) and cell B2 is formatted with fewer decimal places than the number actually has, the result of this formula will be rounded to what is displayed. This is not a problem for malformed numbers because they are text and the cell cannot be formatted to show fewer decimals than the "number" has.

Example:

4 567.89 is in cell B2 formatted with one decimal place so you see it in the cell as 4 567.9

The formula result will be exactly 4567.9 not 4567.89

Reply