Excel NUMBERVALUE Function
last modified April 4, 2025
The NUMBERVALUE function converts text representations of numbers
into actual numeric values. It's particularly useful for international data
with different decimal and group separators. This tutorial provides a
comprehensive guide to using NUMBERVALUE with detailed examples.
You'll learn its syntax, practical applications, and advanced techniques.
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. The optional arguments make it flexible for various
number formats.
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. It uses the system's default decimal separator (period in US locales). The result can then be used in calculations.
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 with Currency Symbols
NUMBERVALUE can handle numbers with currency symbols or other non-numeric prefixes/suffixes by ignoring them during conversion.
| A | B |
|---|---|
| $1,234.56 | |
| €1.234,56 | |
| =NUMBERVALUE(A1, ".", ",") | |
| =NUMBERVALUE(A2, ",", ".") |
The table shows numbers with currency symbols in different formats. The formulas in column B demonstrate how NUMBERVALUE ignores non-numeric characters while respecting the specified number separators.
=NUMBERVALUE("$1,234.56", ".", ",")
=NUMBERVALUE("€1.234,56", ",", ".")
These formulas convert currency-formatted text to numbers while ignoring the currency symbols. The first handles US format, the second European. Both produce 1234.56 as the numeric result, ready for calculations.
NUMBERVALUE with Percentage Values
This example demonstrates how NUMBERVALUE can convert percentage text to decimal numbers by dividing by 100 during conversion.
| A | B |
|---|---|
| 12,34% | |
| =NUMBERVALUE(A1, ",", ".")/100 |
The table shows a European-format percentage value. The formula divides the result by 100 to convert from percentage to decimal, while properly handling the comma decimal separator.
=NUMBERVALUE("12,34%", ",", ".")/100
This formula converts "12,34%" to 0.1234 by first converting the text to 12.34 then dividing by 100. This technique is useful for processing percentage data from text sources.
The NUMBERVALUE function is essential for working with
international numeric data in Excel. It provides precise control over number
format interpretation, making it invaluable for data cleaning and preparation.
By mastering its various applications, you can handle virtually any text-based
number format conversion task in your spreadsheets.
Author
List all Excel Formulas.