MS Excel: How to use the NUMBERVALUE Function (WS)
This Excel tutorial explains how to use the Excel NUMBERVALUE function with syntax and examples.
Description
The Microsoft Excel NUMBERVALUE function converts a text value to a number by specifying the decimal and group separators.
The NUMBERVALUE function is a built-in function in Excel that is categorized as a String/Text Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the NUMBERVALUE function can be entered as part of a formula in a cell of a worksheet.
If you want to follow along with this tutorial, download the example spreadsheet.
Syntax
The syntax for the NUMBERVALUE function in Microsoft Excel is:
NUMBERVALUE( text, [decimal_separator], [group_separator] )
Parameters or Arguments
- text
- The text value that you wish to convert to number.
- decimal_separator
- Optional. It is the character used as the decimal separator in the text value.
- group_separator
- Optional. It is the character used as the group separator in the text value.
Returns
The NUMBERVALUE function returns a numeric value.
Note
- If any arguments are not valid, #VALUE! will be returned
- If the group separator appears after the decimal separater in the text value, #VALUE! will be returned
- If multiple decimal separators appear in the text value, #VALUE! will be returned
- If multiple characters are provided in the separator fields, only the first character will be used in each field
- If an empty text value is provided, a value of 0 will be returned
- If separator values are not provided, the current locale values are used
- Spaces in the text value are ignored
- Percent signs appearing at the end of the text value will affect the result. For example: =NUMBERVALUE("15%") will return the same result as entering =15% into a cell.
Applies To
- Excel for Office 365, Excel 2019, Excel 2016
Type of Function
- Worksheet function (WS)
Example (as Worksheet Function)
Let's look at some Excel NUMBERVALUE function examples and explore how to use the NUMBERVALUE function as a worksheet function in Microsoft Excel:
Based on the Excel spreadsheet above, the following NUMBERVALUE examples would return:
=NUMBERVALUE(A2, ".", ",") Result: 1000.5 'Uses a period for the decimal separator and a comma as a group separator =NUMBERVALUE(A3, ".", ","); Result: 300000.1 'Group separators do not have to be on thousand intervals =NUMBERVALUE(A4, ".", ",") Result: 3000 'Spaces are ignored in the text value =NUMBERVALUE("4_250:50", ":", "_") Result: 4250.50 'Separators can be characters other than commas and periods =NUMBERVALUE("1.200,70", ",", ".") Result: 1200.7 'Some regions use commas as decimal separators and periods as group separators
Advertisements