Converting Text to Numbers in Excel

ID: Q75945

The information in this article applies to:

Microsoft Excel for Windows, versions 2.x, 3.x, 4.x, 5.0, 5.0c
Microsoft Excel for Windows NT, version 5.0
Microsoft Excel for Windows 95, version 7.0

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

In cell B1, enter the value 1. Select cell B1, and click Copy on the Edit menu.

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:

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).

In the first cell of the inserted column (B1), enter the formula =VALUE(A1).

In column B, select all the cells to the right of the cells containing data in column A.

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.

With the same range selected, click Copy on the Edit menu.

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.

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.

Additional query words: 2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0 3.00 4.0 4.00


Keywords          : xlui xlformat xlformula 
Version           : 2.x 3.00 4.00 5.00 5.00c 7.00
Platform          : WINDOWS 
Issue type        :

Last Reviewed: March 23, 1999