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

Excel VALUE Function

One of Excel's TEXT functions

By

Excel VALUE Function

Excel VALUE Function

© Ted French

Sometimes when data is imported or copied into an Excel spreadsheet the numbers are converted to text. This means that they cannot be used in formulas or functions.

To change them back into numbers, 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

  1. 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.

  2. Click on cell E1 in the spreadsheet - this is where the function will be located.

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

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

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

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

  7. Click on cell D1 in the spreadsheet.

  8. Click OK.

  9. 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.

  10. Use the fill handle to copy the function in cell E1 down to cells E2, E3, and E4.

  11. Cells E2 to E4 will also be converted to values.

  12. 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.