Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

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.

↑ Back to the top


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.

↑ Back to the top


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.

↑ Back to the top


Keywords: kb, ocsentirenet, kbprb, kbpending, kbexcel10ready, kbfreshness2006, kboffice12yes

↑ Back to the top

Article Info
Article ID : 282141
Revision : 7
Created on : 8/20/2020
Published on : 8/20/2020
Exists online : False
Views : 121