Excel NUMBERVALUE Function

last modified October 5, 2026

The NUMBERVALUE function converts a text representation of a number into an actual numeric value using decimal and group separators that you specify. Because you provide the separators yourself, the result does not depend on the regional settings of the computer that opens the workbook. This tutorial provides a comprehensive guide to using NUMBERVALUE with detailed examples. You'll learn its syntax, how it differs from VALUE, and how to handle international number formats reliably.

NUMBERVALUE Function Basics

The NUMBERVALUE function converts text to numbers while allowing custom decimal and group separators. It handles international number formats and cleans text-based numeric data for calculations.

Component	Description
Function Name	NUMBERVALUE
Syntax	=NUMBERVALUE(text, [decimal_separator], [group_separator])
Arguments	Text (required), Decimal separator (optional), Group separator (optional)
Return Value	Numeric value converted from text

This table breaks down the essential components of NUMBERVALUE. It shows the function name, syntax format, argument requirements, and return value characteristics.

Argument	Meaning
text	The text to convert. It can be a literal string, a cell reference, or an expression that returns text, such as A1&"".
decimal_separator	The character that separates the integer and fractional parts in the source text. If it is omitted, the decimal separator of the current locale is used.
group_separator	The character that groups thousands, millions, and so on in the source text. If it is omitted, the group separator of the current locale is used.

Only the first character of each separator argument is used, so ".," for the group separator means the same as ".". If decimal_separator and group_separator are the same character, NUMBERVALUE returns the #VALUE! error. Supplying both separators explicitly is what makes the function locale-independent.

Basic NUMBERVALUE Example

This example demonstrates the simplest use of NUMBERVALUE with standard numeric text.

=NUMBERVALUE("1234.56")

This formula converts the text "1234.56" to the numeric value 1234.56. Because no separators are supplied, Excel uses the separators from the current locale. In a US locale the period is the decimal separator, so the result is 1234.56. On a computer whose locale uses a comma as the decimal separator and a period as the group separator, the same text is read as 123456. To avoid this ambiguity, always pass the separators explicitly.

NUMBERVALUE with Custom Decimal Separator

This example shows how to handle numbers using different decimal separators, common in European formats.

A	B
1234,56
	=NUMBERVALUE(A1, ",", ".")

The table shows a European-style number in cell A1 with comma as decimal separator. The formula in B1 converts it to a proper number by specifying the custom decimal separator.

=NUMBERVALUE("1234,56", ",", ".")

This formula converts "1234,56" to 1234.56 by specifying comma as the decimal separator. The third argument (group separator) is set to period but isn't used here. The result is a proper numeric value.

NUMBERVALUE with Group Separators

This example demonstrates handling numbers with thousand separators, which NUMBERVALUE can process by specifying the group separator.

A	B
1,234.56
	=NUMBERVALUE(A1, ".", ",")

The table shows a number with thousand separators in cell A1. The formula in B1 converts it to a proper number by specifying comma as group separator and period as decimal separator.

=NUMBERVALUE("1,234.56", ".", ",")

This formula converts "1,234.56" to 1234.56 by properly interpreting the comma as thousand separator and period as decimal point. The group separator is removed during conversion to create a proper numeric value.

NUMBERVALUE with European Format

This example shows how to handle full European number format with swapped decimal and group separators compared to US format.

A	B
1.234,56
	=NUMBERVALUE(A1, ",", ".")

The table demonstrates a European-format number where period is thousand separator and comma is decimal separator. The formula correctly interprets this format by specifying the appropriate separators.

=NUMBERVALUE("1.234,56", ",", ".")

This formula converts "1.234,56" to 1234.56 by using comma as decimal separator and period as group separator. This is essential for processing international financial data correctly in Excel.

NUMBERVALUE and Locale Independence

When the optional arguments are omitted, NUMBERVALUE falls back to the decimal and group separators of the current locale. That makes the result depend on the machine that opens the workbook. The same text can even produce different results, or an error, on different computers.

Locale	Decimal	Group	=NUMBERVALUE("1.234,56")
United States	.	,	#VALUE!
Germany	,	.	1234.56

In the US locale the period is taken as the decimal separator and the comma then appears after it, which is invalid. In the German locale the comma is the decimal separator and the period is the group separator, so the text converts to 1234.56. Writing the separators into the formula removes the guesswork: =NUMBERVALUE("1.234,56",",",".") returns 1234.56 on every computer, regardless of its regional settings.

=NUMBERVALUE("1.234,56",",",".")

This is the main advantage of NUMBERVALUE over VALUE. A workbook that parses imported data with NUMBERVALUE behaves the same for every user, while a formula based on VALUE follows whatever regional settings the reader happens to use.

Decimal Separator vs Group Separator

The two separators play different roles. The decimal separator marks where the fractional part begins, and it is kept in the result. The group separator only divides the integer part into readable chunks, and it is removed before the number is built. Excel applies a fixed set of rules while scanning the text.

Rule	Result
Spaces anywhere in the text	Ignored, so " 3 000 " becomes 3000
Group separator before the decimal separator	Removed, so "1,234.56" becomes 1234.56
Group separator after the decimal separator	#VALUE! error
Decimal separator appears more than once	#VALUE! error
Decimal and group separators are the same character	#VALUE! error
Text ends in one or more percent signs	The percentage is applied

These rules explain why =NUMBERVALUE("1,2,3",".",",") returns 123 and not an error: every comma is a group separator that appears before the decimal separator, so all of them are removed. By contrast, =NUMBERVALUE("1.2.3",".",",") returns #VALUE! because the decimal separator is used twice.

