| View previous topic :: View next topic |
| Author |
Message |
carl
Super User
Joined: 21 Apr 2003
Posts: 920
Location: Germany
|
 Posted: Mon May 17, 2004 7:18 am Post subject: Convert a text string into a number |
 |
|
My text is in cell B4 and is -100,00 and I need to covert it to a value.
When I look in Help I find
| Quote: | VALUE
Converts a text string into a number.
Syntax
VALUE(text)
text is the text to be converted to a number.
Example
VALUE("4321") returns 4321. |
I have tried =value(B4) but get Err:502
What am I doing wrong?
PS I mean of course "convert" not covert! Who wants a negative number?
_________________
carl
Using OpenOffice.org 2 on XP sp2 |
|
| Back to top |
|
 |
SergeM
Super User
Joined: 09 Sep 2003
Posts: 3211
Location: Troyes France
|
|
| Back to top |
|
|
carl
Super User
Joined: 21 Apr 2003
Posts: 920
Location: Germany
|
| Posted: Tue May 18, 2004 2:22 am Post subject: |
|
|
Good thought but I use English inOOo.
I tried "wert(B4) and got #NAME?
_________________
carl
Using OpenOffice.org 2 on XP sp2 |
|
| Back to top |
|
|
JohnV
Administrator
Joined: 07 Mar 2003
Posts: 8980
Location: Lexinton, Kentucky, USA
|
| Posted: Tue May 18, 2004 4:03 am Post subject: |
|
|
| Value(B4) works for me where B4 is '-100. |
|
| Back to top |
|
|
carl
Super User
Joined: 21 Apr 2003
Posts: 920
Location: Germany
|
| Posted: Tue May 18, 2004 5:55 am Post subject: |
|
|
nope! I just get Err:502
OO01.1.1 andXP home
_________________
carl
Using OpenOffice.org 2 on XP sp2 |
|
| Back to top |
|
|
antonbijl
OOo Advocate
Joined: 04 Aug 2003
Posts: 291
Location: Pretoria, South Africa
|
| Posted: Wed May 19, 2004 2:00 am Post subject: |
|
|
Carl
I think this may be because the decimal character isn't recognized, try changing the text to '-100.00 (i.e. change the comma to a point)
I'm also on winXP OOo 1.1.1 and =VALUE("-100.00") yields the value -100 correctly while =VALUE("-100,00") yields Err 502. I hope this looks at the decimal character in your locale settings, though I haven't had a chance to test.
HTH
Anton |
|
| Back to top |
|
|
carl
Super User
Joined: 21 Apr 2003
Posts: 920
Location: Germany
|
| Posted: Wed May 19, 2004 10:53 pm Post subject: |
|
|
This is coppied from the spreadsheet | Quote: | ColA ColB
-150,00 € -150
-69,95 Err:502
|
Col B is the formula =VALUE(A1)
The first row I typed manually ,the second is from a csv
_________________
carl
Using OpenOffice.org 2 on XP sp2 |
|
| Back to top |
|
|
SergeM
Super User
Joined: 09 Sep 2003
Posts: 3211
Location: Troyes France
|
|
| Back to top |
|
|
David
Super User
Joined: 24 Oct 2003
Posts: 5668
Location: Canada
|
| Posted: Sun May 23, 2004 11:55 am Post subject: |
|
|
| sergeM wrote: | | I think like antonbijl that the decimal separator is the problem : in english version, the "." is the decimal separator instead of "," in german. |
That is definitely the problem as I see it. Try the same on a text entry like this:
'-100.00
Don't forget the initial single quote to assure text. If that works, then you see the difficulty. It works in an English spreadsheet, wher you might have had -100.00 as an entry in a text-formatted cell.
David. |
|
| Back to top |
|
|
carl
Super User
Joined: 21 Apr 2003
Posts: 920
Location: Germany
|
| Posted: Mon May 24, 2004 12:50 am Post subject: |
|
|
still no joy
I have long since just re typed the data as it wasnt too much
however
=value(cell ) only results in Err.502
it makes no difference whether the decimal separator is a . or a ,
Incidentally I hav my system set for English and comma, it works fine.
BTW when I open the csv with tab separator I overlooked the comma and found that the currency amounts opened so that the euros were numbers and the cents behind the comma were text!
_________________
carl
Using OpenOffice.org 2 on XP sp2 |
|
| Back to top |
|
|
David
Super User
Joined: 24 Oct 2003
Posts: 5668
Location: Canada
|
| Posted: Mon May 24, 2004 5:26 am Post subject: |
|
|
| carl wrote: | still no joy
I have long since just re typed the data as it wasnt too much
however
=value(cell ) only results in Err.502
it makes no difference whether the decimal separator is a . or a ,
Incidentally I hav my system set for English and comma, it works fine.
BTW when I open the csv with tab separator I overlooked the comma and found that the currency amounts opened so that the euros were numbers and the cents behind the comma were text! |
I think that the problem is that the "VALUE" function is a program snippet that looks for certain parameters and acts on them. In the English system, usinf the decimal, there is no problem recognising the text structure and translating it to number. In your system, it must recognise the comma and then translate that to a numerical value using the comma. It would appear to be an encoding [programming] problem that OOo needs to attend to ...but this is only a guess. What it boils down to at the moment is that it seems that you have no way to translate the comma text to a comma numerical delineator. This is why I thought you might enter a number such as -100.00, but that appears to be not possible. Sorry if I'm wasting your time here, and hope others have something more positive to offer. Perhaps one of the macro gurus can help.
David. |
|
| Back to top |
|
|
antonbijl
OOo Advocate
Joined: 04 Aug 2003
Posts: 291
Location: Pretoria, South Africa
|
| Posted: Mon May 24, 2004 9:27 am Post subject: |
|
|
Carl
This sounds interesting. A few questions:
1-
Just for clarity in the example you provided
Does that mean in the CSV file that value looks like this:
(assuming double quotes are used as string identifier)
2-
Do you get the same behaviour when the value is "-69.95" instead?
Now some further comments:
The fact that this behaviour is only witnessed on the import from CSV means that the cause is either (a) something wrong with the OOo CSV import filter OR (b) something wrong with the CSV file.
You mentioned | Quote: | | BTW when I open the csv with tab separator I overlooked the comma and found that the currency amounts opened so that the euros were numbers and the cents behind the comma were text! |
This makes me think the problem might be that there is an "invisible" character behind the cents and before the trailing comma-seperator, so I would suggest you check for that. A space is the most obvious one, for anything else you could open the CSV in writer and press Ctrl-F10 to view non-printing characters.
HTH
Anton |
|
| Back to top |
|
|
billbc
Power User
Joined: 15 Apr 2004
Posts: 79
Location: Germany
|
| Posted: Mon May 24, 2004 10:50 am Post subject: |
|
|
I am also using the english OO1.1.1 (W2k) and I tired both formatting the cell as text and then using "=VALUE" as well as the leading apostrophe to ensure text: in both cases the function produced the right result (i.e.(text) -100,00 became -100 (number). The formatting is confirmed by the new setting in "Format cells".
The commy is incidentally not an issue: I occasionally have problems whenI I " think Anglo-Saxon" and write a decimal point, but the Windows version at least seems to expect a comma for decimals. So I think you can safely class that angle as a dead-end or a red herring if you prefer.
This still doesn't explain the phenomenom on Carl's machine ...!!
Bill |
|
| Back to top |
|
|
carl
Super User
Joined: 21 Apr 2003
Posts: 920
Location: Germany
|
| Posted: Tue May 25, 2004 1:48 am Post subject: |
|
|
Setting comma as decimal separtor
29.95 =VALUE(C18) Err:502
'29.95 =VALUE(C19) Err:502
29,95 =VALUE(C20) 29,95
'29,95 =VALUE(C21) 29,95
I thought of "invisble characters" too but cant find any.I hunted a bit with Find & Replace but "Dere aint nuffink dere". I cant open a csv in writer but I saved it as txt and this is what I found.
'29.95' 'Err:502'
'29,95' 29,95
I entered the following as a spreadsheet
text with a ' 100.09 Err:502
text with a ' 100,10 100,1
number 100.11 Err:502
number 100,12 100,12
so it seems that my system wont recognize . only ,
_________________
carl
Using OpenOffice.org 2 on XP sp2 |
|
| Back to top |
|
|
antonbijl
OOo Advocate
Joined: 04 Aug 2003
Posts: 291
Location: Pretoria, South Africa
|
| Posted: Fri May 28, 2004 12:27 am Post subject: |
|
|
Carl
Send me the CSV file on yahoo (antonbijl AT yahoo.co.uk) and I'll have a look for you. Please post back here as soon as you've sent it, since I don't check that mailbox very regularly.
Cheers
Anton |
|
| Back to top |
|
|
|
