5 Ways to Fix Excel Not Recognizing Numbers

Excel usually detects numbers as you enter them. But, not always. When the data isn’t stored in appropriate format, you will notice warning with a green indent on the top-left corner of the cell. 

Generally, this is because Excel cannot store numbers in a cell formatted as text. Excel will not recognize your data as numbers if your cell contains trailing spaces or a leading apostrophe(‘) in the numbers.

This can also occur when you copy-paste data from external sources or enter your data manually.

Depending on the reasons, there are different solutions for the issue, so try to go through every solution we have mentioned below.

Convert Into Number Format

You must format the cells to the number format to make Excel recognize them as numbers. You can achieve that by using the Format cells feature in Excel that modifies the format of the cells.

1. Select all Cells and Press CTRL + 1 to open Format Cell Window.
2. On the pop-up dialog box, select the Number Category and click Ok.
3. Now all the data will convert into a Number Format.

Try Using the Value Function

The VALUE function in Excel converts text values into numbers. It also recognizes strings, dates, and times and converts them into numerals. 

Syntax: =VALUE(“123”)

Using this formula, Excel will return 123 as a numeric value.

Example:

We will convert data in the Score column into Number Format using the VALUE function.

1. Click in an empty cell and enter the formula,
   =VALUE(cell)
2. The VALUE function gives the numeric value of the selected cell (B2).
3. Now Flash Fill to fill the remaining cells.

Using Paste Special Command

The paste special command can be helpful when you are copy-pasting data from external sources. It discards the source formatting and converts text into numbers, thus, making Excel recognize numbers.

Here is how to use the paste special command.

1. Copy any of the empty cells.
2. Select the data or cells you want to fix.
3. Now press CTRL+ALT+V to open the Paste Special window.
4. Select All in the Paste and Add in Operation Section > click OK.

Via Text to Columns Feature

You can use Excel’s Text to column feature to convert text data into numbers. It is a helpful feature for parsing text data that needs to be formatted properly or consistently.

You can follow the steps below to learn how to use this feature and make Excel recognize numbers.

1. Select the Cells that contain numbers and go to the Data tab.
2. Select Text to Columns.
3. Choosing Delimited as your file type, hit Next.
4. Uncheck all the options in the Delimiters section, and hit Next.
5. Choose General in Column data format and Click Finish.

Use Find and Replace Feature

In an Excel spreadsheet, when there are special characters such as apostrophes (‘), commas (,), and spaces between numbers, it will recognize them as text values.

The Find and Replace feature can help you find and remove all those particular characters.

1. Click CTRL + H to open the Find and Replace window.
2. Enter a comma (,) or single quote (‘), whichever you are finding to remove in the Find What section.
3. Leave blank in the Replace with box and Click Replace All.

All commas or single quotes (‘) will be removed, and Excel will recognize them as numbers.