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

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.