Excel ISNUMBER Not Working (3 Reasons with Solutions)

We have to determine whether a cell contains a number value or not in different parts of the Excel formulation. But in a lot of unfortunate cases, those formulas don’t work. To tackle this problem, we discuss how you can solve the issue of the ISNUMBER function not working in Excel with detailed explanations.

Among all of those errors, we have the following issue where we need to switch the cell format from text to number or general in order to enable the ISNUMBER function properly (showing TRUE in cell C6). This is done by converting cell B5 to a number.

Excel ISNUMBER Not Working: 3 Reasons with Solutions to This Issue

Here, we will present 3 separate solutions for 3 separate issues associated with the usage of the ISNUMBER function. For avoiding any compatibility issues, use Excel 365 edition instead.

Reason 1: Cell Formatted as Text

Sometimes the cell in which the values might have been formatted as Text, which made the ISNUMBER function confused and return FALSE despite having a number value in the cell.

If you look at the following example ISNUMBER function in cell C6, then you can see that the result is showing FALSE, which is clearly wrong, as cell C6 refers to cell B6 and B6 actually contains numerical numbers.

Upon investigation, it found that cell B6 actually was in a text format, before the value 123 was entered.

And that is what creates the issue for the ISNUMBER function.

Solution: Change Cell Format to Number/General

To resolve the issue, follow the process below.

If you look closely, you can see that there is a warning flag in the cell B6. This flag means that there is a format mismatch detected by the ISNUMBER formula.
Select cell B6 and choose Convert to Number from the warning drop down menu.

After clicking on the Convert to Number, we can see that the ISNUMBER function shows the correct status in cell C6 and at the same time, the cell format also been changed to General format.

Reason 2: Cell in Text Format Due to LEFT/RIGHT Functions

Using the LEFT or RIGHT function will convert the cell content to text, so in order to use the ISNUMBER function, we just need to use the VALUE function to convert the text format to number format.

In the below formula in cell C6, we need to determine whether there is a number in cell B6 or not.
But we can see that, despite cell B6 containing a numerical value, the C6 cell outcome is showing false. Which is completely wrong.

Solution: Use VALUE Function to Convert to Number Format

To resolve the issue, we have to use the VALUE function just before the LEFT Function. Using the VALUE function will convert the text values back to numbers, and the status now shows the correct status.

Reason 3: Special Character

Special characters in the cell will convert the cell values into text, so the user has to manually remove the special characters.

If you notice closely, we have the special character in cell B6, after the value 123.
And we also have the formula ISNUMBER referring to cell B6 and showing False.
The main issue is that special character $, because of this, the formula ISNUMBER got confused and showed false output.

Solution: Remove Special Character Using Find and Replace

You can solve this issue by opting for Excel’s Find and Replace method.

For this, press the Ctrl+H keyboard, upon pressing this shortcut, there will be a dialog box.
In the dialog box, enter $ in the Find what box.
and enter a single space in the Replace with box.
Then click on Replace All.

After removing the special characters in front of the numbers using the Find and Replace tool, we can see that the Status is showing that the ISNUMBER is working properly.

Download Practice Workbook

You can download the workbook and practice with them.

ISNUMBER Not Working.xlsx

Conclusion

To sum it up, the issue of how we can resolve the ISNUMBER function is not working in Excel on various occasions. Feel free to ask any questions or feedback through the comment section.

<< Go Back to Excel ISNUMBER Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!