How to convert text to numbers

[pago]

Hello,

I have imported stock quotes into Excel using a web query (Data, Impor
External Data, New Web Query, etc ..). The field containing the stoc
quotes is output as text, and contains the currency symbol (se
attached - column D). In order to try and use this data in formulas,
have tried to convert it into numbers, by:
1. removing the currency symbol (Data, Text to Columns, ..) - se
column J in attached
2. convert the remaining data to number by using any of the method
proposed by Microsoft and others, all of which failed to work.

Sample spreadsheet is attached. I'd be very grateful to anyone wh
tries to help .

Thank

+-------------------------------------------------------------------
|Filename: Sample.zip
|Download: http://www.excelforum.com/attachment.php?postid=4551
+-------------------------------------------------------------------

 

[Guest]

=Value(CellAddress) will do it.

 

[Peo Sjoblom]

If there are no hidden spaces just select the column, do data>text to
columns and click finish


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

 

[pago]

I have tried both =Value(CellAddress) and data>text to
columns, but got no further (tried a sum and got 0). Of course, there
might be some spaces left, but I how could I tell?

 

[Peo Sjoblom]

Did you try to copy an empty cell, selecting the values and doing paste
special selecting add?
Otherwise you can try this

=--TRIM(SUBSTITUTE(A1,CHAR(160)," "))

copy down then paste special as values in place



--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

 

[pago]

Tried both, but still no joy. Have you tried it on the sample I
attached? I am probably doing something silly. Thanks

Pascal