How to get Excel to Recognize Numbers in Cells (3 Techniques)

Below is a dataset with some formatted numbers.

dataset of Excel Not Recognizing Numbers

Method 1 – Using the Number Format Command 


  • Select the range of cells B5:B10.

select range of cells

  • From the Home tab, select Number format from Number.

select number format in excel

  • This will convert your data to a number format.

excel not recognizing numbers in cells fixed

  • If you click General format instead of Number, it will look like this:

excel not recognizing numbers in cells fixed

Both are in the number formats.

  • Choose your option according to your problem.

Read More: Excel Number Format Not Working (2 Reasons with Solutions)

Similar Readings

Method 2 – Using the VALUE Function 


  • Create a new column, VALUE, next to the Numbers column.

  • Enter the following formula in Cell C5:

formula for converting to numbers in excel

  • Press Enter. You will see the text converted to a number.

recognizing as numbers in cell excel

  • Drag the fill handle over the cells C6:C10.

excel not recognizing numbers in cells fixed

Read More: [Solved] Excel Number Stored As Text

Method 3 – Use of the Paste Special Command


  • Copy any empty cell from your worksheet.

copy any empty cells

  • Select cells B5:B10.

  • Rght-click and select Paste Special.

Use of the Paste Special Command in Excel

  • A Paste Special dialog box will appear. Select the All except borders. Select Add.

  • Click OK.

excel not recognizing numbers in cells fixed

Read More: How to Custom Format Cells in Excel?

Things to Remember

We are showing the problem in text formats. Your numbers can be in different formats. So, make sure you check the format in the Number group from the Home Tab.

If you use the Number Formats method, it will show a green triangle even after it changes 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.

Download the Practice Workbook

Download the workbook to practice.

Related Articles

Get FREE Advanced Excel Exercises with Solutions!
A.N.M. Mohaimen Shanto
A.N.M. Mohaimen Shanto

A.N.M. Mohaimen Shanto, a B.Sc. in Computer Science and Engineering from Daffodil International University, boasts two years of experience as a Project Manager at Exceldemy. He authored 90+ articles and led teams as a Team Leader, meticulously reviewing over a thousand articles. Currently, he focuses on enhancing article quality. His passion lies in Excel VBA, Data Science, and SEO, where he enjoys simplifying complex ideas to facilitate learning and growth. His journey mirrors Exceldemy's dedication to excellence and... Read Full Bio

  1. 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?

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

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

Leave a reply

Advanced Excel Exercises with Solutions PDF