Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

Excel Number Format Not Working (2 Reasons 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.

excel number format not working


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.


2 Reasons with Solutions to Fix When Number Format Not Working in Excel

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

Excel Is Unable to Recognize Numbers


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.

Remove Thousand and Decimal Separators

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

Remove Thousand and Decimal Separators

  • Later, click on the Replace option.

Choosing the Replace option form the menu

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

Remove Thousand and Decimal Separators

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

Hitting OK from the Microsoft Excel window

  • Thus, the outcome will display like the below one.

Output of removing the Dot sign

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

Remove Thousand and Decimal Separators

  • Consequently, Microsoft Excel will come and click OK.

Hitting OK from the Microsoft Excel window

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

Output of putting Commas as Decimal separators

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

Thus the Number Format Not Working issue is solved

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.

Numbers Are Formatted 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.

Utilize Error Sign Option to Turn Text Format into Number

  • Later, choose Convert to Number from the menu.

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

Number Format Not Working issue is solved


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.

Run an Excel VBA Code to Convert Text to Number Format

  • Second, click on InsertModule

Click Insert followed by Module from the VBA window

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

Press the  Ctrl + S  key to save

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

Run an Excel VBA Code to Convert Text to Number Format

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

Run an Excel VBA Code to Convert Text to Number Format

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

Run an Excel VBA Code to Convert Text to Number Format

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

Thus the Number Format Not Working issue is solved


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. Further articles on the ExcelDemy website are like this one. Continue to use them, and let us know if you come up with any new ideas or techniques for finishing the assignment. You are welcome to submit any questions, comments, or suggestions in the comment box below.

Lutfor Rahman Shimanto

Lutfor Rahman Shimanto

Hi there! I am Lutfor Rahman Shimanto. I have completed my graduation in Information Technology from Jahangirnagar University. Currently, I am working as a technical content writer at ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of work, I enjoy Chess a lot. I am a founding member of the Jahangirnagar University Chess Club and an internationally rated chess player.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo