Prevent formatting of numbers

When you are working with numbers for purposes other than calculation, you can treat the numbers as text. For example, if you are using credit card numbers, or number codes that contain 16 digits or more, you should use the text format because Excel has a maximum of 15 digits of precision. If there are more than 15 numbers, Excel rounds the numbers that follow the fifteenth digit down to zero.

When a number is formatted as text, it is left-aligned rather than right-aligned in the cell. It is also not used in any calculation.

1. Select the cells where you want to apply text formatting.

   To select all cells on a sheet, click the triangle at the top-left corner of the sheet grid.

   

   Triangle

2. On the Home tab, under Number, click the Number Format pop-up menu, and then click Text.

   Tips: 

   • To prevent automatic formatting of numbers cell by cell, type a single quotation mark (') as the first character in a cell, followed by the number. When you press RETURN , the single quotation mark will not appear on the sheet, but it will appear in the formula bar. Use this method when you need to display a visible equal sign (=) as the first character in a cell, and want to prevent Excel from interpreting the equal sign as the start of a formula.

   • When you enter a number that begins with a zero — for example, a product code — Excel automatically deletes the zero. If this is not what you want, you can create a custom number format that forces Excel to retain the leading zero. For example, if you're typing or pasting ten-digit product codes in a sheet, Excel will change numbers like 0784367998 to 784367998. In this case, you could create a custom number format consisting of the code 0000000000, which forces Excel to display all ten digits of the product code, including the leading zero.

See also

Keep leading zeros

Create and apply a custom number format

Display or hide zero values