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

Get FREE Advanced Excel Exercises with Solutions!

Sometimes values look like numbers but don’t work like numbers as they can’t add up, multiply, etc. The numbers stored as the text will not work accordingly even if you change the format later. This tutorial will show 6 solutions to fix convert to number format error in Excel.


How to Fix Convert to Number Error in Excel: 6 Methods

To describe the following methods, we’ll use the following dataset that represents some bestseller books at amazon.com. Have a look at the triangle-shaped green sign at the top-left corner of every cell in the Published Year column which indicates the error that numbers are stored as text. Now look forward to the following methods to solve this error.


1. Use Convert to Number Command

In our very first method, we’ll learn the easiest way to fix it.

  • Select the cells C5:C9.
  • Then you will get an error sign, âš  as shown below in the image, click on it.
  • Later, select Convert to Number from the context menu.

Convert to Number Command

Soon after, you will see that the problem is fixed.

Convert to Number Command

Read more: Excel Convert to Number Entire Column


2. Apply Paste Special to Fix Convert to Number Error in Excel

The Paste Special command can handle the error smartly.

  • Select any blank cell and copy it.

Apply Paste Special to Fix Convert to Number Error in Excel

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

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

Then you will observe that the error signs are removed.

Read More: How Excel Formulas Convert Text to Number


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

If your values are along a column only then the Text to Columns Wizard can help you better. Let’s see how to apply it.

  • Select the cells C5:C9 from the column.
  • Then click as follows-

Data > Data Tools > Text to Columns.

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

  • After appearing the Text to Columns Wizard dialog box nothing to do in the first step, just press Next.

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

  • Same here in this step, nothing to do just press Next.

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

  • In the third step, mark General from the Column data format section.
  • Finally, just press Finish.

And now the error is successfully removed by the Text to Columns Wizard.

Read More: How to Convert Alphabet to Number in Excel


4. Use Excel VALUE Function to Fix Convert to Number Error

We can also fix the problem by using a function instead of a command in Excel. The VALUE function can help in this particular situation. To apply the VALUE function I have added a helper column right beside the number column. Now let’s try.

  • Activate Cell D5.
  • Type the following formula in it-
=VALUE(C5)
  • Then hit the Enter button to get the output.

Use Excel VALUE Function to Fix Convert to Number Error

  • Finally, drag down the Fill Handle icon to copy the formula for the other cells.

Use Excel VALUE Function to Fix Convert to Number Error

Now have a look that the VALUE function has removed the Convert to Number error completely.


5. Use Mathematical Operations in Excel to Fix Convert to Number Error

Some simple tricky mathematical operations can be used to fix the Convert to Number error. The trick is- just multiply or divide the values by 1. Also, you can add zero with the values and it will give you the same output. Here we’ll use the helper column too.

  • In Cell D5 write the following formula-
=C5*1

Or,

=C5/1

Or,

=C5+0
  • Then just press the Enter

Use Mathematical Operations in Excel to Fix Convert to Number Error

Finally, to get the other outputs, just drag down the Fill Handle icon over the cells D6:D9.

Use Mathematical Operations in Excel to Fix Convert to Number Error

Now the values are converted to numbers successfully.


6. Embed Excel VBA to Fix Convert to Number Error

In our last method, we’ll learn how to fix the Convert to Number error problem using VBA Macro.

  • First, select the cells C5:C9.
  • Then right-click on the sheet title.
  • Then select View Code from the context menu.

Soon after a VBA window will open up.

Embed Excel VBA to Fix Convert to Number Error

  • Write the following codes in it-
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

Finally, just click the Run icon in the VBA window to run the codes.

Embed Excel VBA to Fix Convert to Number Error

Then you will see that the above codes have removed the Convert to Number error.


Download Practice Workbook


Conclusion

I hope the procedures described above will be good enough to fix the Convert to Number error in Excel. Feel free to ask any question in the comment section and please give me feedback.


Further Readings


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo