How to Use the NUMBERVALUE Function in Excel

When dealing with numerical data in Excel or Google Sheets, the NUMBERVALUE function proves invaluable. This function transforms text formatted as numbers into actual numeric values, facilitating various mathematical operations.

Basic Syntax

The syntax for the NUMBERVALUE function is as follows:

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

• text: The text string enclosed in quotation marks or a cell reference containing the text to be converted.
• [decimal_separator] (optional): The character used to denote the decimal point in the text (default is a period).
• [group_separator] (optional): The character used to denote grouping in the text (default is a comma).

Examples of Usage

Example 1: Convert Text to Number

Consider the text “123.45” in cell A1 that you need to convert into a number. You can use the formula:

=NUMBERVALUE(A1)

This formula will convert the text and return the numeric value 123.45.

Example 2: Specify Decimal Separator

If your text uses a comma as a decimal separator, for instance “123,45”, you can specify it in the formula as follows:

=NUMBERVALUE("123,45", ",", ".")

This tells the function to interpret “123,45” as the numeric value 123.45.

Example 3: Specify Group Separator

For text with a non-standard group separator such as a period (e.g., “1.234.567”), you can adjust the formula to:

=NUMBERVALUE("1.234.567", ".", ",")

This adjustment allows the function to correctly interpret “1.234.567” as the numeric value 1234567.

Wrap Up

The NUMBERVALUE function is essential for efficiently handling text-formatted numerical data in Excel or Google Sheets. It allows you to seamlessly convert text into numbers, making it easier to execute various calculations and data manipulations.

More information: https://support.microsoft.com/en-us/office/numbervalue-function-1b05c8cf-2bfa-4437-af70-596c7ea7d879