Excel2010 How do I convert text to numbers?

Many people have found that Excel 2010 may recognize some numbers as text after importing other data. 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 does Excel2010 convert text to numbers? To convert text in a worksheet to a number, you can use any of the methods that are described in this article.

Excel2010

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

Method 5: Remove hidden characters and spaces

This method works best if the data is arranged in a single column or row. It uses the TRIM and clean functions to remove extra spaces and nonprinting characters that are imported with the file. The following example assumes that the data is in column A and the first behavior line 1th ($A $). To remove hidden characters and spaces, follow these steps:

1. Insert a column to the right of column A.

To do this, click B, and then on the Insert menu, click Columns.

2. Type the following in the first cell (B1) of the inserted column:

$B $: =value (TRIM (A1))

3. In column B, select all cells to the right of the cell that contains the data in column A.

4. On the Edit menu, point to Fill, and then click Down.

The new column contains the value of the text in column A.

5. After selecting the same area, click Copy on the Edit menu.

6. Click cell A1, and then on the Edit menu, click Paste Selective.

Under Paste, click Values, and then click OK to paste the converted values back to the top of column A.

7. Delete Column B.

To do this, click the column, and then click Delete on the Edit menu.

The text in column A is now a number format.

Note This method may not remove all nonprinting white-space characters. For example, the whitespace character chr$ (160) is not deleted.

Method 6: Use the Microsoft Visual Basic for Applications (VBA) procedure

Create a VBA macro to re-enter the numbers in the selected cell or selected range of cells. The specific steps are as follows:

1. Select the cells you want to convert.

2. If you haven't changed the number format for the cell, on the Format menu, click Cells, and then click the Number tab to change the number format for the cell.

3. Press the ALT+F11 key combination to start the Visual Basic editor.

4. On the Insert menu, click Modules.

5. Type the following macro code into 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. If the cell you want to convert is not already selected, select it.

8. On the Tools menu, point to Macros, and then click Macros.

In the Macro Name list, click Enter_Values. Click "Run".

Method 7: Use the text 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 the first behavior line 1th ($A $). To use this example, follow these steps:

1. Select a column of cells that contains text.

2. On the Data menu, click Columns.

3. Under Original data type, click Separator Symbol, and then click Next.

4. Under separator symbols, click to select the TAB key check box, and then click Next.

5. Under Column data format, click General.

6. Click Advanced to set the 10-bit separator and thousands separator accordingly. Click OK.

7. Click Finish.

The text is now converted to numbers.