Excel NUMBERVALUE Function

NUMBERVALUE converts text that represents a number into an actual numeric value, and lets you specify which characters count as the decimal separator and the thousands separator. It exists mainly for one problem: text imported from a different regional format, where a comma means "decimal point" instead of "thousands," or vice versa. Feed NUMBERVALUE an empty cell and it quietly returns 0 instead of an error, which is worth knowing before you build a formula around it.

Available in: Excel 2013 and later, Excel 365. Not available in Excel 2010 or earlier.

Syntax

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

Parameter	Required	Description
`text`	Yes	The text you want converted to a number. Can be a literal string in quotes or a cell reference.
`decimal_separator`	No	The single character that marks the decimal point in your text. Defaults to whatever your Windows/Excel locale is set to (usually `.` in the US, `,` in much of Europe).
`group_separator`	No	The character used to group digits by thousands in your text. Defaults to your current locale setting as well.

⚠

When you omit decimal_separator and group_separator, NUMBERVALUE falls back to your system's regional settings, not a fixed default. A formula built on a US machine can misread data on a machine set to a European locale. If you're converting text with a known, specific format (like data pulled from a CSV export), always specify both separators explicitly.

Basic Example

You've imported a sales report from a German distributor, and the revenue column arrived as text: "1.234,56" in cell B2, where the period groups thousands and the comma marks the decimal.

=NUMBERVALUE(B2, ",", ".")

// B2  = the text string "1.234,56"
// ","  = the decimal separator used in this text
// "."  = the group (thousands) separator used in this text

NUMBERVALUE strips out the period, reads the comma as the decimal point, and returns the number 1234.56. That value now sums, sorts, and feeds into other formulas correctly. Left as text, it would have summed to 0 and sorted alphabetically instead of numerically.

How NUMBERVALUE Works

It converts text to a locale-independent number

NUMBERVALUE doesn't care what regional format the text was written in. Tell it which character is the decimal separator and which is the group separator, and it normalizes the result into a standard Excel number, regardless of what your machine's locale expects.

Separators must be single characters, in quotes

Both decimal_separator and group_separator need to be wrapped in double quotes, and each accepts exactly one character. =NUMBERVALUE("3,50", ",") works. Trying to pass a multi-character separator returns #VALUE!.

Percent signs are calculated, not just stripped

If the text ends in one or more % signs, NUMBERVALUE divides the result by 100 for each sign. =NUMBERVALUE("45%") returns 0.45. =NUMBERVALUE("45%%") returns 0.0045, dividing by 100 twice. Most users never need the double-percent case, but it's there.

Empty text returns 0

=NUMBERVALUE("") and =NUMBERVALUE(A2) where A2 is blank both return 0. No error, no warning. This matters if you're using NUMBERVALUE inside a larger calculation and expecting a blank cell to break the formula so you'd notice.

Common Use Cases

Converting European-formatted sales figures

You're consolidating data from a European subsidiary where numbers use a comma decimal and a period thousands separator.

=NUMBERVALUE(revenue_text, ",", ".")   // converts "12.500,75" to 12500.75

Cleaning currency text with symbols removed first

Imported financial data often carries a currency symbol that NUMBERVALUE can't parse on its own, so strip it first with SUBSTITUTE.

=NUMBERVALUE(SUBSTITUTE(A2, "$", ""), ".", ",")

// SUBSTITUTE strips the dollar sign before NUMBERVALUE ever sees the text
// "." and ","  = decimal and group separators for the cleaned text

Converting percentage strings pulled from a report

A survey export stores response rates as text like "82%" instead of a real percentage value.

=NUMBERVALUE(response_rate)   // "82%" becomes 0.82

Standardizing a mixed-format column before analysis

When a single column contains numbers typed in more than one regional format (some with commas, some with periods), wrap NUMBERVALUE in an IF to test which pattern applies before converting. This is more of a workaround than a clean fix, since NUMBERVALUE itself only accepts one separator pair per call.

Handling Errors

NUMBERVALUE throws #VALUE! when it can't parse the text into a number using the separators you gave it. This is a real, catchable error, not a silent failure like the zero-return case above.

Common causes of #VALUE!:

The text contains characters that aren't digits, separators, or a percent sign (letters, currency symbols, extra spaces)
decimal_separator and group_separator are set to the same character
The text has more than one decimal separator
The text genuinely isn't a number, like "N/A" or "Pending"

=IFERROR(NUMBERVALUE(B2, ",", "."), "Check format")
=IFERROR(NUMBERVALUE(SUBSTITUTE(A2, " ", "")), 0)

✓

If NUMBERVALUE keeps returning #VALUE! on data that looks fine at a glance, check for a non-breaking space (Unicode 160) instead of a regular space. It's invisible in the cell but breaks the parse. Wrap the text in SUBSTITUTE(text, CHAR(160), "") before converting.

Notes & Gotchas

What is NUMBERVALUE used for in Excel?

NUMBERVALUE converts text representing a number into a real numeric value, in a way that isn't tied to your system's regional settings. It's built specifically for text imported in a different locale's number format, where a plain VALUE conversion would misread the separators or fail outright.

What are the arguments of the NUMBERVALUE function?

NUMBERVALUE takes one required argument, text, and two optional arguments, decimal_separator and group_separator. If you omit the separators, Excel uses whatever decimal and thousands characters your current locale is set to.

How does NUMBERVALUE handle percent signs in text?

If the text ends in one or more % signs, NUMBERVALUE divides the numeric result by 100 for each sign present. One percent sign gives you the standard percentage-to-decimal conversion. Two signs divide by 100 twice, matching how Excel treats %% inside a regular formula.

What version of Excel introduced NUMBERVALUE?

NUMBERVALUE was added in Excel 2013. It isn't available in Excel 2010 or earlier, so a workbook built for older versions needs a different approach, typically SUBSTITUTE combined with VALUE.

Why does NUMBERVALUE return 0 instead of an error for empty text?

This is documented behavior, not a bug: NUMBERVALUE treats an empty string or blank cell as 0, silently. If your formula needs to distinguish "genuinely zero" from "no data was entered," add an explicit check with ISBLANK before calling NUMBERVALUE, since the function itself won't flag the difference.

What happens if the text argument is already a number, not text?

NUMBERVALUE handles this fine. If you pass an actual numeric value instead of a text string, Excel coerces it to text first and returns the same number unchanged. This is different from DATEVALUE, which throws #VALUE! if you feed it a real date serial number instead of text.

Does NUMBERVALUE work with dates?

Not directly. NUMBERVALUE is built for numbers, and text that looks like a date will usually return #VALUE! or an unexpected result rather than a proper date serial. Use DATEVALUE for text dates instead; it applies the same locale-independent logic but returns a date serial number rather than a plain number.

Related Functions

Function	Use this when...
`VALUE`	You're converting text to a number using your system's default locale settings, with no need for custom separators.
`DATEVALUE`	The text represents a date, not a plain number.
`TEXT`	You need to go the other direction: converting a number into formatted text.