Text to Number format

[Janet]

I have copied a column of numbers to excel and now cannot apply any formulas
because the numbers are text. How can I convert the text to numbers?
Thanks.

 

[Gary''s Student]

In an un-used cell enter 1 (as a true number). Copy this cell and
paste/special/multiply onto the cells you would like to convert.

 

[Coco212]

perfect timing, I just exported a spreadsheet from an online source and am
trying to make a chart but it is not recognizing numbers as numbers. I
followed your steps but they still are not numbers unless I literally erase
them and type them in again, which I don't have time to do (lots of cells).
I selected them and changed the cell format to number, which you would think
would work, also, but did not work.
????
Thanks for any help you offer,
Synthia

 

[Gord Dibben]

After you changed the format to Number, did you follow the instructions
about copy/paste special/multiply?

Perhaps the cells have the dreaded non-breaking html spaces common with
online sources.

Select cells and Edit>Replace

What: Alt + 0160 on numpad

With: nothing

Replace all.


Gord Dibben MS Excel MVP

 

[Joe User]

I followed your steps but they still are not numbers
unless I literally erase them and type them in again,
which I don't have time to do (lots of cells).

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, GS's 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 -----

 

[Ashish Mathur]

Hi,

Try one of the following:

1. Find space and replace with blank; or
2. Select the dataset and go to Data > Text to columns > Finish