converting text to number

Question

I have a spreadsheet (Excel2007) that has a column of numbers in it.  When it was exported from Access2007 it came over as text.  I redid the format to number but now I get the little notice button that has one menu item as convert to number.  I use this and it does become a number on on the right side of the cell.  The column has close to 30,000 rows to it.  I do not want to go each cell.  How can I do the whole column at once?

Thanks...John

Answer 1

Write a quick little macro to convert the data in the column from text to numbers.

<code>

Sub FixIt()

Dim i As Long

With Selection
    For i = 1 To Selection.Rows.Count
        Selection.Cells(i, 1).Value = CDbl(Selection.Cells(i, 1).Value)
    Next i
End With

End Sub
</code>

Select the column you want to fix then run FixIt

HTH,

Eric

Answer 2

Another way that doesn't use VBA is to enter 0 in some unused cell, copy that cell, select the range with the numbers as text and then do a PasteSpecial with the Addition operation. This will cause Excel to add 0 to all cells, and in the process, as a side effect, convert the text to real numeric data.

---

Chip Pearson, Excel MVP Pearson Software Consulting, LLC www.cpearson.com

Answer 3

Try this -

put 1 in a cell and Copy
select the range you want to convert to numbers
PasteSpecial, multiply

Peter Thornton

Answer 4

Thanks to all 3 of you for your suggestions.  I copied the spreadsheet and tried all 3 just to see how they worked.  I rarely work with spreadsheets and these 3 suggestions are going into my tips folder. 

Thanks...John