NUMBERVALUE

Text Functions

(4.7/5)

Converts a text string that represents a number to a number in a locale-independent way. Handles different decimal and group separators. Essential for international data conversion and locale-independent number parsing in Excel.

Interactive Formula Tester

Text Number:

Formula:

=NUMBERVALUE("1234.56")

Complete Theory & Understanding

Master the fundamentals of Excel NUMBERVALUE function

Core Concept

The NUMBERVALUE function converts text strings to numbers with support for locale-specific decimal and group separators. Unlike VALUE, NUMBERVALUE allows you to specify custom separators, making it essential for international data conversion, handling different number formats, and locale-independent number parsing.

Why Use NUMBERVALUE?

Convert numbers from different locales
Import and convert formatted numbers
Convert between number formats
Parse numbers from formatted text

Key Characteristics

Locale Support

Handles different decimal/group separators

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

Custom Separators

Specify decimal and group separators

Supports any separator characters

International Format

Handles European, US, and other formats

Works with various number formats

Better than VALUE

More flexible than VALUE function

NUMBERVALUE supports custom separators

Function Anatomy

=NUMBERVALUE(parameters...)

Required

Parameters:

Function-specific parameters

Returns

Return Value:

Function-specific return type

Primary Use Cases

International Data

Convert numbers from different locales

Data Import

Import and convert formatted numbers

Format Conversion

Convert between number formats

Text Parsing

Parse numbers from formatted text

Theory Summary

Precise

Exact matching required

Position-Based

Returns numeric position

Error-Safe

Handles missing text gracefully

Syntax & Parameters

=NUMBERVALUE(text, decimal_separator, group_separator)

Required

text:

The text string to convert to a number

Optional

decimal_separator:

The character used to separate the integer and fractional part of the result. Defaults to period (.) if omitted.

Optional

group_separator:

The character used to separate groupings of numbers (thousands). Defaults to comma (,) if omitted.

Returns

Return Value:

Numeric value

Description: Converts text string to number with locale-specific decimal and group separators

Interactive Examples

Basic Conversion

Convert text number to numeric

Input Text:

""123.45""

Formula:

=NUMBERVALUE("123.45")

Result:

123.45

Converts text string to number with default separators

VBA Implementation & Automation

Basic NUMBERVALUE in VBA

Simple VBA implementation of NUMBERVALUE function

' Basic NUMBERVALUE in VBA
Range("B1").Value = Application.WorksheetFunction.NumberValue(Range("A1").Value)

' Using VBA NumberValue function
Dim textValue As String
textValue = "123.45"
Dim numValue As Double
numValue = Application.WorksheetFunction.NumberValue(textValue)
Debug.Print numValue ' Output: 123.45

' Convert European format
Sub ConvertEuropeanFormat()
    Dim cell As Range
    For Each cell In Range("A1:A10")
        If Not IsEmpty(cell.Value) Then
            On Error Resume Next
            Dim numValue As Double
            numValue = Application.WorksheetFunction.NumberValue(cell.Value, ",", ".")
            If Err.Number = 0 Then
                cell.Offset(0, 1).Value = numValue
            Else
                Err.Clear
            End If
            On Error GoTo 0
        End If
    Next cell
End Sub

Advanced Format Conversion

Handle multiple number formats

' Function to detect and convert number format
Function SmartNumberValue(text As String) As Variant
    On Error Resume Next
    
    ' Try US format first
    Dim result As Double
    result = Application.WorksheetFunction.NumberValue(text, ".", ",")
    If Err.Number = 0 Then
        SmartNumberValue = result
        Exit Function
    End If
    Err.Clear
    
    ' Try European format
    result = Application.WorksheetFunction.NumberValue(text, ",", ".")
    If Err.Number = 0 Then
        SmartNumberValue = result
        Exit Function
    End If
    Err.Clear
    
    ' Try space as group separator
    result = Application.WorksheetFunction.NumberValue(text, ".", " ")
    If Err.Number = 0 Then
        SmartNumberValue = result
        Exit Function
    End If
    
    SmartNumberValue = "#VALUE!"
    On Error GoTo 0
End Function

' Convert range with auto-detection
Sub ConvertRangeAutoFormat()
    Dim cell As Range
    For Each cell In Range("A1:A100")
        If Not IsEmpty(cell.Value) Then
            cell.Offset(0, 1).Value = SmartNumberValue(CStr(cell.Value))
        End If
    Next cell
End Sub

Business Applications

International Data

Convert numbers from different locales

=NUMBERVALUE(A1, ",", ".")

European Format

Handle European number format

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

Data Import

Import and convert formatted numbers

=NUMBERVALUE(A1)

Format Conversion

Convert between number formats

=NUMBERVALUE(A1, ".", ",")

Common Issues & Solutions

#VALUE! Error

NUMBERVALUE returns #VALUE! when text cannot be converted

=IFERROR(NUMBERVALUE(A1, ",", "."), 0)

Solution: Ensure text contains valid numeric characters and separators match the format. Use IFERROR to handle: =IFERROR(NUMBERVALUE(A1, ",", "."), 0)