Convert Numbers stored as Text to Numbers



I have imported a column with numbers, but the cells are formatted as text.
How do I convert them to Numbers?

Thanks in advance


Jacob Skaria

You can easily convert these cells to numeric format if you have enabled
error checking for these cells. Another work around is

--Copy a blank cell
--Keeping the copy select the range of cells with numeric values
--Right click>PasteSpecial>
--Select 'Add' and click OK.

Another way to convert the cells to numerics do the below.
In 2003 Tools>Options>Error checking>'Number stored as text'
In 2007 OfficeButton>ExcelOptions>Formulas>Error checking>

--If you have this option checked; then error checking is enabled for such
--For cells with numeric value but formatted as text; on the left top corner
of the cell you will see a green triangle.
--Select the range of cells and make sure one of the cells with the green
triangle is the active cell (cell with white background).
--Click/dropdown on the error information popup which is displayed towards
the left of the active cell
--Select 'Convert to number'


Don Guillett

I sometimes use this assigned to a custom button on the toolbar

Sub fixmynums()
Application.ScreenUpdating = False
On Error Resume Next
For Each c In Selection
If Trim(Len(c)) > 0 And c.HasFormula = False Then
c.NumberFormat = "General"
c.Value = CDbl(c)
End If

Application.ScreenUpdating = True
End Sub

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question