How to convert text to numbers in Excel 2002 and in Excel 2003 (291047)

SUMMARY

This step-by-step article describes how to convert cells that contain text to cells that contain numbers. convert cells that contain text to cells that contain numbers.

When you import a file that was created in another program (such as dBASE or Lotus 1-2-3) or was downloaded from a mainframe, Microsoft Excel may recognize some numbers as text.

This causes 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 that you want to convert.

back to the top

Converting Text to Numbers

To convert text to numbers in a worksheet, use any of the methods described later in this article (methods 1 through 7).

Note Each of these methods assumes that you have already changed any cell number formatting in the cells to General. To do this, follow these steps:

1. On the Format menu, click Cells.
2. On the Number tab, click General under Category, and then click OK.

back to the top

Method 1: Use the Error Button

If the cells in which numbers are displayed as text contain an error indicator in the upper-left corner, follow these steps:

1. Click the cell that contains the error indicator.
2. Click the error button next to the cell, and then click Convert to Number on the shortcut menu.

back to the top

Method 2: Retype the Values in the Cells

To retype the values in the cells, follow these steps:

1. On the Format menu, click Cells, and then click the Number tab to change the number format of the cells.
2. Retype the numbers.

back to the top

Method 3: Edit Directly in the Cell

To edit directly in the cells, follow these steps:

1. On the Tools menu, click Options.
2. On the Edit tab, verify that the Edit directly in cell check box is selected. Click OK.
3. Double-click the cell you want to format, and then press ENTER.

back to the top

Method 4: Use the Paste Special Command

To use the Paste Special command, follow these steps:

1. In any blank cell, type the value 1.
2. Select the cell in which you typed 1, and then click Copy on the Edit menu.
3. Select the cells with the values that you want to convert to numbers.
4. On the Edit menu, click Paste Special.
5. Under Operation, click Multiply. Under Paste, click Values, and then click OK.

back to the top

Method 5: Remove Hidden Characters and Spaces

This method works best if the data is arranged in a single column or row. This method uses the TRIM function and the CLEAN function to remove extra spaces and nonprinting characters that may be imported with the file. The following example assumes that the data is in column A and starts in row 1 ($A$1). To use this example, follow these steps:

1. Insert a column to the right of column A. To do this, select column B, and then click Columns on the Insert menu.
2. In the first cell of the inserted column (B1), type the following:

   $B$1:   =VALUE(TRIM(CLEAN(A1)))
   					

3. In column B, select all the cells to the right of the cells that contain data in column A.
4. On the Edit menu, point to Fill, and then click Down.
   
   The new column 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 then click Paste Special on the Edit menu. Under Paste, click Values, and then click OK to paste the converted values back on top of column A.
7. Delete column B by selecting the column and clicking Delete on the Edit menu.

The text that was in column A is in a number format.

Note This method may not remove all nonprinting whitespace characters. For example, the whitespace character Chr$(160) will not be removed.

back to the top

Method 6: Use a Visual Basic for Applications Procedure

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. Create a Visual Basic for Applications macro to reenter the numbers in the selected cell or range of cells. To do this, follow these steps:

1. Select the cells that you want to convert.
2. If you have not already done so, click Cells on the Format menu, and then click the Number tab to change the number format of the cells.
3. Press ALT+F11 to start the Visual Basic Editor.
4. On the Insert menu, click Module.
5. Type the following macro code in the new module:

   Sub Enter_Values()
       For Each xCell In Selection
           xCell.Value = xCell.Value
       Next xCell
   End Sub
   					

6. Press ALT+F11 to switch to Excel.
7. Select the cells that you want to convert, if they are not already selected.
8. On the Tools menu, point to Macro, and then click Macros. In the Macro name list, click Enter_Values. Click Run.

back to the top

Method 7: Use the Text to Columns Command

This method works best if the data is arranged in a single column. The following example assumes that the data is in column A and starts in row 1 ($A$1). To use this example, follow these steps:

1. Select one column of cells that contain the text.
2. On the Data menu, click Text to Columns.
3. Under Original data type, click Delimited, and click Next.
4. Under Delimiters, click to select the Tab check box, and click Next.
5. Under Column data format, click General.
6. Click Advanced and make any appropriate settings for the Decimal separator and Thousands separator. Click OK.
7. Click Finish.

The text is converted to numbers.

back to the top