How do I convert a column of mixed-format salary strings to numbers in one step?

Create a helper column that calls NUMBERVALUE with the appropriate separators for each row, or use BYROW/LAMBDA to apply different separator pairs based on a lookup table. The result will be a clean numeric column ready for aggregation.

Can NUMBERVALUE handle negative numbers or parentheses for accounting formats?

Yes. NUMBERVALUE recognises a leading minus sign ("-123") and parentheses ("(123)") as negative values, converting them to the corresponding numeric result. Ensure the separators are correctly specified so the function can still locate the decimal point.

Why does NUMBERVALUE return #VALUE! even though the string looks like a number?

The most common cause is a mismatch between the actual separators in the string and the ones you supplied. Double-check that the decimal_separator matches the character that separates the fractional part and that the group_separator matches the thousands delimiter, and that they are not the same character.

Is there a way to convert numbers stored as text without specifying separators?

If the text follows the current locale's formatting, simply use VALUE() or add a unary plus (+) before the cell reference. Both coerce the text to a number without needing to tell the function what separators to expect.

NUMBERVALUE function

NUMBERVALUE converts a text string that represents a number—using any decimal or group separator—into a true numeric value that can be used in calculations.

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

Generate a NUMBERVALUE formula

Describe what you need. The generator will reach for NUMBERVALUE where NUMBERVALUE is the right tool, and tell you when it is not.

Arguments

How NUMBERVALUE reads its arguments

Argument	Required	Description
text	Required	The text (or reference to a cell containing text) that looks like a number; if omitted or not parsable, NUMBERVALUE returns an error.
decimal_separator	Optional	Optional single-character string that tells NUMBERVALUE which symbol marks the decimal place; if omitted Excel assumes the system locale.
group_separator	Optional	Optional single-character string that tells NUMBERVALUE which symbol separates thousands; if omitted Excel assumes the system locale.

Returns

A single numeric scalar representing the parsed value.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Convert US-style salary to a number

EmployeeID	Name	Department	HireDate	Salary	Status
101	Alice	Finance	2015-03-01	45,000.75	Active

=NUMBERVALUE(E2, ".", ",")

Result: 45000.75

The salary in E2 uses a comma to separate thousands and a period for the decimal. By passing "." as the decimal_separator and "," as the group_separator, NUMBERVALUE strips the commas, recognises the period as the decimal point, and returns the numeric value 45000.75. This value can now be summed, averaged, or used in any arithmetic formula.

2. Parse European-style salary with dot as group and comma as decimal

EmployeeID	Name	Department	HireDate	Salary	Status
102	Bob	HR	2018-07-15	38.500,50	Active

=NUMBERVALUE(E3, ",", ".")

Result: 38500.5

Bob's salary string uses a period to separate thousands and a comma for the decimal fraction. Supplying "," as the decimal_separator and "." as the group_separator tells NUMBERVALUE how to interpret each symbol, resulting in the numeric value 38500.5. The function ignores the group separator and converts the comma-based decimal correctly.

3. Handle space-delimited thousands with a comma decimal

EmployeeID	Name	Department	HireDate	Salary	Status
103	Carlos	IT	2020-11-30	52 000,00	On Leave

=NUMBERVALUE(E4, ",", " ")

Result: 52000

Carlos' salary contains a space between the thousands and a comma for the decimal part. By setting the decimal_separator to "," and the group_separator to a space character, NUMBERVALUE removes the space, treats the comma as the decimal marker, and returns the clean number 52000. This demonstrates the function's flexibility with non-standard separators.

Common errors

Which NUMBERVALUE error are you seeing?

Error	Why it happens	How to fix it
#VALUE!	The text argument contains characters that cannot be interpreted as a number, such as "N/A" or alphabetic letters.	Replace the non-numeric text with a valid numeric string or wrap the call in IFERROR to supply a fallback value.
#NUM!	The parsed number exceeds Excel's maximum representable value (approximately 9.99E307).	Ensure the source string represents a value within Excel's numeric limits, or truncate/scale the number before calling NUMBERVALUE.
#NULL!	Both decimal_separator and group_separator are supplied as the same character, making the parsing ambiguous.	Provide two distinct separator characters, or omit one of them so Excel can use the locale defaults.

Tips and when to use something else

If your data already uses the system locale, you can omit the optional separators and let NUMBERVALUE rely on defaults.
When cleaning imported CSV files, combine SUBSTITUTE with NUMBERVALUE to replace unexpected separators before conversion.
Use VALUE() instead of NUMBERVALUE when you only need to convert plain numbers without custom separators.
Wrap NUMBERVALUE in IFERROR() to avoid #VALUE! errors propagating through downstream calculations.

Related functions

VALUEVALUE converts a text string that looks like a number, currency, or date into the underlying numeric value Excel or Sheets can use in calculations.TEXTTEXT converts a numeric, date, or time value into a formatted text string based on a custom format pattern you supply.SUBSTITUTESUBSTITUTE returns a new text string where specified characters or substrings are replaced, optionally limiting the change to a particular occurrence.VLOOKUPVLOOKUP searches the first column of a table and returns a value from a specified column in the same row, optionally using exact or approximate matching.IFERRORIFERROR returns the result of a formula unless that formula generates an error, in which case it returns a user-specified alternative value.

Frequently asked questions

How do I convert a column of mixed-format salary strings to numbers in one step?: Create a helper column that calls NUMBERVALUE with the appropriate separators for each row, or use BYROW/LAMBDA to apply different separator pairs based on a lookup table. The result will be a clean numeric column ready for aggregation.
Can NUMBERVALUE handle negative numbers or parentheses for accounting formats?: Yes. NUMBERVALUE recognises a leading minus sign ("-123") and parentheses ("(123)") as negative values, converting them to the corresponding numeric result. Ensure the separators are correctly specified so the function can still locate the decimal point.
Why does NUMBERVALUE return #VALUE! even though the string looks like a number?: The most common cause is a mismatch between the actual separators in the string and the ones you supplied. Double-check that the decimal_separator matches the character that separates the fractional part and that the group_separator matches the thousands delimiter, and that they are not the same character.
Is there a way to convert numbers stored as text without specifying separators?: If the text follows the current locale's formatting, simply use VALUE() or add a unary plus (+) before the cell reference. Both coerce the text to a number without needing to tell the function what separators to expect.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator