The PROPER function in Excel is used to convert the first letter of each word of a given string to upper case, and the remaining characters to lower case.
Along with the UPPER function and the LOWER function, PROPER converts text in Excel. The PROPER function converts the first letter to upper case only.
The PROPER function belongs to the Text function category in the Excel functions list.
The PROPER function syntax contains just one argument, which is the text you want to convert to proper case.
=PROPER(text)The text you want to convert to proper case, is usually provided as a cell reference, or a text value returned by another Excel worksheet function.
Let’s see some practical examples.
Download the practise file to follow along.
Excel PROPER Function Example
In this simple PROPER function example, the country names in column A have been converted to proper case.
=PROPER(A2)
The first letter of each country name is converted to upper case and all other letters are changed to lower case. This is noted by the country name “Latvia”, where the last letter was accidentally entered as an upper case letter.
PROPER Function in Excel to Capitalise Names
A common use of the PROPER function is to capitalise names in Excel.
In the following PROPER formula, the names in the [Name] column of the table have been changed.
=PROPER([@Name])
How the Excel PROPER Function Works
The PROPER function in Excel ignores any non text characters (except those with specific roles such as =, + and -) that precede the letters in the text string.
This is most notable when the text contains leading spaces such as in the following example.
This formula still manages to capitalise the first letter in the name “Milla Jovovich” despite there being a space in the first character position.
=PROPER(A2)
PROPER Function in Excel with TRIM
Although the PROPER function ignored the leading space in the previous example, the space should be removed for effective data analysis.
To remove erroneous spaces from a text string, you can use the PROPER function combined with the TRIM function. The TRIM function removes both leading and trailing spaces from a text string.
In this PROPER formula, TRIM has been nested to remove the leading space before the name “Milla Jovovich”.
=PROPER(TRIM([@Name]))
PROPER with Other Excel Functions
You can use the PROPER function to convert text returned from any function in Excel.
In this example, the PROPER function is used to convert text returned by the VLOOKUP function in Excel.
=PROPER(VLOOKUP(A2,tblRegions,2,FALSE))
The VLOOKUP function returns the region from the second column of the table named tblRegions where there is a match for the value entered in cell reference A2.
We use the PROPER function in Excel to then convert the returned region name to proper case.
The PROPER function is simple to use and can be very useful in Microsoft Excel.
Learn more than 150 of the best Excel functions with Advanced Excel Formulas. This book contains over 500 ‘real world’ examples with all practise files provided.
