How to Convert Text to Numbers in Excel (75945)


The information in this article applies to:

  • Microsoft Excel for Windows 95
  • Microsoft Excel for Windows 5.0c
  • Microsoft Excel for Windows 4.0c
  • Microsoft Excel for Windows 3.0a
  • Microsoft Excel for Windows 2.10d
  • Microsoft Excel for Windows 2.1
  • Microsoft Excel for Windows 2.01
  • Microsoft Excel for Windows 2.0
  • Microsoft Excel for Windows NT 5.0
This article was previously published under Q75945
For a Microsoft Excel 2002 version of this article, see 291047.
For a Microsoft Excel 2000 version of this article, see 181298.

SUMMARY

When you import a file in Microsoft Excel that has been created in another program (such as dBASE or Lotus 1-2-3) or that has been downloaded from a mainframe, Microsoft Excel may recognize some numbers as text. This will cause functions such as SUM() and AVERAGE() to ignore the values in these cells. These text strings may contain actual text in addition to the numbers you want to convert.

MORE INFORMATION

Consider the following example: 

   A1: ='123
   A2: ='234
   A3: ='345
   A4: ='456
   A5: ='567


To convert these text strings to numbers, do the following:

Method 1

  1. In cell B1, enter the value 1. Select cell B1, and click Copy on the Edit menu.
  2. Select cells A1:A5. On the Edit menu, click Paste Special. Click the Multiply option, and then click OK.

Method 2

The second technique works best if the data is arranged in a single column or row. The following example assumes that the data is in column A:

  1. Insert a column to the right of column A by selecting column B and clicking Columns on the Insert menu (version 5.0 and later) or click Insert on the Edit Menu (earlier versions).
  2. In the first cell of the inserted column (B1), enter the formula =VALUE(A1).
  3. In column B, select all the cells to the right of the cells containing data in column A.
  4. On the Edit menu, click Fill, and then click Down (version 5.0 and later) or on the Edit menu, click Fill Down (versions earlier than 5.0).

    The new column now contains the values of the text in column A.
  5. With the same range selected, click Copy on the Edit menu.
  6. Select cell A1, and click Paste Special on the Edit menu. Under Paste, select the Values option, and click OK to paste the converted values back on top of column A.
  7. Delete column B by selecting the column and click Delete on the Edit menu.
The text that was in column A is now in a number format.

REFERENCES

"Microsoft Excel Function Reference," version 4.0, page 444.
"Microsoft Excel User's Guide, Book 1," version 4.0, page 188-197.
"Microsoft Excel Function Reference," version 3.0, page 243.
"Microsoft Excel User's Guide," version 3.0, pages 156-165.
"Microsoft Excel Functions and Macros," versions 2.x, pages 122-123.
"Microsoft Excel Reference Guide," versions 2.x, pages 236-237.
Modification Type:MinorLast Reviewed:8/15/2005
Keywords:KB75945
©2004 Microsoft Corporation. All rights reserved.