Text or number converted to unintended number format in Excel

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.

When you type a number in a cell, Microsoft Excel may automatically applya built-in number format to the cell, based on the characteristics of thenumber.
Microsoft Excel automatically applies a built-in number format to a cell,based on the following criteria:
  • If a number contains a slash mark (/) 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), or the number contains more characters than can be displayed based on the column width and font, the number may be converted to scientific notation, or exponential, format.
  • If a number contains leading zeros, the leading zeros are dropped.

Avoiding Automatic Number Formatting

If you want to type a value such as 10e5, 1 p, or 1-2, and you do notwant the value to be converted to a built-in number format, type thenumber as a text value. To type a number as a text value, use any of theappropriate 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

  1. Choose Format Cells
  2. Click the Number tab.
  3. 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:
'1 p

Typing a Fraction

To type a fraction, such as 1/2, so that it is not converted to a datevalue, 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 thefollowing:
    6 7/8
    The cell displays the fraction, but the formula bar displays the truevalue 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 themwhen you type a number, such as 00023, in a cell. In this case, MicrosoftExcel applies the General number format.

To type a value with a leading zero, you can use one of the methodsdescribed in the previous section to type the value as text, or you canuse the following steps to create a custom number format thatcontains leading zeros.
  1. Select the cell range that you want to format.
  2. On the Format menu, click Cells. On the Number tab, click Custom.
  3. In the Type box, 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 afteryou import or parse the text. You do not need to preformat the entries astext. 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.
For more information about formatting numbers as text, click Microsoft Excel Help on the Help menu, type format numbers as text in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
XL2002 XL2000 XL97 XL2001 XL98 XLX XL2004 XL2003 XL2007 XL2010 number text convert kbhowto

Article ID: 214233 - Last Review: 09/23/2015 06:46:00 - Revision: 7.0

Microsoft Excel 95 Standard Edition, Microsoft Excel 97 Standard Edition, Microsoft Excel 2000 Standard Edition, Microsoft Excel 2002 Standard Edition, Microsoft Excel 98 for Macintosh, Microsoft Excel 2001 for Mac, Microsoft Excel X for Mac, Microsoft Excel 2004 for Mac, Microsoft Excel 2008 for Mac, Microsoft Office Excel 2003, Microsoft Excel 2010, Microsoft Office Excel 2007, Excel 2016, Microsoft Excel 2013

  • kbprb KB214233