Here, we will learn how to use the Excel text functions: VALUE and NUMBERVALUE.
VALUE function
The Excel VALUE function converts the given text that appears in a recognized format into a numeric value. The text can be anything like a number, date or time. For example, =VALUE("123") returns 123 in numeric format. The function can be found in all the versions of Excel. VALUE takes just one argument, text, that is the text string that needs to be converted to a numeric value. Text string can be converted by either directly supplying the string to the function's argument (enclosing in double-quotes) or by supplying a cell reference. The function always returns the result in the numeric format. Supplying a text that Excel does not recognize, VALUE returns the #VALUE! error. For example, =VALUE("hello") returns #VALUE! If VALUE is given a text value with formatting, the function will remove the number formatting. For example, A5 (see image) contains the date 01-11-2024 and VALUE removes the formatting and returns the date serial number as numeric format. The formula in B4, copied down, is:
=VALUE(A4)
NUMBERVALUE function
The Excel NUMBERVALUE function converts the given number as text into a numeric value, using custom decimal and group separators. For example, =NUMBERVALUE("1.234,5",",",".") returns 1234.5 in numeric format. The function can be found in Excel 365 - Excel 2013. NUMBERVALUE takes the arguments like this: text, [decimal_separator], [group_separator], of which text is required. Text accepts the text that needs to be converted to a number. It is usually supplied as a cell reference. Decimal_separator accepts the character used as the decimal separator in the given text value. Group_separator accepts the character used to group by thousands in the given text value. Both decimal_separator and group_separator are supplied directly in the function enclosed in the double quotes. The function always returns the result in the numeric format. Supplying a text other than a number as text, NUMBERVALUE returns the #VALUE! error. For example, =NUMBERVALUE("a.bcd,e",",",".") returns #VALUE! The formula in G4, copied down, is:
=NUMBERVALUE(D4,E4,F4)
You have successfully learnt how to use the Excel text functions: VALUE and NUMBERVALUE.
