Purpose

Return value

Syntax

=NUMBERVALUE(text,[decimal_separator],[group_separator])
  • text - The text to convert to a number.
  • decimal_separator - [optional] The character for decimal values.
  • group_separator - [optional] The character for grouping by thousands.

Using the NUMBERVALUE function

The NUMBERVALUE function converts a text value representing a number into a valid numeric using custom decimal and group separators. You can use NUMBERVALUE to translate numbers from a locale-specific text format into a locale-independent number.

To perform a numeric conversion, the NUMBERVALUE function uses the custom separators you provide. The decimal_separator is the character used to separate integers from fractional values in the source text. The group_separator is the character used to group text by thousands in the source text. Both separators should be enclosed in double quotes (""). When decimal_separator and group_separator , Excel uses separators for the current locale.

Examples

To convert the text string “10,15” to the number 10.15:

=NUMBERVALUE("10,15",",") // returns 10.15

To convert the text value “5%” to a number with no grouping or decimal separator:

=NUMBERVALUE("5%") // returns 0.05

To convert the string “6.000” to the number 6000, where the grouping separator in the source text is a period (.) use:

=NUMBERVALUE("6.000",",",".") // returns 6000

In the example shown, input text is in column B and function output is in column E. Columns C and D are the decimal and group separators used in each row. The formula in E6, copied down, is:

=NUMBERVALUE(B6,C6,D6)

Notice in addition to decimal and group separators, the NUMBERVALUE automatically ignores the extra space in B9 and automatically evaluates the percentage (%) symbol in B11 by dividing by 100.

Notes

  • NUMBERVALUE ignores extra space characters.
  • Multiple percent symbols are additive.
  • If decimal separator and/or group_separators are not provided, NUMBERVALUE uses separators from the current locale.
  • NUMBERVALUE uses only the first character provided for decimal and group separators. Additional characters are discarded.
  • NUMBERVALUE returns zero (0) if no text value is provided.
  • NUMBERVALUE returns the #VALUE error if: The decimal separator appears more than once in the source text The group separator occurs after the decimal separator

Purpose

Return value

Syntax

=PROPER(text)
  • text - The text that should be converted to proper case.

Using the PROPER function

The PROPER function capitalizes each word in a given text string. PROPER function takes just one argument, text , which can be a text value or cell reference. PROPER first lowercases any uppercase letters, then capitalizes each word in the provided text string. Numbers, punctuation, and spaces are not affected. PROPER will convert numbers to text with number formatting removed.

Examples

=PROPER("apple") // returns "Apple"
=PROPER("APPLE") // returns "Apple"

Numbers or punctuation characters inside a text string are unaffected:

=PROPER("XYY-020-kwp") // returns "Xyy-020-Kwp"

If a numeric value is given to PROPER, number formatting is removed. For example, if cell A1 contains the date June 26, 2021, date formatting will be lost and PROPER will return a date serial number as text:

=PROPER(A1) // returns "44373"

Use the LOWER function to convert text to lowercase, use the UPPER function to convert text to uppercase, and use the PROPER function to capitalize the words in a text string.

Capitalizing the first word only

One limitation of the PROPER function is that it will capitalize all words in a text string . If you only want to capitalize the first word (i.e. capitalize the first word in a sentence) while leaving other characters unchanged, you can use a custom formula like this:

=REPLACE(A1,1,1,UPPER(LEFT(A1)))

See this page for a full explanation .

Notes

  • Use PROPER to capitalize each word in a given string.
  • All words in a text string are capitalized.
  • Numbers and punctuation characters are not affected.
  • Number formatting is removed from standalone numeric values.