XL: Unexpected Behavior with Numbers Formatted as Text

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel 97 for Windows
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a
• Microsoft Excel for the Power Macintosh, versions 5.0, 5.0a
• Microsoft Excel 98 Macintosh Edition

SYMPTOMS

In Microsoft Excel, if a number is formatted with the Text number format, and you attempt to change the format to something other than Text, the number may not be changed as you expect.

WORKAROUND

To work around this behavior, use any of the following methods to reenter the numbers in the reformatted cells.

Method 1: Retype the Numbers

After you change the format, retype the numbers.

Method 2: Select Edit Directly In Cell

On the Tools menu, click Options or Preferences, click the Edit tab, and verify that the Edit Directly In Cell check box is selected. Double-click the cell you want to format, and press ENTER.

Method 3: Use a Visual Basic for Applications Procedure

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/supportnet/refguide/

   Sub Enter_Values()
      For Each xCell In Selection
         xCell.Value = xCell.Value
      Next xCell
   End Sub

Method 4: Use Paste Special

To use this method, follow these steps:

1. Type the value 1 in a blank cell on the worksheet.

2. Select the cell that contains the value 1 and click Copy on the Edit menu.

3. Select the range that contains the numbers that are formatted as text. Click Paste Special on the Edit menu, click Multiply under Operation, and click OK.

STATUS

This behavior is by design. When you format a cell using the Text number format, the cell is changed and anything that is entered is displayed as text.

MORE INFORMATION

To format a number as text, follow these steps:

1. Select a single cell or a range of cells.

2. On the Format menu, click Cells.

3. On the Number tab, click the Text category, and click OK.

4. In the cell or range of cells selected, type numeric values.

NOTE: If numbers are entered before the cells are formatted to use the Text number format, they remain numbers, but they are aligned to the left in the cell to appear as text.

REFERENCES

Microsoft Excel 7.0, Microsoft Excel 97, and Microsoft Excel 98 Macintosh Edition

For more information about formatting numbers as text, click the Answer Wizard in Help and type:

   How do I format numbers as text

Microsoft Excel 5.0

"User's Guide," version 5.0, Chapter 9, "How Microsoft Excel Interprets What You Enter"

For more information about Formatting numbers as text, click the Search button in Help and type:

   numbers, formatting