Excel Value Function

Ideally, Microsoft Excel recognizes the number values entered as string or text and automatically converts them into the numerical format. But sometimes, when we import the data from a different platform to Excel, data comes in an unrecognizable format, and the numeric data can be left as strings making computations impossible. In such conditions the right call will be to convert the values to numbers and therefore the inbuilt Value function can be a great help.

What is a Value Function?

"The Excel VALUE function converts the string data to numbers. It can identify numeric strings, dates and times and converts them to numbers".

To understand this better, we have taken a series of datasets wherein we have mentioned the total cost of different products in the text format. For instance, in the below image, the values in Column C are in a text format.

NOTE: The text or String values are always aligned towards the left side of any cell, whereas the numeric data type is aligned at the right corner.

Now, if we want to convert to number, we can use the following formula in cell D2:

=VALUE(C2)

As a result, the value is converted in a numeric format.

Although the VALUE function is not used much in Excel, it automatically converts text to numbers when required. But users were facing problems as this practice was incompatible with other spreadsheet applications. Therefore to combat this situation, Microsoft has introduced the VALUE function in Excel 2007 version, and it is available in all later versions.

Syntax

Parameters

Text (required): This parameter represents a text string confined in quotation marks or a reference to a cell containing the text you want to change to a number data type.

Return Value

The Value function returns a numerical data after converting the text string value to number.

Example

In most situations, Excel automatically converts the string values to numbers. However, you need to explicitly tell Excel to do so in many cases when it can't take the values as numbers. Let's cover some examples to check how this function works in Excel worksheets.

Example 1: Convert to Number

Question: We have given a series of the cost of different products. But since we have exported it from other software, it is in a text format. Using the VALUE function, convert it to a number.

Following are the steps to convert the given text values to number:

1. Start the formula with equals to "=", followed by the Value function.
   
2. In its parameters, pass the text that you want to convert to number. Here, we have referred the cell C2.
   
3. Press enter button. Once done, it will convert the text value to a number. The value in cell D2 is on the right side of the cell since the numeric values are always aligned towards the right. So it means the value in cell D2 is a number.
   
4. Drag the formula down the cells to replicate it. It will return the output for the given values. The following screenshot shows possible outputs:
   
5. You can even apply different operations with your VALUE function.

Example 2: Extract number from string values

Many times in Excel, we require to extract only some characters from the beginning of the given text, or sometimes we need to extract the characters towards the ends of the text. Typically, we use Excel's inbuilt LEFT, RIGHT and MID functions to achieve the characters based on their position.

The output received using these functions is always text, even if you are extracting numbers. Though Excel automatically converts the text to numbers, but in many critical cases, it treats the extracted number characters as text strings, not numbers.

For instance, in the below image, we try to add the extracted numbers using the SUM function. Because the extracted number characters are text values, therefore the SUM function is not able to add up the values and is returning 0 as output.

The best method to resolve the above problem is by using the value function and converting the text to number. Follow the below steps to quickly get it done!

1. Start the formula with equals to "=", followed by the Value function.
2. In its parameters, specify another function i.e., Left. To extract the three characters from the beginning from the given string and return the output value as a number, use the below formula:
   Formula Applied =VALUE(LEFT(B2,3)
   
3. Press enter button once done, it will convert the text value to number. Now, you can apply your operation.
   
4. Similarly, if you want to extract three characters from the middle of the given string, use the below formula:
   Formula Applied =VALUE(MID(B3,15,4))
   
5. Now, if you want to extract the last two characters from the string and want to convert it to numbers, use the below formula:
   Formula Applied =VALUE(RIGHT(B4,4))
   
6. The above formulas will not only extract the number digits but also convert the extracted text values to numbers. Now if you apply the SUM function, you will get the proper output.
   

Example 3: Convert text to dates and times using VALUE function

The Value function can convert data and time text strings into a serial number representing the date or/and time in the internal Excel system. Therefore, it helps to restore the given date into integer value and time into decimal value. Later, if you want the output to appear in date format, you can easily apply the Date function and change the number format to Date format for the formula cells.

Following are the steps to quickly convert the Date into integer value using the VALUE function:

1. Start the formula with equals to "=", followed by the Value function.
2. In its parameters, pass the date that you want to convert to number. Here, we have referred the cells B2.
   
3. Press enter button once done, it will convert the date to integer. Now, you can apply your operation.
   
4. Drag the formula down the cells to replicate it. As a result, it will return the integer values for all the given Date data.

The following screenshot shows possible outputs:

NOTE: After applying the operation to your integer value, you can convert it to date format using the DATEVALUE function.

Excel VALUE function returns #VALUE error

While working with the Value function, it often returns a #VALUE error. It mainly occurs when the specified string is in a format not recognized by Microsoft Excel.

For instance, in the below image, we are converting the currencies into number format. But since in the source string the symbol '$' is present, that is not recognized by Excel. Therefore it will throw the #VALUE error.

To catch the unwanted errors in the strings, we can incorporate the IFERROR function along with the VALUE function.

=IFERROR(VALUE(B2), "ERROR, use only numbers")

Things to Remember

1. The VALUE formula returns the #VALUE error. This happens mostly when EXCEL doesn't recognize the data, and even if you apply the VALUE function, it leaves the number as text. Now, if you use some mathematical functions such as SUM or SUBTRACT, it ignores the data in these cells and calculation errors occur.
2. Though sometimes it becomes confusing to track whether the number is actually converted to text or still a text value. The simple way to verify the data type is to check the alignment of the text. In Excel, the Text values are aligned on the left side of a cell, whereas the number values are aligned on the right side of the cell.
3. The VALUE function converts text string that is present in a recognized format into a number. For example: It can convert text, date, or time format, currency, percentage in a numeric value.
4. Normally, VALUE function is not used much in Excel, it automatically converts text to numbers when required.