### Fix 1 – Fix Formula Result Showing 0 by Converting Text to Number

You’ll need to check the data in the dataset that is being used in the formulas. Sometimes, the numbers are stored in text format and the formula returns 0 instead of the actual result.

#### Case 1.1 – Convert Text to Number with Mouse Click

In the following screenshot, we have a dataset of a bookstore. The dataset consists of the names of some books and the available quantity of those books in that bookstore. Suppose we want to calculate the number of total books in cell **C10**.

**Setup:**

- Input the formula in cell
**C10**to calculate the total quantity:

`=SUM(C5:C9)`

- Press
**Enter**and we can see that the formula returns 0. This is the problem that we need to fix.

- In cell
**F6**insert the following formula:

`=COUNTA(C5:C9)`

- If you press
**Enter**after inserting the formula, you get the result because the**COUNTA**function counts the number of non-blank cells. It doesn’t look at the cell values.

- Insert the following formula in cell
**F7**:

`=COUNT(C5:C9)`

- Press
**Enter**. Here the formula returns**0**because the**COUNT**function counts the number of cells that contain numbers, not text.

- We can see the numbers in the cell but there is an invisible
**apostrophe**that indicates the number is in text format, which can only be seen in the formula bar. That is why we get an error while using formulas.

**Steps:**

- Select the cells (
**C5:C9**).

- Click on the
**exclamation.**Â A drop-down menu will appear. - Select the option
**Convert to Number**Â from the drop-down menu.

- We can see that the formula result no longer shows a zero value in cells
**C10**and**F7**.

#### Case 1.2 – Use the Paste Special Option to Convert Text to Numbers

To illustrate this method, we will use the same dataset that we used in the previous method.

**Steps:**

- Select any cell outside the data range and click on
**Copy**. - Select the cell range (
**C5:C9**). - Go to
**Home,**selectÂ**Paste,**and choose**Paste Special**

- This opens a new dialogue box named
**Paste Special**. - Check the option
**Add**under the**Operation**section and click on**OK**.

- The expected output of the formula is now in cell
**C10**.

#### Case 1.3 – Apply VBA Code to Convert Text to Number to fix formula result showing 0 in Excel

We’ll use the same dataset to show VBA code use.

**Steps:**

- Select cells
**C5:C9**. **Right-click**on the active sheet.- Select the option
**View Code**.

- This will open a blank
**VBAÂ**module. - Insert the following code in that module:

```
Sub TextToNumber()
Dim rg As Range
On Error Resume Next
Set rg = Selection _
.SpecialCells(xlCellTypeConstants, 23)
On Error GoTo ErrorHandle
If Not rg Is Nothing Then
Cells.SpecialCells(xlCellTypeLastCell) _
.Offset(0, 1).Copy
rg.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlPasteSpecialOperationAdd
Else
MsgBox "Unable to find Constants in selection"
End If
Handler_Exit:
Application.CutCopyMode = False
Set rg = Nothing
Exit Sub
ErrorHandle:
MsgBox "Unable to convert text to numbers"
Resume Handler_Exit
End Sub
```

- Click on the
**Run**button or press the**F5**key to run the code.

- The code converts the text values into numbers, fixing the issue.

### Fix 2 – Fix Formula Result Is Showing 0 in Excel Using the Text to Column Option

We will use the same dataset as before.

**Steps:**

- Select the cells
**C5:C9**. - Go to the
**Data tab.** - Select
**Text to Columns**from the**Data Tools**Â section.

- A new dialogue box will appear. Check the option
**Delimited**from the file type options and click on the**FinishÂ**button.

- The above action returns the result of the formula in cell
**C10**.

### Fix 3 – Remove Hidden Characters When Formula Result Showing 0 in Excel

Another reason for the formula result showing **0 **in Excel is the presence of hidden characters in the formula range.

#### Case 3.1 – Fix Formula Result Showing 0 By Removing Hidden Characters Using Character Codes

In **Microsoft Excel**, the character code for a non-breaking space is **0160**. We have to replace those characters with blank or empty strings. The following dataset of a bookstore consists of the names of different books and their all-time sales quantity from that store.

**Steps:**

- Select the cells
**C5:C8**. - Go to the
**HomeÂ**tab.

- Go to the
**Find & Select**option. - Select the option
**Replace**.

- A new dialogue box named
**Find and Replace**Â will appear. - Go to the
**Find what**Â text field. Hold the**Alt**key and type**0160**on the**number keypad (**not from the**alphanumeric keys)**. You won’t be able to see the character. - Keep the
**Replace with**Â text field empty. - Click on
**Replace All**.

- A new dialogue box appears which shows the number of replacements that have been made. Click
**OK**.

- This will replace hidden characters with empty strings from the range. We’ll get the expected output of the formula in cell
**C9**.

#### Case 3.2 – Insert VBA Code to Remove Hidden Characters to Fix Formula Result Showing 0 in Excel

We’ll use the same dataset to showcase the VBA code.

**Steps:**

- Select the cell range
**C5:C8**. **Right-click**on the active sheet and select the option**View Code**.

- This will open a blank
**VBA**Â module. - Insert the following code in that module:

```
Sub RemoveCode160()
Dim rng As Range
Dim vrn As Variant
Dim i As Long
Set rg = Selection
If rg.Cells.Count = 1 Then
ReDim vrn(1 To 1, 1 To 1)
vrn(1, 1) = rg.Value
Else
vrn = rg.Value
End If
For i = 1 To UBound(vrn, 1)
vrn(i, 1) = Replace(vrn(i, 1), Chr(160), "")
Next i
rg.Value = vrn
End Sub
```

- Click on the
**Run**button or press the**F5Â**key.

- The code replaced all the hidden characters and returned the output of the formula in cell
**C9**.

**Download the Practice Workbook**

## Related Articles

- How to Display Cell Formulas in Excel
- How to Show All Formulas in Excel
- Why Excel Shows Formulas Instead of Results
- How to Show Formula in Cells Instead of Value in Excel
- How to Show Value Instead of Formula in Excel
- How to Show Formula as Text in Another Cell in Excel
- How to Show Formulas When Printing in Excel

**<< Go Back To Show Excel Formulas | Excel Formulas | Learn Excel**

Thank you for the well stated solution.