The Excel NUMBERVALUE Function

Excel Text and Numeric Values

Excel stores values as either text or numeric values, so, for example, if you attempt to use the text value "10" in an addition, multiplication, or other numeric operation, you will get an error.

Therefore, if you want to extract a numeric value from a text string, you need to convert this to a number, before Excel will be able to recognise it as a numeric value. The Numbervalue function can be used to do this.

Related Function:

VALUE function

Function Description

The Excel Numbervalue function converts a text string into a number, in a locale-independant way.

Note: The Numbervalue function is new in Excel 2013 and so is not available in earlier versions of Excel.

The syntax of the function is:

NUMBERVALUE( text, [decimal_separator], [group_separator] )

where the function arguments are:

text	-	The text string that you want to convert to a number.
[decimal_separator]	-	An optional argument specifying the character that separates the integer part from the decimal part of the supplied number. If [decimal_separator] is omitted, the separator from the current locale is used.
[group_separator]	-	An optional argument specifying the character that separates groups (e.g. thousands, millions, etc.). If [group_separator] is omitted, the separator from the current locale is used.

Numbervalue Function Examples

The following spreadsheet shows examples of the Excel Numbervalue function, used to convert different text strings to numbers.

Formulas:

	A
1	=NUMBERVALUE( "1000" )
2	=NUMBERVALUE( "2.45", "." )
3	=NUMBERVALUE( "1,000.05", ".", "," )
4	=NUMBERVALUE( "1.000,05", ",", "." )

Results:

	A
1	1000
2	2.45
3	1000.05
4	1000.05

Percentages and the Numbervalue Function

If the % character is used in the supplied text argument, this is treated as a percentage (i.e. the number is divided by 100).

Multiple % characters cause the numeric part of the text argument to be divided multiple times.

This is shown in the examples below.

Formulas:

	A
1	=NUMBERVALUE( "5%" )
2	=NUMBERVALUE( "5%%" )
3	=NUMBERVALUE( "5%%%" )

Results:

	A
1	0.05
2	0.0005
3	0.000005

For further details and examples of the Excel Numbervalue function, see the Microsoft Office website.

Numbervalue Function Error

If you get an error from the Excel Numbervalue function this is likely to be the #VALUE! error:

Common Error

#VALUE!

Occurs if either:

The [decimal_separator] occurs more than once in the supplied [text] argument;
The [group_separator] occurs after the [decimal_separator] within the supplied [text] argument;
Any of the supplied arguments are not valid.

Return to the Excel Text Functions Page
Return to the List of All Built-In Excel Functions