Number Format Is Not Working in Excel (2 Reasons with Solutions)

This article will discuss two causes and solutions for why the Number Format might not be working.

Number Format Is Not Working in Excel: 2 Reasons with Solutions

Our sample dataset has two tables.

The first table has two columns titled Sales Rep and Salary. Whenever we try to sum up the salaries of all employees, we experience issues. Excel does not recognize these as Number Format because the Salary columns contain thousand and decimal separators.

The second table has three columns Sales Rep, Product, and Price. Here, we run into issues if we try to calculate the total price. The Price column contains values in the Text Format.

Reason 1 – Excel Is Unable to Recognize the Number Format

In most European nations, the dot (.) represents a thousand separators, and the comma represents the decimal point. Excel might not be able to interpret the data.

Solution – Remove Thousand and Decimal Separators to Fix the Number Format Not Working Issue

STEPS:

Create a column titled Formatted Salary.
Copy the C5:C10 range and paste it into the D5:D10 field.

Select the D5:D10 range.
Go to the Home tab and choose Find & Select.

Click on the Replace option.

The Find and Replace window will appear. Go to the Replace tab.
Input a Dot (.) sign in the Find What section and keep the Replace With box empty.
Choose the Replace All button.

Hit OK when you get a notification.

Here’s the result.

Go to the Find and Replace window again.
From the Replace tab, input a Comma in the Find What section and put a Period (.) sign in the Replace With box.
Hit the Replace All button.

Microsoft Excel will notify you that it made the replacements. Click OK.

Here’s the result.

Select the D12 cell.
Input the following formula in the Formula bar.

=SUM(D5:D10)

We’ll get the correct results.

Formula Breakdown

Reason 2 – Numbers Are Formatted as Text in Excel

Another common problem is that the values we want to execute are in Text Format, and we must change the value format to the Number Format in this case.

Solution 1 – Utilize the Error Sign Option to Turn Text Format into Number in Excel

STEPS:

Select the D5:D10 range.
Click on the Error icon.

Choose Convert to Number from the menu.

Select the D12 cell.
Input the following in the Formula bar.

=SUM(D5:D10)

Hit Enter to see the desired result.

Solution 2 – Run an Excel VBA Code to Solve the Number Format Not Working Problem in Excel

STEPS:

Navigate to the Developer tab and click on Visual Basic.

Click on Insert and select Module.

Insert the following code in the Module box.

Sub FormTextToNumber()
Dim selectedField As Range
On Error Resume Next
Set selectedField = Selection _
  .SpecialCells(xlCellTypeConstants, 23)
On Error GoTo errHandler
If Not selectedField Is Nothing Then
  Cells.SpecialCells(xlCellTypeLastCell) _
      .Offset(0, 1).Copy
   selectedField.PasteSpecial Paste:=xlPasteValues, _
       Operation:=xlPasteSpecialOperationAdd
Else
  MsgBox "Unable to convert."
End If
exitHandler:
  Application.CutCopyMode = False
  Set selectedField = Nothing
  Exit Sub
errHandler:
  MsgBox "Unable to convert."
  Resume exitHandler
End Sub