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, use any of the
appropriate methods below.
Method 1
Place a space at the beginning of the entry.
NOTE: This method does not work if the entry resembles a number formatted in scientific notation. For example, typing
1e9 results in a scientific number.
Method 2
- Select a range of cells, and then click Cells on the Format menu.
- 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.
Method 3
Precede the entry with an apostrophe. For example, type the following:
Typing a Fraction
To type 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:
- Select the cell in which you want to type the fraction.
- Type the fraction in the form XX YY/ZZ. For example, type the
following:
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.
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 steps to create a custom number format that
contains leading zeros.
- Select the cell range that you want to format.
- On the Format menu, click Cells. On the Number tab, click Custom.
- In the Type box, type a zero for each digit in the number. For example, if the number has five digits, type five zeros.
- Click OK.
If you import a text file or parse text, you can use this procedure after
you import or parse the text. You do not need to preformat the entries as
text. However, you must preformat numbers that are evaluated as dates, times, or scientific notation.
Retaining Number Formats When You Import Text Files or Use Parse
When you import a text file into Microsoft Excel or use the
Text to Columns command (located on the
Data menu) 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.