Sometimes when data is imported or copied into an Excel spreadsheet the numbers are converted to labels. This means that they cannot be used in formulas or functions.
To change them back into values, so that they can be used in calculations, use the VALUE function.
The syntax for the VALUE function is:
= VALUE ( Text )
Text - the data you want to change. This can be a cell reference or text enclosed in quotation marks.
Example Using Excel's VALUE Function:
Note: For help with this example, see the image to the right.
- Enter the following data into cells D1 to D4: '24, '34, '44, '54. - note that typing an apostrophe ( ' ) in front of a number turns it into a text label.
- Click on cell E1 in the spreadsheet - this is where the function will be located.
- 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 text label 24 is converted to a number that can be used in calculations.
Note that the text label in D1 aligns to the left side of the cell while the value in cell E1 aligns to the right. - Use the fill handle to copy the function in cell E1 down to cells E2, E3, and E4.
- Cells E2 to E4 will also be converted to values.
- When you click on cell E1 the complete function = VALUE ( D1 ) appears in the formula bar above the worksheet.
Related Articles
