Microsoft Excel - A Simple Way To Convert Numbers Entered As Text (Excel 97/2000/2001/2002)


You may occasionally come across numbers in Excel that were entered with a preceding apostrophe ('), which formats the number as text. You're especially likely to run into this problem when you import data into Excel from some other application source. If you want to convert the value to a genuine number, you may find that the task isn't as easy as you'd suspect. You might logically assume that changing the cell's format to a number format would do the trick. Unfortunately, such is not the case. If you only need to convert one or two values, you can just re-enter them. However, for a large volume, there are more efficient ways.


If you have Excel 2002, there's an easy solution. You can take advantage of a smart tag that addresses this specific problem. Simply select the cell or range of cells that you want to convert. When the smart tag button appears, click on it and choose Convert To Number from the action menu.


Things are a bit trickier if you have an older version of Excel, but the process is still better than a brute force re-entry effort. First, enter the number 1 in any blank cell. Next, select the cell and choose Edit | Copy from the menu bar. Then, select all the cells containing values you want converted. Choose Edit | Paste Special from the menu bar. Finally, select the Multiply option button and click OK.

Go back