H2: "0,1290 € "
I want to convert this into a number.
In German:
=WERT(GLÄTTEN(WECHSELN(H2;"€";"")))
Translated:
=VALUE(TRIM(SUBSTITUTE(H2;"€";""))
The conversion into a number fails with
Err:502
Can anyone tell me why this may happen?
What format has cell H2?
Is the EUR sign in H2 created by formatting the H2 as a currency with EUR as the unit? If so the reason for Err: 502 could be caused by the missing “EUR” sign in the H2.
Could please provide a bit more information ?
It might as well be about the comma, which needs to be replaced with dot.
Bug fdo#53173 describes a problem with the VALUE() function not honoring the locale i.e., it is not handling a comma being used in place of a full stop to indicate a decimal. I have just conducted a brief test here using v4.0.3.3 from the website and it still seems to be an issue. I am not sure about v4.1.
EDIT: This has been fixed for v4.1, with a new function (NUMBERVALUE) provided to handle the locale. Thanks to the developers for the update.
4.1 will have the NUMBERVALUE() function for this.
I suspected something like this as well, but somehow it handled this correctly. This is LibreOffice 3.5.4.2
Build-ID: 350m1(Build:2)
@erAck, thanks for the clarification on that bug Eike. I will update my (wrong) answer to be clearer.