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.
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.
=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.
Read More: How to Convert Percentage to Decimal in Excel
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.
Download Practice Workbook
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.
