How to Use VALUE Function in Excel (5 Common Examples)

Excel provides several text functions to perform your desired text-related tasks easily and swiftly. One of them is a text function called: VALUE. Today we are going to show you how to use the Excel VALUE function. For this session, we are using Excel 2019, feel free to use yours (at least version 2003).

Overview - Excel VALUE Function

Practice Workbook

You are welcome to download the practice workbook from the link below.

Excel VALUE Function

1. Basics of VALUE

The VALUE function is categorized under the TEXT functions in Excel. It converts a given text string that represents a number into a numeric value.

Syntax - Excel VALUE Function

Summary

Converts a text string that represents a number to a number.

Syntax

VALUE(text)

Arguments

text – The text value to convert into a number.

Versions

Workable from Excel 2003.

2. Uses of VALUE

Depending on the circumstances you need to use the VALUE function. Here we are listing a few common uses of VALUE.

I. Text to Number

By mistake (sometimes on purpose) a number can be formatted as a text value. We can hardly perform the generic numeric operations then. So, we need to modify the formation.

Text to number data - Excel VALUE Function

Here we have listed a few numeric values that are formatted as text. To convert the value into a number all we need to provide the value within VALUE.

=VALUE(B4)

VALUE Formula to convert text - Excel VALUE Function

B4 is the cell reference of the value. The formula will return the value in number format.

Formula result text to number - Excel VALUE Function

Here 100 from the Output column is in number format. Convert the other values as well using the VALUE function.

Change all data text to number - Excel VALUE Function

II. Currency to Number

We can convert the currency into a plain number. For example, we have listed a few currency values.

Currency to number data - Excel VALUE Function

Now let’s convert these into plain numeric values. The formula will be the following one

=VALUE(B4)

VALUE formula currency to text - Excel VALUE Function

Here we have inserted the value from inside the VALUE function. This will convert the currency into a number.

Formula result Currency to number - Excel VALUE Function

No matter which currency it is, the VALUE function will convert the currency into a numeric value.

Convert all data Currency to number - Excel VALUE Function

III. Date – Time to Number

The date and time value can be converted into a number format using VALUE. Here we have listed a few date and time values in different formats.

Date - Time to Number data - Excel VALUE Function

Let’s convert these values into the number format.

Insert the cell reference that contains the value in the VALUE function.

Change time to number result - Excel VALUE Function

Here we have provided the time value 7:30 PM within VALUE using its cell reference and the formula returned the result.

Now let’s use the cell reference of 19:31:45, you have understood that it’s also a time format.

Change time to number result 2 - Excel VALUE Function

We have found the numeric value for the time value.

Similarly, we can convert the date value into a numeric value. Use the cell reference for the date inside the VALUE function.

Date to Number result - Excel VALUE Function

Here we have found the number for the date. Excel starts date counting from 01/01/1990, and numbers it as 1. Then 02/01/1990, numbers as 2 and so on. Thus the 18 September 2021 becomes 44457 and the formula returns us that value.

No matter how the date is stored (definitely a recognized format) the VALUE function will convert that into the number format.

Date to Number result 2 - Excel VALUE Function

IV. Use VALUE with LEFT

Sometimes you may find data with a combination of numbers and text strings. To retrieve the number and make sure that the value is in the number format we need to use another helping function along with VALUE.

VALUE - LEFT data - Excel VALUE Function

Here we have listed several items along with the quantity at the start of the string. We will fetch the quantity value.

Since the numeric values are on the left of the string, we will use the LEFT function.

This function retrieves a specific number of characters from the left of a string. To know about it, visit the article: LEFT.

Now our formula will be

=VALUE(LEFT(B4,2))

VALUE - LEFT formula result - Excel VALUE Function

We have found the desired result using this formula.

Let’s help you to understand the mechanism. First of all, the LEFT function extracts the 2 characters from the string, and then VALUE converts that into a number.

Do the same for the rest of the values.

VALUE - LEFT formula result all - Excel VALUE Function

V. An Advanced Use of VALUE Function

Let’s observe an advanced use of the VALUE function. Don’t worry, compared to the earlier examples it’s going to be a bit complex, but the operation itself is a very simple one.

HR Remarks data - Excel VALUE Function

Here we have a dataset of a few employees with their entry and exit time. The duration of their work time is found by subtracting the exit and entry time.

Let’s say that HR wants to check whether the employees are working the entire 8 hours or anything less than that. To check that we need to use the IF function. Check the IF article, in case you want to know about the function.

The formula is going to be the following one

=IF(E4>=VALUE("8:00"),"Complete","Short")

IF- VALUE formula - Excel VALUE Function

Here we have inserted “8:00” within the VALUE  and converted it then checked the logic. When the duration value (E4) is greater or equal to 8:00 the formula will return “Complete”, otherwise “Short”.

Formula result Complete - Excel VALUE Function

Here the duration is greater than 8 hours so the output is “Complete”. When the duration is less than 8 hours, the output will be “Short”.

Formula result Short

Let’s write the formula for the rest of the values and find the result.

IF- VALUE Formula result

Quick Notes

  1. Apart from the cell reference, we can directly insert the values within VALUE.

Direct input

It will return the value as a number.

Direct input result

  1. Don’t hesitate to insert a negative numeric value (less than 0) within the VALUE

Negative number input

You will find the negative number.

Negative number input result

  1. There are several date-time functions (NOW, TODAY) in Excel. You can insert any of those within VALUE.

NOW in VALUE

Here we have inserted NOW in VALUE and it will convert the time to a number.

NOW in VALUE result

  1. If we use a text string,

Text string input

we will find the #VALUE error.

Text string result

  1. Just for your information, if we insert the text string without double quotes

Input text without double quote

then we will find the #NAME? error.

Input text without double quote result

Conclusion

That’s all for today. We have tried showing how you can use the VALUE function. You can use the function to convert numeric text into number format. Hope you will find this helpful.

Feel free to comment if anything seems difficult to understand. Let us know any of your VALUE function-related scenarios where you have stuck, we are ready to help.

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo