numbers need to be numbers

[Coco212]

I exported a spreadsheet from a website using their export link, using MS
2007, it has hundreds of cells with numbers, I need to make charts, but, even
though the characters look like numbers they are not recognized as numbers.



I tried selecting/changing format. I tried entering 1 as a number in a
different cell, made sure it is a number, copied it, and did
paste/special/multiply.



The only thing that works is to create a new spreadsheet and hand enter the
numbers, and there is not enough time.



Help....? THANKS!

Synthia

 

[T. Valko]

a spreadsheet from a website

You probably have unseen whitespace characters that are causing the problem.

I use the macro located at this site to clean data imported from websites:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

 

[tompl]

Try using the formula =Value(A1) to convert to number.

Tom

 

[Joe User]

I exported a spreadsheet from a website using their
export link [...]. [...] even though the characters look
like numbers they are not recognized as numbers.

I tried entering 1 as a number in a different cell, made
sure it is a number, copied it, and did paste/special/multiply.

See replies to your follow-up in the thread "Text to Number format".
Generally, it is not a good idea to bifurcate discussion of your particular
problem. Repating my reply in the other thread....

First, I wonder if you are truly using Excel. (Apparently Excel 2007,
according to your other posting.) When you exported, did you save the file
first, then open with Excel; or did you simply allow the online source to
open a window?

If the latter, I wonder if you have actually have a work-alike like Open
Office or Google Spreadsheet (which may actually be one in the same; I have
not used either). Click on Help > "About ..." link, and check the product
information (name and revision).

Second, if you are truly using Excel, you should not need to "erase and type
them again". It should be sufficient to press F2, then press Enter after
ensuring that the cell format is Number.

But of course, that might be tedious to do for "lots of cells". So try this
macro:

Option Explicit
Sub doit()
Dim c As Range
For Each c In Selection
c.NumberFormat = "General"
c = c.Value
Next c
End Sub

I selected them and changed the cell format to
number, which you would think would work, also,
but did not work.

That is not sufficient, at least not in Excel 2003, which I use. If you
simply change the format, you do need to cause the cell to be recalculated,
for example by pressing F2, then Enter.

However, the procedure of copy-and-paste-special-multiply should work even
without changing the cell format. At least, it does work in Excel 2003.


----- original message -----

 

[Gord Dibben]

See your other post............the one you tacked onto.


Gord Dibben MS Excel MVP