Converting Text to Numbers in Excel

Last reviewed: December 19, 1996

Article 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)" (without the quotation marks).

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.

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.