Winsby Group LLC Corporate Logo

Change UPC Numeric to Text in Excel

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.

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.

Revised: 2011-01-28
Next