Numbers "VALUE" function not working correctly

Question

Jhornby Author

Level 1

10 points

Numbers "VALUE" function not working correctly

According to the function reference, if you create the following formula:

=VALUE("$100.50") it returns the number 100.5.

Not on my Mac, it returns an error "VALUE requires a string specifying a number, but found "$100.50" instead.

I've tried it on numbers for iPhone and get the same issue :(

I'm using version 13.2 (7038.0.87) on a MBP running Ventura 13.6

Ironically, if you swap out the $ with £ ... it works.

If I change the Language & Region settings on my Mac from Region = United Kingdom TO United States it correctly identifies $100.50 as 100.50 AND £100.50 as 100.50

In short it's a bug, the function should work the same regardless of your Region settings. Not sure how you report a bug to the development team. Any ideas?

MacBook Pro 15″, macOS 10.13

Posted on Oct 10, 2023 11:57 AM

Reply

Answer 1

Recycleur

Level 4

1,741 points

Oct 10, 2023 1:51 PM in response to Jhornby

Feedback to Apple: Product Feedback - Apple

Indeed the function does not work identically with all region settings.

English - Canada

French - Canada (with commas)

English - UK

English - USA

Reply

Answer 2

Jhornby Author

Level 1

10 points

Oct 11, 2023 6:39 AM in response to SGIII

Good tip SGIII. Hadn't realised there was a feedback link in the first menu.

I ended up leaving the VALUE statement in place with a SUBSTITUTE to replace $ with "".

If/ when Apple fix the bug, I can simply remove the SUBSTITUTE from within ... I'm referencing other cells, but here's what it looks like spelled out ... =VALUE(SUBSTITUTE("$100.50"),"$","")

Cheers

Reply

Answer 3

Jhornby Author

Level 1

10 points

Oct 11, 2023 1:34 AM in response to Recycleur

Thanks Recycleur. Bookmarked the product feedback link and reported as a bug.

In the meantime, I'll use other Text functions to strip out the $ and any commas before converting to a number. Not as elegant/ efficient, but a work around for now. Cheers.

Reply

Answer 4

Jun 30, 2024 8:41 AM in response to Recycleur

There is another solution. Use concatenate to place US before the $ sign and it return the correct result.

=VALUE(CONCATENATE("US","$100.50"))

Reply

Answer 5

SGIII

Level 8

37,930 points

Oct 11, 2023 4:51 AM in response to Jhornby

Jhornby wrote:

Thanks Recycleur. Bookmarked the product feedback link and reported as a bug.

In the meantime, I'll use other Text functions to strip out the $ and any commas before converting to a number. Not as elegant/ efficient, but a work around for now. Cheers.

If you are on the Mac then there is no need to bookmark. Just go to Numbers > Provide Numbers Feedback in the menu.

Keep in mind that REGEX.EXTRACT can be useful for extracting the numerical value, something like this:

In the column C I simply multiplied the value in B by 1 to coerce it to a number.

That coercion works for the first string when the region of the document is set via File > Advanced > Language and Region... to a region that uses , as the thousands separator and . as the decimal separator.

That coercion works for the second string when the region of the document is set via File > Advanced > Language and Region... to a region that uses , as the thousands separator and . as the decimal separator.

Explanation of this REGEX:

(\d{1,3}(?:[.,]\d{3})*(?:[.,]\d+)?)

The outer ( ) create a capture group (optional here in REGEX.EXTRACT

\d{1,3} matches 1 to three digits

(?:[.,]\d{3})* matches thousands separators (either , or .) followed by three digits. The * allows for more than one of these.

(?:[.,]\d+)? matches a decimal separator (either , or .) followed by one or more digits. The ? makes that optional (in case there are no decimal digits.

Not as easy as VALUE but very flexible and powerful.

SG

Reply

Numbers "VALUE" function not working correctly

Similar questions