=NUMBERVALUE("1,2,3",".",",")
=NUMBERVALUE("1.2.3",".",",")

A group separator that follows the decimal separator is rejected rather than ignored. For example, =NUMBERVALUE("1,23.45",",",".") returns #VALUE! because the period, which is the group separator here, comes after the comma decimal separator.

NUMBERVALUE with Currency Symbols

NUMBERVALUE does not strip currency symbols. A string such as "$1,234.56" contains a character the function cannot interpret, so the formula returns the #VALUE! error. Remove the symbol first with SUBSTITUTE, then convert the cleaned text.

A	B
$1,234.56
€1.234,56
	=NUMBERVALUE(SUBSTITUTE(A1,"$",""), ".", ",")
	=NUMBERVALUE(SUBSTITUTE(A2,"€",""), ",", ".")

The table shows numbers with currency symbols in two formats. Each formula removes the matching symbol and then converts the remaining text with the correct separators.

=NUMBERVALUE(SUBSTITUTE("$1,234.56","$",""), ".", ",")
=NUMBERVALUE(SUBSTITUTE("€1.234,56","€",""), ",", ".")

The first formula removes the dollar sign and reads the period as the decimal separator, giving 1234.56. The second removes the euro sign and reads the comma as the decimal separator, also giving 1234.56. Both results are ready for calculations. If a value contains several unwanted characters, nest additional SUBSTITUTE calls around the text.

NUMBERVALUE with Percentage Values

A percent sign at the end of the text is not an error. NUMBERVALUE recognizes it and divides the result by 100, exactly as Excel does when you type a percentage into a cell. You do not need to divide by 100 yourself.

A	B
12,34%
	=NUMBERVALUE(A1, ",", ".")

The table shows a European-format percentage. The formula converts it directly to 0.1234, handling the comma decimal separator along the way.

=NUMBERVALUE("12,34%", ",", ".")

This formula returns 0.1234, not 12.34. Multiple percent signs are additive, just as they are in a formula, so =NUMBERVALUE("9%%") returns 0.0009. Dividing the result by 100 again would be a mistake and would produce 0.001234.

NUMBERVALUE Error Conditions

Most problems with NUMBERVALUE end in the #VALUE! error. The table below lists the common triggers so you can recognize them quickly.

Text	Formula	Result
(empty string)	=NUMBERVALUE("")	0
(a space)	=NUMBERVALUE(" ")	0
abc	=NUMBERVALUE("abc")	#VALUE!
+	=NUMBERVALUE("+")	#VALUE!
-	=NUMBERVALUE("-")	#VALUE!
1.2.3	=NUMBERVALUE("1.2.3",".",",")	#VALUE!
1,23.45	=NUMBERVALUE("1,23.45",",",".")	#VALUE!
$1,234.56	=NUMBERVALUE("$1,234.56",".",",")	#VALUE!

The first two rows show that an empty or blank string is treated as zero. The remaining rows show invalid text: letters, a lone sign, a repeated decimal separator, a group separator placed after the decimal separator, or an unexpected symbol such as a currency sign. All of them produce #VALUE!.

NUMBERVALUE vs VALUE

Both functions turn text into a number, but they read the text differently. VALUE follows the current locale and understands dates, times, currency symbols, and thousands separators as the locale defines them. NUMBERVALUE ignores the locale when you supply separators, and it does not interpret dates or times at all.

Feature	NUMBERVALUE	VALUE
Separators	You specify decimal and group separators	Taken from the current locale
Locale independence	Yes, when both separators are supplied	No, results change with regional settings
Dates and times	Not supported	Supported
Currency symbols	Not supported, returns #VALUE!	Accepted in the matching locale
Percent signs	Applied	Applied

Use NUMBERVALUE when the number format is fixed and must be read the same way everywhere, for example when importing a file that always uses a comma decimal separator. Use VALUE when the text follows the reader's own regional settings or when you need to convert dates and times.

NUMBERVALUE Errors and Troubleshooting

Cause	Symptom	Fix
Decimal and group separators are the same character	#VALUE! error	Choose two different characters
Decimal separator appears more than once	#VALUE! error	Correct the source text or use SUBSTITUTE
Group separator appears after the decimal separator	#VALUE! error	Swap the separators or clean the text first
Text contains a currency symbol	#VALUE! error	Remove it with SUBSTITUTE before converting
Text has no digits, only letters or a sign	#VALUE! error	Validate the input with ISNUMBER or IFERROR
Separators omitted and locale differs from the data	Wrong number or #VALUE!	Supply decimal and group separators explicitly
Result divided by 100 after a percentage	Value 100 times too small	Remove the extra division, NUMBERVALUE already applies percent

Wrap the call in IFERROR when the source data is unpredictable: =IFERROR(NUMBERVALUE(A1,",","."),"check format") returns a clear message instead of an error.

The NUMBERVALUE function is essential for working with international numeric data in Excel. It provides precise control over number format interpretation and, when you supply both separators, produces the same result on every computer. Remember that it ignores spaces, applies trailing percent signs, removes group separators that appear before the decimal separator, and returns #VALUE! for currency symbols and other unexpected characters. By combining it with SUBSTITUTE and IFERROR, you can handle virtually any text-based number format and prepare clean, reliable data for calculations.

Author

My name is Jan Bodnar, and I am a passionate programmer with extensive programming experience. I have been writing programming articles since 2007. To date, I have authored over 1,400 articles and 8 e-books. I possess more than ten years of experience in teaching programming.

List all Excel Formulas.