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

Read More: [Solved] Excel Number Stored As Text

#### 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, _
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``````
• Press Ctrl + S.

• Select the D5:D10 range.
• Go to the Developer tab and click Macros.

• The Macro window will appear.
• Choose the FromTextToNumber procedure and click the Run button.

• Here’s the result.

• Insert a formula in D12:

`=SUM(D5:D10)`

• Click OK to see the desired outcome.

## Related Articles

<< Go Back to Number Format | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto, BSc, Information Technology, Jahangirnagar University, Bangladesh, has worked with the ExcelDemy project for over a year. He has written 50+ articles and provided solutions of 100+ comments for ExcelDemy. Currently, he works as an Excel & VBA Developer and provides support and solutions in the ExcelDemy Forum. He has solved 100+ ExcelDemy Forum problems. His work and learning interests are in developing various Excel & VBA and Desktop applications. Outside of work, he enjoys Chess... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF