Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Use VALUE Function in Excel (5 Ideal 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


Download Practice Workbook

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


5 Suitable Examples of VALUE Function in Excel

Before diving into the examples, let’s have a detailed look at the 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.

Version:

Workable from Excel 2003.

Now, let’s have a look at the examples.


1. Change Text Format 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.

Steps:

  • Go to D5 and write down the following formula
=VALUE(B5)

Text to number Excel VALUE Function

  • Then, press ENTER to get the output.

Text to number Excel VALUE Function

  • After that, use Fill Handle to AutoFill up to D7.

Text to number Excel VALUE Function


2. Convert Currency to Number

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

Steps:

  • Go to D5 and write down the following formula
=VALUE(B5)

  • Then, press ENTER to get the output.

  • After that, use Fill Handle to AutoFill up to D7.


3. Alter 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. Let’s convert these values into the number format.

Steps:

  • Go to D5 and write down the following formula
=VALUE(B5)

Date to number Excel VALUE Function

  • Then, press ENTER to get the output.

Date to number Excel VALUE Function

  • After that, use Fill Handle to AutoFill up to D7.

Date to number Excel VALUE Function

Note

Excel has inbuilt numeric values for times and dates. So, we will get those numeric values as the outputs upon applying the VALUE function. For example, the numeric value for 7:30 PM is 0.8125.


4. Combine VALUE with LEFT Functions

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.

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

Steps:

  • Since the numeric values are on the left of the string, we will use the LEFT 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(B5,2))

Formula Explanation

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.

  • We have found the desired result using this formula.

  • Do the same for the rest of the values.


5. Merge VALUE and IF Functions

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.

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.

Combination of functions Excel VALUE Function

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.

Steps:

  • Go to F5 and write down the following formula
=IF(E5>=VALUE("8:00"),"Complete","Short")

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

Combination of functions 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”. Let’s write the formula for the rest of the values and find the result.

Combination of functions Excel VALUE Function

Read More: How to Fix Convert to Number Error in Excel (6 Methods)


Quick Notes

  • Apart from the cell reference, we can directly insert the values within VALUE. It will return the value as a number.
  • Don’t hesitate to insert a negative numeric value (less than 0) within the VALUE. You will find the negative number.
  • There are several date-time functions (NOW, TODAY) in Excel. You can insert any of those within VALUE.
  • If we use a text string, we will find the #VALUE error.
  • Just for your information, if we insert the text string without double quotes then we will find the #NAME? error.

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.

And obviously, 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

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