While working with Microsoft Excel, you may find yourself in a situation where Excel won’t recognize your numbers. Sometimes, we collect data from another source. After that, we paste it into our worksheet. But, the numbers do not act as a general format. In this tutorial, we will fix the problem of Excel not recognizing your numbers in cells with suitable illustrations and proper explanations.
Download Practice Workbook
Download this practice workbook
Why is Excel Not Recognizing Numbers in Cells?
Before we start the tutorial, we should learn why this happens. When we directly copy and paste any data, it acts as the same data type from the source. If your data is in number format, it will act as the number. If it is in text format, it will behave like text. So, you should keep in mind what type of data you are inserting into your dataset.
Take a look at this dataset:
We all know numbers always are right-aligned. But, we can see these numbers are left-aligned. Why is this? Sure they are not in number format. You can also see a green triangle in the left corner of the cells.
When you click any of these cells, you will see an error-checking option. Click on that.
After that, you will see the reason for this problem. Here, you can see that the numbers are in text format. That’s why they are not acting as numbers.
3 Methods to Fix Excel Not Recognizing Numbers in Cells
In the following sections, we will provide you with three functional and compelling methods for the problem of Excel not recognizing numbers in cells. We recommend you learn and apply all these methods to your dataset. It will surely increase your knowledge of Excel.
To demonstrate this tutorial, we are going to use this sample dataset:
Here, we have some numbers. But, they are not in number format. We will fix this problem with the upcoming methods. Make sure you read them all.
1. Using Number Format Command in Cells for Not Recognizing Numbers
Now, this method should be the go-to method for Excel not recognizing numbers in cells. It is simple and easy to use. We can change any format with the help of this command. If you face any of these format problems, you can definitely use this method.
📌 Steps
① First, select the range of cells B5:B10.
② Then, from the Home tab, select Number format from the Number group.
③ After that, it will convert your data to number format.
④ Now, if you click General format instead of Number, it will look like this:
Now, both are in the number formats. Choose your option according to your problem.
So, as you can see we are successful in solving the problem of Excel not recognizing numbers in cells.
Read More: Excel Number Format Not Working (2 Reasons with Solutions)
Similar Readings
- How to Custom Cell Format Number with Text in Excel (4 Ways)
- Custom Number Format: Millions with One Decimal in Excel (6 Ways)
- How to Format Number with VBA in Excel (3 Methods)
- Remove Leading Zeros in Excel (8 Easy Methods)
2. Using VALUE Function in Excel for Not Recognizing Numbers
Now, in this method, we are using the VALUE function of Excel. The VALUE function is categorized under the TEXT functions in Excel. It converts a given text string to a number into a numeric value. As we saw, our numbers are in text format. So, we can use this function to convert them into numbers.
📌 Steps
① First, create a new column VALUE next to the Numbers column.
② Then, type the following formula in Cell C5:
③ After that, press Enter. You will see the text converted to a number.
④ Now, drag the fill handle over the range of cells C6:C10.
In the end, we successfully fixed the problem of Excel not recognizing numbers in cells with the VALUE function.
Read More: [Solved] Excel Number Stored As Text
3. Use of the Paste Special Command in Excel
Here, this method is a little bit tricky. We don’t use this method too often. But, it will come in handy in a lot of situations. It is pretty simple and easy to perform. Perform this in any dataset after you have found the problem of Excel not recognizing the numbers in cells. It also converts text formatted numbers into number formats. Let’s try this.
📌 Steps
① First, copy any empty cell from your worksheet.
② Then, select the range of cells B5:B10.
③ After that, right-click on the mouse. Then, select Paste Special option.
④ Now, a Paste Special dialog box will appear. First, select the All except borders radio button. Then, select Add radio button.
⑤ After that, click on OK.
As you can see, we have successfully used the Paste Special command and solved the problem of not recognizing numbers in cells of Excel.
Read More: How to Custom Format Cells in Excel?
💬 Things to Remember
✎ We are showing the problem as text formats. Your numbers can be in different formats. So, make sure you check the format in the Number group from Home Tab.
✎ If you use Number Formats methods, it will show a green triangle even after it changed to numbers. You have to double-click the cell to make it right-aligned.
✎ The VALUE function will only work if your numbers are in text form.
Conclusion
To conclude, I hope this tutorial has provided you with a piece of useful knowledge to fix the problem of Excel not recognizing numbers in cells. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.
Don’t forget to check our website ExcelDemy.com for various Excel-related problems and solutions.
Keep learning new methods and keep growing!
Related Articles
- How to Use Short Date Number Format in Excel
- Apply Engineering Number Format in Excel (2 Easy Ways)
- How to Change International Number Format in Excel (4 Examples)
- Enter 20 Digit Number in Excel (2 Easy Methods)
- How to Enter 16 Digit Number in Excel (3 Simple Ways)
- Display Long Numbers in Excel (4 Easy Ways)
didn’t work for me, I do not have the green triangle in any of my cells; therefore none of your methods worked. I know what the problem is, it is a space after the number finishes, however, I have tried to get rid of the space, but cannot find a way that works via formula, only physically going in to each cell and manually removing the blank space at the end of each number – do you have any solutions for this please?
Hey Luna, you can use VBA codes to solve your problem if these methods don’t work.
Read this article:
https://www.exceldemy.com/convert-text-to-number-excel-vba/
Thank you Shanto, I have tried all the formulas in your link for removing blank space after number; none worked 🙁 The only one I haven’t tried is the VBA code, fingers crossed this one works … I shall let you know. Can I email the spreadsheet to you if not? 🙂
Hey Luna, did you solve the problem using the VBA code? If you face further problems, feel free to share them in the comment box. We are eager to help you to get the desired result.
You should not need VBA, you should be able to use trim() or left() or Right() or mid() functions to remove space. I had a different problem, none of these methods worked for me but on the Home toolbar on the RHS is a Clear Formatting (eraser icon) function. When I did that, and combined it with the =value() function as well as copy and paste values only back into the same column, i was able to fix. Unbeliveable that this is necessary and that Format > Number just does not work sometimes.
Hello JB,
Thank you for your feedback. Admittedly, like everything else, Excel has its downsides too and sometimes the solution to a problem can be quite surprising, but whatever works! Right?
That said, we’re delighted that you’ve shared your experience with us, hopefully, other people find this useful. Have a good day.