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

Get FREE Advanced Excel Exercises with Solutions!

Microsoft Excel is considered one of the most valuable tools currently accessible. With Excelâ€™s tools and capabilities, it is feasible to perform an endless number of actions on a dataset. Although they may seem numbered, not all Excel values are addable. In addition, you have experienced a problem in which an Excel spreadsheet does not recognize values as numbers. This article will discuss two causes and solutions for the Excel Number Format Not Working issue. Therefore, it is recommended that you go through these quick solutions to solve when the Number Format is Not Working in Excel.

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

For the sake of providing an example, letâ€™s investigate a sample dataset. For instance, the datasets have 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 will find issues if we try to calculate the total price. The reason behind this, the Price column contains values in Text Format. We will use the first solution to remove the thousand and decimal separators. Moreover, the following solutions will cover how we can convert Text into Number Format and solve when the Number Format is Not Working in Excel.

Notes

I have yet to say that I have been working with the Microsoft Excel 365 version for this post. Nevertheless, you are free to use any other edition that will provide you with the most significant amount of convenience.

### Reason 1: Excel Is Unable to Recognize Number Format

In most European nations, the Dot (.) represents a thousand separators, and the Comma represents the decimal point. Thus, Excel was unable to interpret the data.

#### Solution: Remove Thousand and Decimal Separators to Fix Number Format Not Working Issue

We must first eliminate the Dot or Period (.) symbol to resolve the difficulties. After that, the Commas must be replaced by the Dot sign. Please follow these instructions closely if you want to finish the assignment well.

STEPS:

• First, create a column titled Formatted Salary.
• Second, copy the C5:C10 range and paste it into the D5:D10 field.

• Third, select the D5:D10Â range.
• After that, go to the Home tab and choose Find & Select.

• Later, click on the ReplaceÂ option.

• Subsequently, the Find and Replace window will appear, and select the ReplaceÂ tab.
• Now, input a Dot (.) sign in the Find What section and keep the Replace With box empty.
• Next, choose the Replace AllÂ button.

• Due to this, Microsoft Excel will pop up and hit OK.

• Thus, the outcome will be displayed like the below one.

• Likewise, again go to the Find and ReplaceÂ window.
• From the Replace tab, input a Comma in the Find What section and put a Period (.) sign in the Replace WithÂ box.
• Next, tap the Replace AllÂ button.

• Consequently, Microsoft Excel will come and click OK.

• At this stage, the outcome will look like the following.

• Now, select the D12Â cell.
• Later, input the below equation in the FormulaÂ bar.

=SUM(D5:D10)

• As a result, the intended output will display like the below one.
• Thus, we solve the Number Format Not WorkingÂ issues.

Formula Breakdown

=SUM(D5:D10)

To understand this formula, you must be familiar with the following Excel function:

SUM Function

• SUM(D5:D10)

The SUM function totals up numbers. You may enter data as single numbers, ranges, or any combination of these. In this example, by involving the SUM function, we find 10571.

### 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 Error Sign Option to Turn Text Format into Number in Excel

We can utilize the Error icon to convert the text format into the Number Format. You must pay close attention to these directions to understand this assignment well.

STEPS:

• First, select the D5:D10Â range.
• Second, click on the ErrorÂ icon.

• Later, choose Convert to Number from the menu.

• Presently, select the D12Â cell.
• Next, input the below equation in the FormulaÂ bar.

=SUM(D5:D10)

• After that, hit OK to see the desired result.
• Thus, we resolve the difficulties with the Number Format Not Working.

#### Solution 2: Run an Excel VBA Code to Solve Number Format Not Working Problem in Excel

The acronym VBA stands for Visual Basic for Applications. Microsoft has created VBA, and we can utilize Excelâ€™s incompatible features using VBA code. We can employ a VBA Macro to automate the process of converting text to numbers. Please complete the assignment according to these instructions.

STEPS:

• First, navigate to the Developer tab and click on Visual Basic.

• Second, click on Insert â†’ Module

• After that, write 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
• Next, press the Ctrl + S key.

• Presently, select the D5:D10Â range.
• Eventually, go to the Developer tab and tap the MacrosÂ icon.

• Consequently, the Macro window will appear.
• Now, choose the FromTextToNumber procedure and click the RunÂ button.

• At this stage, the result will display like the following.

• Select the D12 cell at this time.
• Next, enter the equation shown below into the FormulaÂ bar.

=SUM(D5:D10)

• Then, click OK to see the desired outcome.
• Thus, we have resolved the Number Format Not WorkingÂ issue.

You are welcome to get a free copy of the sample workbook we referred to throughout the presentation by clicking on the link immediately below.

## Conclusion

By following the solutions we have just gone through, you will now be able to solve when the Number Format is Not Working in Excel. You are welcome to submit any questions, comments, or suggestions in the comment box below.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto, BSc, Information Technology, Jahangirnagar University, Bangladesh, has been working with the ExcelDemy project for more than 1 year. He has written 50+ articles for ExcelDemy. Currently, he is working as an Excel & VBA Developer and also provides support and solutions in the ExcelDemy Forum. His work and learning interests are in developing various Excel applications. Outside of work, he enjoys Chess a lot. He is a founding Jahangirnagar University Chess Club member and an internationally rated chess player.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF