# 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.

Read more: Excel Convert to Number Entire Column

### 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.
• 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``````
• Click the Run icon in the VBA window to run the codes.

• The values are converted to numbers.

<< Go Back to Convert Text to Number in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF