Excel – Examples and guidance for VALUE function- a function for converting string to number

By Laura

To convert a string to a number, Excel supports you a function - VALUE function. Normally, you do not need to use Value function since Excel will automatically convert text to numbers, however, in some cases, VALUE function will be combined with other functions to give users desired result.

To use VALUE function effectively, you must understand its syntax, as well as its function to be able to combine and use this function with other functions in Excel.

Examples and guidance for VALUE function

The syntax: VALUE (text)

In which: text is the string containing numbers, it can be date, time or constants. Text must be enclosed in double quotes ("") or reference to a cell containing the text you want to convert.

For date, Excel starts counting from 1900 to the date in the function. For time, Excel willdivide24 by hours to get the result.

Function: convert a character string containing numbers to numeric values.

Example 1:

= VALUE ("100") = 100

= VALUE ("$ 100,001") = 100001

= VALUE ("30/01/1900") = 30

= VALUE ("6:00") = 0:25

Example 2: with the following data table:

The column of number is the code of employee. We will filter 2 last numbers of the employee code, so how to do it?

We will cut the string by RIGHT function to cut the last 2 numbers. The formula is = RIGHT (C5, 2)

Now, when using RIGHT function, you will get the two last numbers of the string of employee code. If we want to collect the raw value as"1" instead of "01", we will use VALUE function.

Formula: = VALUE (RIGHT (A4, 2))

You just need to drag your mouse to copy the formula to the remaining cells in order to get the result.

VALUE function converts string to number. Normally, you do not need to use VALUE function often in Excel. This function is provided for easy compatibility with other spreadsheet programs.

Related Articles:
Excel - LEFT function, for cutting left string
Excel - How to using the SUM function
Excel - PRODUCT function for multiplying
Dmax function in Excel – Take smallest value in row or column
Excel – AVERAGEIF function, calculating average with conditions
Excel – FREQUENCY function, counting frequently-appeared values




value function in excel


some string conversion function


the function value excel 2003 excel 2007 value function

, the function value excel 2010 excel 2013 function value

Related software

Latest update

Copyright BETDOWNLOAD.COM © 2014 - All rights reserved