Four ways to convert text to numbers in Excel

Excel 2003 may recognize some of these numbers as text when you import files created in another program, such as DBASE or Lotus 1-2-3, or when you import files that are downloaded from a mainframe.

This causes some functions, such as SUM and AVERAGE, to ignore the values in those cells. In addition to the numbers you want to convert, these text strings may also contain real text characters. So how do you convert text to numbers?

To convert text from a worksheet to a number, you can use any of the methods that are described in this article (methods 1 through 7).

Note Each method assumes that you have changed the number format of any cell in the cell to general. To do this, follow these steps:

1. On the Format menu, click Cells.

2. On the Number tab, click General under Categorize, and then click OK.

　　Method 1: Use the error button

If there is an error indicator in the upper-left corner of the cell that displays the number as text, follow these steps.

Note This method assumes that the background error checking feature is turned on in Excel. If you have not turned on the error checking feature, you must open it to use this method.

1. On the Tools menu, click Options.

2. Click the Error Checking tab.

3. Click to select the Enable background error checking check box.

4. Click to select the "store numbers as text" check box.

5. Click to select the other rules you want to open.

6. Click OK.

7. Click the cell that contains the error indicator.

8. Click the Error button next to the cell, and then click Convert to Number.

　　Method 2: Retype the value in the cell

To retype a value in a cell, follow these steps:

1. On the Format menu, click Cells, and then click the Number tab to change the number format of the cell.

2. Retype the numbers.

　　Method 3: Edit directly in a cell

To edit directly within a cell, follow these steps:

1. On the Tools menu, click Options.

2. On the Edit tab, verify that the edit directly inside cells check box is selected.

Click OK.

3. Double-click the cell that you want to format, and then press Enter.

　　Method 4: Use the Paste-selective command

To use the paste Selective command, follow these steps:

1. In any empty cell, type a value of 1.

2. Select the cell in which you typed 1, and then click Copy on the Edit menu.

3. Select the cell in which you want to convert the value to a number.

4. On the Edit menu, click Paste Selective.

5. Under Actions, click Multiply.

Under Paste, click Values, and then click OK.

6. Delete the value 1 you typed in the blank cell.

Note that when some accounting programs display negative values, the minus sign (–) is placed to the right of the value. To convert a text string to a value, you must return all the characters of the text string (except for the rightmost minus character) and multiply the result by –1. For example, if the value in cell A2 is 156–, the following formula can convert text to a value –156:

=left (A2,len (A2)-1) *-1