Microsoft KB Archive/75945

= How to Convert Text to Numbers in Excel =

Article ID: 75945

Article Last Modified on 11/16/2006

-

APPLIES TO


 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0c
 * Microsoft Excel 4.0c
 * Microsoft Excel 3.0a
 * Microsoft Excel 2.10d
 * Microsoft Excel 2.1 Standard Edition
 * Microsoft Excel 2.01
 * Microsoft Excel 2.0 Standard Edition
 * Microsoft Excel 5.0 Standard Edition

-



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.
 * 1) With the same range selected, click Copy on the Edit menu.
 * 2) 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.
 * 3) 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.

