If you want Excel to treat certain types of numbers as text, you can use the text format instead of a number format. For example, If you are using credit card numbers, or other number codes that contain 16 digits or more, you must use a text format. That’s because Excel has a maximum of 15 digits of precision and will round any numbers that follow the 15th digit down to zero, which probably isn’t what you want to happen.
It’s easy to tell at a glance if a number is formatted as text, because it will be left-aligned instead of right-aligned in the cell.
-
Select the cell or range of cells that contains the numbers that you want to format as text. How to select cells or a range.
: You can also select empty cells, and then enter numbers after you format the cells as text. Those numbers will be formatted as text.
-
On the Home tab, in the Number group, click the arrow next to the Number Format box, and then click Text.
: If you don't see the Text option, use the scroll bar to scroll to the end of the list.
:
-
To use decimal places in numbers that are stored as text, you may need to include the decimal points when you type the numbers.
-
When you enter a number that begins with a zero—for example, a product code—Excel will delete the zero by default. 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 worksheet, 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. For more information about this issue, see Create or delete a custom number format and Keep leading zeros in number codes.
-
Occasionally, numbers might be formatted and stored in cells as text, which later can cause problems with calculations or produce confusing sort orders. This sometimes happens when you import or copy numbers from a database or other data source. In this scenario, you must convert the numbers stored as text back to numbers. For more information, see Convert numbers stored as text to numbers.
-
You can also use the TEXT function to convert a number to text in a specific number format. For examples of this technique, see Keep leading zeros in number codes. For information about using the TEXT function, see TEXT function.
