How to Fix Convert to Number Error in Excel (6 Methods)

The sample dataset represents some bestseller books at amazon.com. The triangle-shaped green sign at the top-left corner of every cell in the Published Year column indicates the error that numbers are stored as text.

Method 1 – Use Convert to Number Command

Select the cells C5:C9.
Click on the error message.
Select Convert to Number from the context menu.

Method 2 – Apply Paste Special to Fix Convert to Number Error in Excel

Select any blank cell and copy it.

Select the number containing cells.
Right-click your mouse.
Select Paste Special from the context menu.

A dialog box will open up.

Mark Values from the Paste section and Add from the Operation section.

Method 3 – Text to Columns Wizard to Fix Convert to Number Error in Excel

Select the cells C5:C9 from the column.
Click Data > Data Tools > Text to Columns.

Press Next in the Text to Columns Wizard dialog box.

Press Next again.

In the third step, select General from the Column data format section.
Click Finish.

Method 4 – Use Excel VALUE Function to Fix Convert to Number Error

Add a helper column beside the number column.
Select Cell D5.
Enter the following formula.

=VALUE(C5)

Hit the Enter button to get the output.

Drag the Fill Handle icon to copy the formula for the other cells.

Method 5 – Use Mathematical Operations in Excel to Fix Convert to Number Error

In Cell D5 enter the following formula.

=C5*1

Or,

=C5/1

Or,

=C5+0

Hit Enter.

Drag the Fill Handle icon to copy the formula for the other cells.

The values are converted to numbers.

Method 6 – Embed Excel VBA to Fix Convert to Number Error

Select the cells C5:C9.
Right-click on the sheet title.
Select View Code from the context menu.
The VBA window will open up.

Enter the following code.

Sub Fix_ConvertToNumber()
Dim y As Range
On Error Resume Next
Set y = Selection _
  .SpecialCells(xlCellTypeConstants, 23)
On Error GoTo errHandler
If Not y Is Nothing Then
  Cells.SpecialCells(xlCellTypeLastCell) _
      .Offset(0, 1).Copy
   y.PasteSpecial Paste:=xlPasteValues, _
       Operation:=xlPasteSpecialOperationAdd
Else
  MsgBox "Could not find any Constant"
End If
exitHandler:
  Application.CutCopyMode = False
  Set y = Nothing
  Exit Sub
errHandler:
  MsgBox "Failed to change text to numbers"
  Resume exitHandler
End Sub