Three ways to convert numbers to text

Important notice for users of Office 2003    To continue receiving security updates for Office, make sure you're running Office 2003 Service Pack 3 (SP3). The support for Office 2003 ends April 8, 2014. If you’re running Office 2003 after support ends, to receive all important security updates for Office, you need to upgrade to a later version such as Office 365 or Office 2013. For more information, see Support is ending for Office 2003.

Applies to
Microsoft Office Excel 2003

Mr. Excel logo This article was adapted from MrExcel.com. Visit the MrExcel.com Web site for more tips and information.

Is there a way to easily format existing numbers as text? Here are three answers to this commonly-asked question.

Use the Format Cells dialog box

Let's say you have a column of numbers in a spreadsheet. You could format the column as text using the Format > Cells > Number > Text command.

For more information, see Format numbers as text.

Use the TEXT function

Another approach is to use the TEXT function, which converts a value to text in a specific number format. For this example, let's assume that you have numbers in cells A2:A100. To convert them to text, you could do the following.

  1. Insert a temporary blank column B.
  2. In cell B2, enter this formula:

=TEXT(A2,"0")

  1. Fill the formula in B2 down to B3:B100.
  2. You need to change the formulas to values in order to have them become text. Highlight cells B2:B100.
  3. Use Ctrl+C to copy, then click Edit > Paste Special > Values > OK.

The entries in column B will now be text versions of the numbers in column A.

  1. Copy column B back into column A.
  2. Delete the temporary column B.

The key to this technique is the =TEXT() function. The second parameter in this function describes how the number should be formatted before being converted to text. You may need to adjust this based on your numbers. For example:

  • The result of =TEXT(123.25,"0") will be 123.
  • The result of =TEXT(123.25,"0.0") will be 123.3.
  • The result of =TEXT(123.25,"0.00") will be 123.25.
  • To keep only the decimals that were entered, use =TEXT(A2,"General").

This function is also great for converting dates to formatted dates. If you have 5/29/2003 in a cell, then using =TEXT(A2,"d mmmm, yyyy") will give you 29 May 2003.

For more information, see the TEXT Help topic.

Use the Convert Text to Columns Wizard

A third method is to highlight the column of numbers and use the Data > Text to Columns command. In Page 1 of the wizard, choose the appropriate file type (this will probably be Delimited). In Page 2, remove any column dividers that may have shown up to keep the data in one column. In Page 3, click Text under Column data format to indicate that this column is text.

 Tip   After you finish the wizard, you may see small triangles, called error indicators, in one or more cells. If you find these distracting, you can select the column and remove all of them by removing just one. For more information, see Hide error values and error indicators in cells.

 
 
Applies to:
Excel 2003