Save 5 minutes a day by using ASAP Utilities to quickly fix the numbers that Excel may not recognize.
"Easily make Excel recognize the numbers in your selection"
Sometimes Excel fails to recognize numbers properly, which causes unexpected results when you sort, filter, or use formulas.
For example, when you import a file that was created in another program, such as an accounting package, downloaded from a mainframe or copied from a website, then Excel may fail to recognize the numbers.
Some Excel values may then look like numbers, but Excel thinks they are text. These numbers are then usually left-aligned (by default, text is left-aligned and numbers are right-aligned in Excel).
You sometimes may experience that:
- A formula isn't calculating your numbers properly, such as the SUM of cells, because the 'text'-number are ignored.
- Excel does not allow you to change the number format of selected cells.
- The sort order is confusing and incorrect.
- You have fractions in your cells that Excel doesn't recognize and can't calculate, such as 1/4.
- Excel's built in error checking option and correction isn't available or does not fix the numbers.
But this is easy to fix. Just select the cells and use:
ASAP Utilities » Numbers & Dates » Convert unrecognized numbers (text?) to numbers
This "Convert unrecognized numbers (text?) to numbers" tool quickly fixes the numbers in your selection.
It will:
- Remove leading and trailing spaces around cells with numbers.
- Remove apostrophes in front of numbers.
- If the number format in the cells with numbers is "Text" then it will be changed to "General" in these cells.
- Handle non breaking spaces (this so called character, char 160, is commonly used in web pages as the HTML entity, ).
- Remove leading zeros.
- Recognize and fix text-numbers that are displayed as fractions (such as 1/4, 1/2, etc.)
- Preserve long numbers such as credit cards. Unlike Excel, it will not change the last digit to a zero such as from credit card numbers, which is what Excel's built-in "Convert Text to Number" does.
- Empty cells that only contain one or more spaces.
- Turn all empty cells into truly empty/blank cells that Excel recognizes as being empty.
- Skip cells with formulas or text.
Tip: If your formulas don't give the correct result or don't calculate, then in addition to fixing the numbers, you may also want to verify that the calculation of your workbook is set to Automatic (or press F9 to calculate when calculation is set to Manual):
ASAP Utilities compared to Excel's built in "Convert to number"
Excel (2002+) has built in "Error Checking Options", which can help to detect numbers stored as text and help sometimes to fix them. Numbers that are formatted as text are left-aligned instead of right-aligned in the cell, and are often marked with an error indicator.
If the cells in which numbers are displayed as text contain an error indicator in the upper-left corner (a small green triangle), you can select that cell and then click the error button next to the cell. Then you can choose "Convert to Number" on the pop-up menu.
While this is a handy tool, it doesn't always detect all numbers that are stored as text properly and you cannot choose your own range to run Microsoft Excel's built-in "Convert to Number" tool on.
When compared to Excel's built-in solution, these are the benefits of ASAP Utilities:
- You decide which cells to convert and when. It even works in multiple non-adjacent selected ranges.
- You can assign your own shortcut to this tool for quick use.
- It fixes numbers that Excel can't.
Do you recognize any of these situations?
- I copied some numbers on a web page and pasted them onto an excel spreadsheet. When I use the sum formula, or average formula, excel is not recognizing the numbers in the cell, and won't add up my rows. I've tried reformatting the field as a number, but it's not working. If I re-type the numbers in the field, then Excel recognizes them. This is frustrating!
- I am trying to find the average for a list of numbers. These numbers were pulled from a database, run through a mail-merge program and then given back to me. They look like normal numbers, and don't seem to have any odd formatting, but the AVERAGE function does not seem to see them, and returns a #DIV/0! error. When I click on the cells as if I am going to change the data, the numbers are suddenly recognized by the formula as if they have changed format somehow. Is there some way I can change the format of these numbers, I don't want to have to double click on thousands of cells to miraculously change them to be recognized....
- I have a user with a query from an SQL Database that returns a series of strings that are actually numbers, but Excel treats them as text-strings.
- I've copied a column of data (numbers) from a website and pasted it in Excel. However, the numbers are not recognized as numeric values (we can't sum them, etc.). We have tried all of the "easy" solutions such as changing their format to general or number, copying a 1 from another cell in a clean workbook and using the paste special/multiply function... We've tried the text to columns thing, basically have tried everything in the MS KB, but nothing works except for retyping the value in each cell, which is a bummer since we've got about 2000 cells. Any solutions?
- Excel will not change the format of my cells. I have copied a table that I found online. The problem after the import is that Excel will not change the format of selected cells. I am trying to change some cells to a number format. The only way it will accept the change is to clear the cell and retype it. There are too many numbers to make that fix acceptable.
- I have a bunch of numbers that are preceded with an apostrophe. If I use Excel's Find/Replace and try to replace the apostrophe with nothing, Excel will not recognize it, and nothing will happen. How can we fix this quickly without having to re-enter the numbers?
- Among my data I had creditcard numbers and Excel's Error Checking stripped the last numbers from it.
- My =VLOOKUP() formulas can't find any matching numbers because of unwanted spaces.
- Cells that appear empty aren't because they contain a space.
- Excel doesn't recognize some cells as being empty. That happens often with imported data.
- I tried to remove the spaces by using Excel's =TRIM() function but it didn't remove the spaces in my case. I found that this can happen with so called "non-breaking" spaces. See also this article from Microsoft that describes how to remove these unwanted spaces.
Just select the cells and then choose the following tool in the Excel menu:
ASAP Utilities » Numbers & Dates » Convert unrecognized numbers (text?) to numbers
We find that this tool is often easier, quicker and also more reliable than Microsoft's advised and built-in methods (text to number/paste special workaround, or the text to columns workaround).
And to increase your productivity you can assign your own shortcut to this tool.
Bonus tips, also interesting
How much time will it save?
It's guaranteed that you'll save yourself time and effort by using this tool. However, the actual time saved depends on how much you use Excel, the amount of data you are working with and how often you use this particular tool.
You can easily see how much time ASAP Utilities has saved you so far.
Download
In case you don't have ASAP Utilities yet, you can download the free Home&Student edition (for home projects, schoolwork and use by charitable organizations) or the fully functional 90-day Business trial.
Download page