1. Technology
You can opt-out at any time. Please refer to our privacy policy for contact information.

Convert Text to Numbers in Excel

By

Excel VALUE Function
Convert Text to Numbers in Excel

Convert Text to Numbers in Excel

© Ted French

Convert Text to Numbers with the VALUE Function

Sometimes when you import data into Excel from another program or from the Internet, Excel mixes up its data types and sees numerical data as text data.

When this situation occurs, certain functions, such as SUM or AVERAGE, ignore the data in these cells and you end up with calculation errors.

In this tutorial we will use the VALUE function to convert numbers entered as text data to values and then add them up using the SUM function.

The Default Alignment of Data in Excel

The alignment of data in a cell is one way to identify whether data has been entered as text or values. By default text data aligns to the left in a cell and values on the right.

In the image above, the numbers in D1 and D2 align on the left side of the cell because they have been entered as text data.

In cells E1 and E2 the data has been converted to values and therefore aligns to the right.

The VALUE Function's Syntax and Arguments

A function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments.

The syntax for the VALUE function is:

= VALUE ( Text )

Text - the data you want to convert. The argument can contain the actual data enclosed in quotation marks or it can be a cell reference to the location of the text data in the worksheet.

Example: Convert Text to Numbers with the VALUE Function

For help with this example, see the image above.

Entering Numbers as Text

  1. Enter the data into following cells - note that typing an apostrophe ( ' ) in front of a number turns it into text data.
  2.  
    Cell    Data 
     D1  -  '10
     D2  -  '20
     
  3. Type =SUM(D1 : D2) into cell D3 and press the Enter key on the keyboard.

  4. An answer of zero should appear in cell D3. Since the data in cells D1 and D2 is text data it is ignored by the SUM function.

Converting the Text Data to Numbers with the VALUE Function

  1. Click on cell E1 in the worksheet.

  2. Click on the Formulas tab of the ribbon menu.

  3. Choose Text from the ribbon to open the function drop down list.

  4. Click on VALUE in the list to bring up the function's dialog box.

  5. In the dialog box, click on the Text line.

  6. Click on cell D1 in the spreadsheet.

  7. Click OK.

  8. The number 10 should appear in cell E1. It should be aligned on the right side of the cell indicating it is now a value that can be used in calculations.

  9. Use the fill handle to copy the VALUE function in cell E1 to cell E2.

  10. The number 20 should appear in cell E2 and be aligned on the right side of the cell.

  11. Type =SUM(E1 : E2) into cell E3 and press the Enter key on the keyboard.

  12. An answer of 30 should appear in cell E3.

  13. Since the data in cells E1 and E2 has been converted to numbers they are now included in the SUM function's calculations.

  14. When you click on cell E1 the complete function = VALUE ( D1 ) appears in the formula bar above the worksheet.

©2014 About.com. All rights reserved.