Excel PROPER Function
PROPER Function
The PROPER function is a premade function in Excel, which capitalizes the first letter of each word in a text. All the other letters become lowercase.
It is useful for names that were typed in different ways, like ash ketchum, MISTY or bRoCK.
It is typed =PROPER and has one part:
=PROPER(text)
Text: The text to change. This is usually a cell, like A2.
How to use the PROPER function:
- Select a cell (
B2) - Type
=PROPER - Double click the PROPER command
- Select the cell with the text (
A2) - Hit enter
Let's have a look at an example!
These trainers signed up for a tournament at the Poke Mart. Their names were typed in all kinds of ways. Copy them and paste them into cell A1 to follow along:
Example data
A B 1 Trainer Name 2 ash ketchum 3 MISTY 4 bRoCK 5 gary OAK 6 nurse joy 7 OFFICER JENNY 8 lt. surge 9 professor oak
Use the PROPER function to fix the names, in column B:
| A | B | |
|---|---|---|
| 1 | Trainer | Name |
| 2 | ash ketchum | Ash Ketchum |
| 3 | MISTY | Misty |
| 4 | bRoCK | Brock |
| 5 | gary OAK | Gary Oak |
| 6 | nurse joy | Nurse Joy |
| 7 | OFFICER JENNY | Officer Jenny |
| 8 | lt. surge | Lt. Surge |
| 9 | professor oak | Professor Oak |
=PROPER(A2)
The function is repeated with the filling function for each row, down to B9.
The first letter of each word is now uppercase, and the rest is lowercase. ash ketchum becomes Ash Ketchum, and bRoCK becomes Brock.
In lt. surge, the s comes after a space, so it is capitalized too: Lt. Surge.
Letters After Symbols and Numbers
PROPER does not know what a word is. It capitalizes every letter that comes after something that is not a letter: a space, a dot, a dash, an apostrophe or a digit.
This gives the right result for most names, but not for all of them:
| A | B | |
|---|---|---|
| 1 | Text | Result |
| 2 | mr. mime | Mr. Mime |
| 3 | ho-oh | Ho-Oh |
| 4 | farfetch'd | Farfetch'D |
| 5 | team rocket's | Team Rocket'S |
| 6 | 3rd place | 3Rd Place |
=PROPER(A2)
Mr. Mime and Ho-Oh are correct.
But farfetch'd becomes Farfetch'D, and team rocket's becomes Team Rocket'S. The letter after the apostrophe is capitalized.
Digits have the same effect. 3rd place becomes 3Rd Place, because the r comes after the digit 3.
PROPER also makes the letters inside a word lowercase. A name like McKenzie becomes Mckenzie.
Note: Always check the results of PROPER, especially for text with apostrophes, digits, or capital letters inside a word.
Fix Mistakes With SUBSTITUTE
You can fix a known mistake by putting PROPER inside SUBSTITUTE. SUBSTITUTE is case-sensitive, so it only changes the wrong capital letter:
| A | B | |
|---|---|---|
| 1 | Text | Result |
| 2 | team rocket's | Team Rocket's |
| 3 | 3rd place | 3rd Place |
The formula in B2:
=SUBSTITUTE(PROPER(A2), "'S", "'s")
The formula in B3:
=SUBSTITUTE(PROPER(A3), "3Rd", "3rd")
PROPER runs first. Then SUBSTITUTE changes 'S back to 's, and 3Rd back to 3rd.
Use a fix like this only where it fits. The first formula would also change O'Sullivan into O'sullivan.
Note: The different parts of the function are separated by a symbol, like comma , or semicolon ;
The symbol depends on your Language Settings.
PROPER With TRIM
Names typed by hand often have extra spaces too. Put TRIM inside PROPER to fix both at once:
| A | B | |
|---|---|---|
| 1 | Trainer | Name |
| 2 | ash ketchum | Ash Ketchum |
| 3 | MISTY | Misty |
| 4 | gary oak | Gary Oak |
=PROPER(TRIM(A2))
The names in column A have spaces before, after, and between the words. TRIM removes the extra spaces, and PROPER fixes the letter case.
Good job! The names are clean and ready to use.
Related Functions
To change all letters to capitals, use UPPER. To change all letters to lowercase, use LOWER.
