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.

Convert to Number Command

 

Convert to Number Command

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.

Apply Paste Special to Fix Convert to Number Error in Excel

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

A dialog box will open up.

Apply Paste Special to Fix Convert to Number Error in Excel

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

 

Read More: How Excel Formulas Convert Text to Number


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.

Text to Columns Wizard to Fix Convert to Number Error in Excel

  • Press Next in the Text to Columns Wizard dialog box.

Text to Columns Wizard to Fix Convert to Number Error in Excel

  • Press Next again.

Text to Columns Wizard to Fix Convert to Number Error in Excel

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

 

Read More: How to Convert Alphabet to Number in Excel


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.

Use Excel VALUE Function to Fix Convert to Number Error

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

Use Excel VALUE Function to Fix Convert to Number Error

 


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.

Use Mathematical Operations in Excel to Fix Convert to Number Error

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

Use Mathematical Operations in Excel to Fix Convert to Number Error

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

Embed Excel VBA to Fix Convert to Number Error

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

Embed Excel VBA to Fix Convert to Number Error

  • The values are converted to numbers.


Download Practice Workbook

 


Further Readings


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

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

2 Comments
  1. very helpful solutions!

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Jun 10, 2024 at 4:37 PM

      Dear Naqavi

      Thanks for your compliment! Your appreciation means a lot to us.

      Regards
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo