Article ID: 291047 - View products that this article applies to.
This article was previously published under Q291047
Expand all | Collapse all

On This Page

SUMMARY

This step-by-step article describes how to 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.

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. In Excel 2003, on the Format menu, click Cells. In Excel 2007 or later, click the arrow to the bottom right of the Font section on the ribbon to call up the Format Cells dialog box.
  2. On the Number tab, click General under Category, and then click OK.

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.

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.


Method 3: Edit Directly in the Cell

To edit directly in the cells, follow these steps:

Excel 2003

  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.
  4. Click the error button next to the cell, and then click Convert to Number on the shortcut menu

Excel 2007

  1. Click the Office Button, Click Excel Options, Click Advanced.
  2. In the Editing Options, verify that the AllowEditing directly in cells check box is selected. Click OK.
  3. Double-click the cell you want to format, and then press ENTER.
  4. Click the error button next to the cell, and then click Convert to Number on the shortcut menu

Excel 2010


  1. Click the File tab, Click Excel Options, Click Advanced.
  2. In the Editing Options, verify that the AllowEditing directly in cells check box is selected. Click OK.
  3. Double-click the cell you want to format, and then press ENTER.
  4. Click the error button next to the cell, and then click Convert to Number on the shortcut menu

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. Make sure the cell you typed 1 in is formatted as a number.
  3. Select the cell in which you typed 1, and then right click and choose Copy.
  4. Select the cells with the values that you want to convert to numbers.
  5. Right click and choose Paste Special.
  6. Under Operation, click Multiply and then click OK.

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, right-click Column B's header, and then click Insert.
  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. (In Excel 2007 or later, click the Copy icon on the ribbon.)
  6. Select cell A1. In Excel 2003 or earlier, click Paste Special on the Edit menu. In 2007 or later, click the down arrow under Paste in the ribbon, and click Paste Special. 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.

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. Press Alt + F11 to start the Visual Basic Editor.

2. On the Insert Menu, Click Module.

3. Type the following code in the new module:

Sub Enter_Values()

 For Each xCell In Selection

 Selection.NumberFormat = "0.00" 'Note: The "0.00" determines the number of decimal places. 

 xCell.Value = xCell.Value

 Next xCell

End Sub


4. Press Alt + F11 to switch to Excel.

5. Select the cells that you want to convert, if they are not already selected.

6. In Excel 2003, on the Tools menu, point to Macro, and then click Macros. In the Macro Name list, click Enter_Values, click Run.

In Excel 2007 or later, click the Developer menu and click Macros. In the Macro Name list, click Enter_Values, click Run.

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 or ribbon, 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.
 NOTE: The formatting will still show text but the type has changed to number so formulas will recognize it as a number.

REFERENCES

For more information about cell formatting, click Microsoft Excel Help on the Help menu, type format cells in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.
The third-party products that this article discusses are manufactured by companies that are independent of Microsoft. Microsoft makes no warranty, implied or otherwise, regarding the performance or reliability of these products.

Properties

Article ID: 291047 - Last Review: February 17, 2012 - Revision: 10.0
APPLIES TO
  • Microsoft Office Excel 2003
  • Microsoft Office Excel 2007
  • Microsoft Excel 2010
Keywords: 
kbhowto kbconversion KB291047

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com