XL: Text or Number Converted to Unintended Number Format

• Microsoft Excel for Windows, versions 3.0, 4.0, 4.0a, 5.0
• Microsoft Excel for the Macintosh versions 3.0, 4.0, 5.0
• Microsoft Excel for OS/2, version 3.0
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel 97 for Windows
• Microsoft Excel 98 Macintosh Edition

SUMMARY

In Microsoft Excel, when you enter a number in a cell, a built-in number format is automatically applied based on the characteristics of that number. The format that is used is based on the following set of rules:

• If a number contains a slash (/) or hyphen (-), it may be converted to a date format.
• If a number contains a colon (:), or is followed by a space and the letter A or P, it may be converted to a time format.
• If a number contains the letter E (in uppercase or lowercase letters, for example 10e5), it may be converted to scientific notation, or exponential, format.
• If a number contains leading zeros, the leading zeros are dropped.

WORKAROUNDS

Avoiding Automatic Number Formatting

If you want to enter 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, enter the number as text value. To enter a number as a text value, use the following appropriate method.

Microsoft Excel Version 5.0 and Later:

To select a range of cells, click Cells on the Format menu, click the Number Tab, and then click the TEXT option. This allows you to enter data into those cells as text.

Microsoft Excel Version 4.0:

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

   '1 p

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

   ="1 p"

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 that is formatted in scientific notation. For example, entering " 1e9" (without the quotation marks) results in a scientific number.

Entering a Fraction (Versions 3.0 and Later)

To enter a fraction, such as 1/2, so 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 enter the fraction.

2. Enter the fraction in the form XX YY/ZZ.

To enter the fraction 1/2, type 0 1/2. If you omit the zero, the fraction is converted to a date.

Note that after a fraction is converted 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 your file (as described in the note under "Maintaining Number Formats When You Import Text Files or Use Parse").

Entering a Value with Leading Zeros

Because leading zeros are insignificant digits, Microsoft Excel omits them when you enter a number, such as 00023, into a cell. In this case, the General number format is applied.

To enter a value with a leading zero, use one of the methods described in the previous section to enter the value as text, or do the following to create a custom number format that contains leading zeros:

1. Select the cell range you want to format.

2. In Excel 5.0 and later, click Cells on the Format menu. Click the Number tab, and then click Custom in the Category box. In Excel 4.0 and earlier, click Number on the Format menu.

3. In the Code box, enter a zero for each digit in your number. For example, if your number has 5 digits, enter 5 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 command (located on the Data menu) to place text entries in separate columns, automatic number formats are applied to your data if your file contains entries similar to those mentioned above.

The Application Note "Opening and Saving Text Files" (WE0801 for Windows or ME0802 for the Macintosh) is available from Microsoft Excel Product Support to help you import text files without losing the original number formats of the data. For more information about the Windows version of this Application Note, call Microsoft Excel product support at (425) 635-7070. For more information about the Macintosh version of this Application Note, call (425) 635-7080.

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 these entries with the appropriate text character for your version of Microsoft Excel. When you open the file in Microsoft Excel, these entries will be treated as text.

REFERENCES

"User's Guide 1," version 4.0, pages 218-227