Article ID: 141765 - Last Review: August 17, 2005 - Revision: 3.1

Unexpected behavior with numbers formatted as text

View products that this article applies to.
System TipThis article applies to a different operating system than the one you are using. Article content that may not be relevant to you is disabled.
This article was previously published under Q141765

On This Page

Expand all | Collapse all

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.
Back to the top

CAUSE

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.
Back to the top

WORKAROUND

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

Method 1: Retype the Numbers

After you change the format, retype the numbers.
Back to the top

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.
Back to the top

Method 3: Use a Microsoft Visual Basic for Applications Procedure

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to 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 requirements. Create a Visual Basic for Applications macro to reenter the numbers in the selected cell or range of cells. Type the following code in a module sheet: 
   Sub Enter_Values()
      For Each xCell In Selection
         xCell.Value = xCell.Value
      Next xCell
   End Sub
Before you run this procedure, switch to the worksheet that contains the numbers you want to reentered; select the range of cells that contain those numbers, and then run the macro.
Back to the top

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. Under Operation, click Multiply and click OK.
Back to the top

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, under Category, click Text, and click OK.
  4. In the cell or range of cells selected, type numeric values.
After following these steps, you cannot change the format back to a number without reentering the numbers.

NOTE: If you enter numbers before formatting the cells to use the Text number format, they remain numbers, but they are aligned to the left in the cell to appear as text.
Back to the top

REFERENCES

Microsoft Excel 97

For more information about formatting numbers as text, click the Office Assistant, type Format numbers as text click Search, and then click to view "Format numbers as text."

NOTE: If the Assistant is hidden, click the Office Assistant button on the Standard toolbar. If Microsoft Help is not installed on your computer, please see the following article in the Microsoft Knowledge Base:
120802  (http://support.microsoft.com/kb/120802/EN-US/ ) How to Add/Remove a single Office program or component
Back to the top

Microsoft Excel 7.0

For more information about formatting numbers as text, click the Answer Wizard in Help and type:
How do I format numbers as text
Back to the top

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
Back to the top
APPLIES TO
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 95 Standard Edition
  • Microsoft Excel 5.0 Standard Edition
Back to the top
Keywords: 
kbualink97 kbprb KB141765
Back to the top