Text to numbers

B

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

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
 
S

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
 
R

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.
Click to expand...

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
 
G

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
 
R

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
 
Top