Ask Your Question
0

problem with VALUE() in function chain [closed]

asked 2013-07-01 13:09:29 +0200

AndreasT gravatar image

updated 2013-07-01 13:12:37 +0200

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?

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2015-11-03 05:22:27.171295

Comments

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 ?

ROSt52 gravatar imageROSt52 ( 2013-07-01 13:26:46 +0200 )edit

It might as well be about the comma, which needs to be replaced with dot.

mahfiaz gravatar imagemahfiaz ( 2013-07-01 13:29:52 +0200 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2013-07-01 14:11:03 +0200

oweng gravatar image

updated 2013-07-02 13:04:13 +0200

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.

edit flag offensive delete link more

Comments

4.1 will have the NUMBERVALUE() function for this.

erAck gravatar imageerAck ( 2013-07-01 16:06:40 +0200 )edit

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)

AndreasT gravatar imageAndreasT ( 2013-07-01 16:36:35 +0200 )edit

@erAck, thanks for the clarification on that bug Eike. I will update my (wrong) answer to be clearer.

oweng gravatar imageoweng ( 2013-07-02 12:58:10 +0200 )edit

Question Tools

Stats

Asked: 2013-07-01 13:09:29 +0200

Seen: 182 times

Last updated: Jul 02 '13