Converting Non-numbers To Actual Values
Q. I often import data into Excel from various applications, including Access. I've found that values are sometimes imported as text, which means I can't use them in calculations or with commands that require values. I've tried formatting the cells as values, with no success. The only way I've found to convert the text into values is to edit the cell and then press Enter. Is there an easier way to make these conversions?
This is a common problem in Excel. The good news is the Excel 2002 is able to identify such cells and you can easily correct them If you're using an older version of Excel, you can use this method:
- Select any empty cell
- Enter the value 1 into that cell
- Choose Edit, Copy
- Select all the cells that need to be converted
- Choose Edit, Paste Special
- In the Paste Special dialog box, select the Multiply option, then click OK.
This operation multiplies each cell by 1, and in the process converts the cell's contents to a value.
Excel has a long history, and it continues to evolve and change. Consequently, the tips provided here do not necessarily apply to all versions of Excel.
In particular, the user interface for Excel 2007 (and later), is vastly different from its predecessors. Therefore, the menu commands listed in older tips, will not correspond to the Excel 2007 (and later) user interface.
Browse Tips by Category
Search for Tips
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks