How to convert text to numbers
 

Hello,

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

Sample spreadsheet is attached. I'd be very grateful to anyone who
tries to help :).

Thanks


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

--
pago
------------------------------------------------------------------------
pago's Profile: http://www.excelforum.com/member.php...o&userid=32990
View this thread: http://www.excelforum.com/showthread...hreadid=528189

How to convert text to numbers
 

=Value(CellAddress) will do it.
--
Kevin Backmann


"pago" wrote:


Hello,

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

Sample spreadsheet is attached. I'd be very grateful to anyone who
tries to help :).

Thanks


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

--
pago
------------------------------------------------------------------------
pago's Profile: http://www.excelforum.com/member.php...o&userid=32990
View this thread: http://www.excelforum.com/showthread...hreadid=528189

How to convert text to numbers
 

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


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"pago" wrote in message
...

Hello,

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

Sample spreadsheet is attached. I'd be very grateful to anyone who
tries to help :).

Thanks


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

--
pago
------------------------------------------------------------------------
pago's Profile:
http://www.excelforum.com/member.php...o&userid=32990
View this thread: http://www.excelforum.com/showthread...hreadid=528189

How to convert text to numbers
 

I have tried both =Value(CellAddress) and datatext 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?


--
pago
------------------------------------------------------------------------
pago's Profile: http://www.excelforum.com/member.php...o&userid=32990
View this thread: http://www.excelforum.com/showthread...hreadid=528189

How to convert text to numbers
 

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" wrote in message
...

I have tried both =Value(CellAddress) and datatext 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?


--
pago
------------------------------------------------------------------------
pago's Profile:
http://www.excelforum.com/member.php...o&userid=32990
View this thread: http://www.excelforum.com/showthread...hreadid=528189

How to convert text to numbers
 

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

Pascal


--
pago
------------------------------------------------------------------------
pago's Profile: http://www.excelforum.com/member.php...o&userid=32990
View this thread: http://www.excelforum.com/showthread...hreadid=528189