Select the product you need help with
- Internet Explorer
- Windows Phone
- More products
Text or number converted to unintended number format in Excel
Article ID: 214233 - View products that this article applies to.
This article was previously published under Q214233
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.
Microsoft Excel automatically applies a built-in number format to a cell, based on the following criteria:
Avoiding Automatic Number FormattingIf 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 1Place 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 3Precede the entry with an apostrophe. For example, type the following:
Typing a FractionTo 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:
Typing a Value with Leading ZerosBecause 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.
Retaining Number Formats When You Import Text Files or Use ParseWhen 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.
Article ID: 214233 - Last Review: September 18, 2011 - Revision: 5.0