You are currently offline, waiting for your internet to reconnect

You cannot convert text to number when the cell contains a number

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.

This article was previously published under Q282141
SYMPTOMS
When you try to format a cell as a number in Microsoft Excel, the cell remains formatted as text.
CAUSE
This issue occurs when the following conditions are true:
  • In the Format Cells dialog box, you format a cell as text.
  • You then type a number into that cell.
  • In the Format Cells dialog box, you then format the cell as a number.
The cell remains formatted as text.
WORKAROUND
To convert cells that are formatted as text to numbers, follow these steps:
  1. Select the cell that is formatted as text that you want to convert to a number.

    Notice that the Error Checking Options button appears if you select the cell or rest the mouse pointer over the cell. The cell should contain an Error Indicator in the upper left corner of the cell.

    NOTE: If the Error Checking Options button does not appear or you do not see an Error Indicator, turn on background error checking.

    To do this in Microsoft Excel 2002 or in Microsoft Office Excel 2003, follow these steps:
    1. On the Tools menu, click Options.
    2. In the Options dialog box, click the Error Checking tab.
    3. In the Settings section, click to select the Enable background error checking check box.
    4. In the Rules section, make sure the Number stored as text rule is selected, and then click OK.
    To do this in Microsoft Office Excel 2007, follow these steps:
    1. Click the Microsoft Office Button, and then click Excel Options.
    2. On the Formulas tab, click to select the Enable background error checking check box in the Error Checking section.
    3. Under Error checking rules, make sure that the Numbers formatted as text or preceded by an apostrphe check box is selected, and then click OK.
  2. On the Error Checking Options button, click the down arrow.

    Notice that the Number Stored as Text menu appears.
  3. Click Convert to Number.
Notice that the cell is converted to the number format and the Error Indicator no longer appears in the cell.
XL2002 prb kbnoOfficeAlertID XL2003 XL 2007
Properties

Article ID: 282141 - Last Review: 01/29/2007 06:17:32 - Revision: 2.2

Microsoft Office Excel 2007, Microsoft Office Excel 2003, Microsoft Excel 2002 Standard Edition

  • kbpending kbprb KB282141
Feedback