Use Excel's VALUE Function to Convert Text to Numbers

Convert Text Data to Numbers with the VALUE Function in Excel

The VALUE function in Excel can be used to convert numbers that have been entered as text data into numeric values so that they may be used in calculations.

Normally, Excel automatically converts problem data of this sort to numbers, so the VALUE function is not required.

However, if the data is not in a format that Excel recognizes, the data can be left as text, and, if this situation occurs, certain functions, such as SUM or AVERAGE, will ignore the data in these cells and calculation errors can occur.

SUM and AVERAGE and Text Data

For example, in row five in the image above, the SUM function is used to total the data in rows three and four in both columns A and B with the following results:

• The data in cells A3 and A4 has been entered as text so the SUM function in cell A5 ignores this data and returns a result of zero.
• In cells B3 and B4, the VALUE function has been used to covert the data in A3 and A4 into numbers. The SUM function in cell B5 returns a result of 55 (30 + 25).

The Default Alignment of Data in Excel

By default text data aligns to the left in a cell and numbers - including dates - on the right.

In the example, the data in A3 and A4 align on the left side of the cell because it has been entered as text.

In cells B2 and B3, the data has been converted to number data using the VALUE function 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:

Text - (required) the data to be converted to a number. The argument can contain:

1. the actual data enclosed in quotation marks - row 2 of the example above;
2. a cell reference to the location of the text data in the worksheet. - row 3 of the example.

#VALUE! Error

If the data entered as the Text argument cannot be interpreted as a number, Excel returns the #VALUE! error as shown in row nine of the example.

Example: Convert Text to Numbers with the VALUE Function

Listed below are the steps used to enter the VALUE function B3 in the example above using the function's dialog box.

Alternatively, the complete function = VALUE ( B3 ) can be typed manually into the worksheet cell.

Converting the Text Data to Numbers with the VALUE Function

1. Click on cell B3 to make it the active cell;
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 A3 in the spreadsheet.
7. Click OK to complete the function and return to the worksheet
8. The number 30 should appear in cell B3 aligned on the right side of the cell indicating it is now a value that can be used in calculations.
9. When you click on cell E1 the complete function = VALUE ( B3 ) appears in the formula bar above the worksheet.

Converting Dates and Times

The VALUE function can also be used to convert dates and times to numbers.

Although dates and times are stored as numbers in Excel and there is no need to convert them before using them in calculations, changing the data's format can make it easier to understand the result.

Excel stores dates and times as sequential numbers or serial numbers. Each day the number increases by one. Partial days are entered as fractions of a day - such as 0.5 for half a day (12 hours) as shown in row 8 above.