1. Computing

Format Numbers as Text in Excel

By

Format Numbers as Text in Excel
Format Numbers as Text in Excel

Format Numbers as Text in Excel

© Ted French

Default General Format

If you use Excel to store long numbers - such as credit card or bank card numbers - you might find the number being changed to a format you do not want.

By default, all cells in an Excel worksheet use the General format, and one of the limits of this format is that for numbers with more than 11 digits is converted to scientific (or exponential) notation.

An example of a long number changed to scientific notation by the general number format can be seen in cell D1 in the image above.

The Number Format

One option that is often tried to correct this problem is to use the Number format.

However, the number format is limited to displaying numbers of 15 digits in length. Any digits beyond this limit are converted to zeros as can be seen in cell D2 in the image above.

Credit card and bank card numbers - which are 16 digits long - end up having the last digit changed to a zero, which makes this format no better of a choice than the general format.

Formatting Numbers as Text

The best option for these long numbers is to use the Text format as seen in cell D3 in the image above.

Formatting the cell for text data before entering the number will ensure that it is displayed exactly the way it was entered.

The character limit per cell for the text format is 1,024.

In addition to keeping the number identical to the way it was entered, numbers formatted as text as text can still be used in formulas using basic mathematical operations - such as adding and subtracting.

They cannot, however be use it in calculations with some of Excel's functions - such as SUM and AVERAGE.

Steps to Formatting a Cell for Text

As mentioned, it is important to format the cell for text data before you enter the number - otherwise it will be affected by the current cell formatting.

  1. Click on the cell or select a range of cells that you want to convert to text format

  2. Click on the Home tab of the ribbon

  3. Click on the down arrow next to the Number Format box as seen in the image above to open the drop down menu of format options

  4. Scroll to the bottom of the menu and click on the Text option

  5. The cell or cells will now be formatted for text

When you enter the number into the cell, it should align on the left side of the cell as seen in cell D3 in the image above.

When numbers are entered into cells formatted with the general format or number format, the data will be right aligned.

©2014 About.com. All rights reserved.