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.
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
The syntax for the VALUE function is:
= VALUE ( Text )
Example: Convert Text to Numbers with the VALUE FunctionFor help with this example, see the image above.
Entering Numbers as Text
- Enter the data into following cells - note that typing an apostrophe ( ' ) in front of a number turns it into text data.
- Type =SUM(D1 : D2) into cell D3 and press the Enter key on the keyboard.
- 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.
Cell Data D1 - '10 D2 - '20
Converting the Text Data to Numbers with the VALUE Function
- Click on cell E1 in the worksheet.
- Click on the Formulas tab of the ribbon menu.
- Choose Text from the ribbon to open the function drop down list.
- Click on VALUE in the list to bring up the function's dialog box.
- In the dialog box, click on the Text line.
- Click on cell D1 in the spreadsheet.
- Click OK.
- 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.
- Use the fill handle to copy the VALUE function in cell E1 to cell E2.
- The number 20 should appear in cell E2 and be aligned on the right side of the cell.
- Type =SUM(E1 : E2) into cell E3 and press the Enter key on the keyboard.
- An answer of 30 should appear in cell E3.
- Since the data in cells E1 and E2 has been converted to numbers they are now included in the SUM function's calculations.
- When you click on cell E1 the complete function = VALUE ( D1 ) appears in the formula bar above the worksheet.