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.

**Table of Contents**hide

**Download Practice Workbook**

You can download the free Excel template from here and practice on your own.

**6 Ways to Fix Convert to Number Error in Excel**

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

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

**Read more:** **How to Convert Date to Number in Excel**

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

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

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

Then you will observe that the error signs are removed.

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

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

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

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

**Similar Readings**

**Bulk Convert Text to Number in Excel (6 Ways)****How to Convert Percentage to Number in Excel (5 Easy Ways)****Convert Scientific Notation to Number in Excel (7 Methods)****How to Convert String to Number in Excel VBA (3 Methods)**

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

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

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

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

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.

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

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

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