CODEWITHSUNDEEP
google-sheets
Richard de Ree
Richard de Ree

Reputation: 2540

Did Google change the function VALUE()?

"one thousand dollar and one cent" is notated in USA as 1000.01 and in the rest of the world 1000,01

To solve this problem I convert the entered amounts with the formula below

=VALUE(SUBSTITUTE(A1;".";",")

This formula converts both 1000,01 and 1000.01 into 1000,01

But the very same formula which worked well over all the years, gives a now 100001 as result

Am I the only one with this problem? Or did Google changed something?

example spreadsheet

https://docs.google.com/spreadsheets/d/1HfNEGVjzwf6Xil7vbv69F3ii6nryyDh8f6Z2ELqS5N8/edit?usp=sharing

UPDATE: It seems that it depend on the user. Some users get the right values while others get a 100 fold of those values in the same spreadsheet at the same moment.

See also:

https://webapps.stackexchange.com/questions/79051/how-to-convert-all-numbers-regardless-of-decimal-separator-into-valid-number-of

Upvotes: 0

Views: 512

Answers (2)

Wicket
Wicket

Reputation: 38296

The formula on the question requires that the value on A1 be a text. On the linked spreadsheet the above formula fails because the value of the referred cell is a number that use dot as thousand separator and comma as decimal separator.

The following should work both with numbers and text as input:

=IFERROR(VALUE(SUBSTITUTE(TEXT(A4;"0,00"); ","; ".")); VALUE(SUBSTITUTE(TEXT(A4;"0.00"; "."; ","))))

From answer to How to convert all numbers regardless of decimal separator into valid number of current locale?

The following formula does the job (tested with U.S. and Russia as locales, where separators are different):

=IFERROR(VALUE(SUBSTITUTE(A2, ",", ".")), VALUE(SUBSTITUTE(A2, ".", ",")))

Explanation:

  • IFERROR returns the first argument unless it throws an error, in which case it returns the second.
  • The first argument of IFERROR is the result of converting the text, with any commas replaced by dots, into a number.
  • The second argument of IFERROR is the result of converting the text, with any dots replaced by commas, into a number.

I'm assuming that in every locale, one of two things is going to work.

Upvotes: 1

player0
player0

Reputation: 1

the sheet you provided as an example is Netherlands locale where 1000,01 is a valid number (you can test it with =ISNUMBER(A2)) that's why =VALUE(SUBSTITUTE(A3; "."; ",") works.

fyi. - having =VALUE(SUBSTITUTE(A2; "."; ",") for 1000,01 is double overkill because 1000,01 is already numeric value and also there is nothing to be substituted

if you use a spreadsheet with US syntax than a valid number is 1000.01, therefore, substituting . with , will invalidate it - that's why you got #VALUE! (because logic says: to output value from invalid source - kinda same as =VALUE("red"))

localization settings can be changed here: 4

Upvotes: 1

Related Questions