XL: Text or Number Converted to Unintended Number Format

ID: Q159491

The information in this article applies to:

SYMPTOMS

When you type a number in a cell, Microsoft Excel may automatically apply a built-in number format to the cell based on the characteristics of the number.


CAUSE

Microsoft Excel automatically applies a built-in number format to a cell based on the following criteria:

This behavior is by design of Microsoft Excel.


WORKAROUND

Avoiding Automatic Number Formatting

If you want to type a value such as 10e5, 1 p, or 1-2, and you do not want the value to be converted to a built-in number format, type the number as a text value. To type a number as a text value, follow the appropriate procedure below.

All Versions of Microsoft Excel:

Place a space at the beginning of the entry (or press OPTION+SPACEBAR if you are using Microsoft Excel for the Macintosh).

NOTE: This method does not work if the entry resembles a number formatted in scientific notation. For example, typing " 1e9" (without the quotation marks) results in a scientific number.



Microsoft Excel 5.0 and Later:

Select a range of cells, click Cells on the Format menu, and then click the Number Tab. Click Text, and then click OK. This method allows you to type data in the selected cells as text. You must perform these steps before you type the numbers in the cells.



Microsoft Excel 4.0 and Later:

Precede the entry with an apostrophe. For example, type the following:

'1 p




Microsoft Excel 3.0 and Earlier:

Enclose the entry in quotation marks and precede the value with an equal sign. For example, type the following:

="1 p"


Typing a Fraction

To type a fraction, such as 1/2, such that it is not converted to a date value, type the fraction in the form XX YY/ZZ, where XX is the integer component of the fraction, YY is the numerator, and ZZ is the denominator. To do this, follow these steps:

  1. Select the cell in which you want to type the fraction.


  2. Type the fraction in the form XX YY/ZZ. For example, type the following:

    6 7/8.


The cell displays the fraction, but the formula bar displays the true value of the fraction (in this case, 6.875).

To type the fraction 1/2, type 0 1/2. If you omit 0 (zero), Microsoft Excel converts the fraction to a date.

NOTE: After Microsoft Excel converts a fraction to a date, its underlying value is changed. As a result, if you import a text file or parse text, you must precede these entries with a text character before you import or parse the file (as described below in the note under "Maintaining Number Formats When You Import Text Files or Use Parse").

Typing a Value with Leading Zeros

Because leading zeros are insignificant digits, Microsoft Excel omits them when you type a number, such as 00023, in a cell. In this case, Microsoft Excel applies the General number format.

To type a value with a leading zero, you can use one of the methods described in the previous section to type the value as text, or you can use the following appropriate steps to create a custom number format that contains leading zeros.

Microsoft Excel 5.0 and Later:

  1. Select the cell range you want to format.


  2. On the Format menu, click Cells. On the Number tab, click Custom.


  3. In the Type box (Code box in version 5.0), type a zero for each digit in the number. For example, if the number has five digits, type five zeros.


  4. Click OK.


If you import a text file or parse text, you can use this procedure after you import or parse the text. You don't need to preformat the entries as text (as described in the following section). However, you must preformat numbers that are evaluated as dates, times, or scientific notation.



Microsoft Excel 3.0 and 4.0:

  1. Select the cell range you want to format.


  2. On the Format menu, click Number.


  3. In the Code box (Format box in version 3.0), type a zero for each digit in your number.

    For example, if your number has five digits, type five zeros.


  4. Click OK.


Retaining Number Formats When You Import Text Files or Use Parse

When you import a text file into Microsoft Excel or use the Parse (located on the Data menu in Microsoft version 4.0) or the Text To Columns command (located on the Data menu in Microsoft Excel 5.0, 7.0, 97 and 98) to place text entries in separate columns, Microsoft Excel applies number formats to the data if the file contains entries similar to those mentioned in this article.

NOTE: The following Application Note applies to Microsoft Excel 4.0. Some information in the document is not relevant to later versions of Microsoft Excel. For example, the Parse command on the Data menu in Microsoft Excel 4.0 is replaced by the Text To Columns command, which is also located on the Data menu.

A Microsoft Application Note about text importing is also available. For information about what this Application Note discusses and how you can obtain it, see the following articles in the Microsoft Knowledge Base:

Q102142 : "Excel AppNote: Opening and Saving Text Files (ME0802)"

This Application Note is for Microsoft Excel version 4.0 for the Macintosh.



NOTE: If you are importing a text file or parsing text, and you do not have the Application Note, "Opening and Saving Text Files," you must open the file in a text editor and precede the entries with the appropriate text character for your version of Microsoft Excel. When you open the file in Microsoft Excel version 4.0 and later, these entries are treated as text.


REFERENCES

For more information about formatting numbers as text, click the Index tab in Microsoft Excel Help, type the following text 


   numbers, formatting


and then double-click the selected text to go to the "Format existing numbers as text" topic.

For more information about Converting Text to Columns, click the Index tab in Microsoft Excel Help, type the following text 


   parsing


and then double-click the selected text to go to the "Separate copied text data into columns " topic.

"User's Guide," version 5.0, "How Microsoft Excel Interprets What You Enter", page 107

"User's Guide," version 5.0, "Converting Text to Columns", page 724

"User's Guide 1," version 4.0, "Formatting Data with Number Formats", pages 218-227

Additional query words: XL97 


Keywords          : kbualink97 kbdta xlui xlformat xlformula 
Version           : WINDOWS:3.0,4.0,4.0a,7.0,97; MACINTOSH:3.0,4.0,5.0,98
Platform          : MACINTOSH OS/2 WINDOWS 
Issue type        :

Last Reviewed: April 25, 1999