Text to numbers

[Bob]

For years and years, I have been happily using Excel 97
to copy data from a website to my Excel spreadsheet by
saving it as an Excel Workbook.

I then manipulate the data (multiply, divide etc) without
a problem.

I have just installed Office 2000 Small Business, and
when I try the same copy procedure, all the calculations
return #VALUE. It appears that the "imported" data is
text (?), with spaces. Not sure though.

I realise I haven't explained this too well, but I'm
hoping someone can interpret what I am trying to say, and
assist.

 

[G Patterson]

Try selecting the columns with the data in them and
converting them explicitly to number format using the
Format|Cells menu. From the cells window select the
number tab and 'number' from the category list.

GP

 

[Stewart]

Yeah, I get the same thing with pasting stuff in from
Access. I've got a little bit of macro code that I have
put on a custom toolbar to sort this out:-

Sub cleanup()
ActiveWindow.RangeSelection.Cells.Value = _
ActiveWindow.RangeSelection.Cells.Value
End Sub

Highlight the affected range of cells then run this macro
it will solve your problem (I think)

Hope this helps

Stewart Walker
MOS Excel Expert

 

[Guest]

Thanks GP,

I have tried that without success.

I'm jinxed.

Bobh

 

[Ron Rosenfeld]

For years and years, I have been happily using Excel 97
to copy data from a website to my Excel spreadsheet by
saving it as an Excel Workbook.

I then manipulate the data (multiply, divide etc) without
a problem.

I have just installed Office 2000 Small Business, and
when I try the same copy procedure, all the calculations
return #VALUE. It appears that the "imported" data is
text (?), with spaces. Not sure though.

I realise I haven't explained this too well, but I'm
hoping someone can interpret what I am trying to say, and
assist.

Depending on exactly what is going on, there are several options.

If you cannot multiply without an error (e.g. =1*A1) then:

=TRIM(A1) might work.

But sometimes when importing from the web, a CHAR(160) gets added.

So: =SUBSTITUTE(A1,CHAR(160),"")


--ron

 

[Guest]

Thanks Stewart,

I am ashamed to say that I don't know how to write a
macro!!

I know how to record one, but not write one.

Your solution sounds very promising, but until I can
write it, I won't be able to find out.

If it's easy to do, maybe you can help, but if it's a bit
complicated for a novice like me, I will get my IT guys
at work tomorrow to coach me.

I really appreciate your contribution.

Bob H

 

[RagDyer]

Have you tried this?

Select a "new", unused cell that has the default XL format "General", and
right click in this cell and choose "Copy".
Select the "bad" cells in question.
Right click in this selection and choose "PasteSpecial".
Click on "Add", then <OK>.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Thanks Stewart,

I am ashamed to say that I don't know how to write a
macro!!

I know how to record one, but not write one.

Your solution sounds very promising, but until I can
write it, I won't be able to find out.

If it's easy to do, maybe you can help, but if it's a bit
complicated for a novice like me, I will get my IT guys
at work tomorrow to coach me.

I really appreciate your contribution.

Bob H

 

[R.VENKATARAMAN]

trythis.
in some blank cell e.g. e1 type <1>. click cell E1 click editi-copy. goto
the
cell of the number in text click edit-pastespecial-multiply (in the
operations in the middle of pastespeial window) -ok.. The text will be
converted into numbers.