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
cells.
--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'
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
Next
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.