In Excel. you can quickly change the case of the text in a cell (to lower case, upper case, or proper case) using text functions. Below is an example of each type of case:
Excel PROPER Function – Overview
PROPER function is one of the many text functions in Excel.
What Does it Do?
It takes a string as the input and returns a string where the first letter of all the words has been capitalized and all the remaining characters are in lower case.
When to Use it?
Use it when you have a text string and you want to capitalize the first alphabet of each word in the text string and make all the other character in lowercase. This could be the case when you have names in different formats and you want to make it consistent by capitalizing the first alphabet of the first and the last name.
Proper Function Syntax
text – the text string in which you want in capitalize the first letter of each word.
Examples of using PROPER Function
Here are some practical examples to show you how to the PROPER function in an Excel worksheet.
Example 1 – Making Names Consistent
Suppose you have the dataset as shown below:
The names in this dataset are all inconsistent. You can use the PROPER function to make these consistent (where the first alphabet of each name is capitalized and rest all are small). The below formula would do this:
In the above formula, I use the ampersand operator to add the text in cells in column A and B, and then PROPER function makes the combined string consistent.
Example 2 – Making Address Consistent
Just like the names, you can also use it to make the address consistent. Below is an example dataset where the addresses are in an inconsistent format:
You can use the below formula to make all these addresses in a consistent format: Note that this formula works perfectly, but if you want the state code (such CA, NV, NY) in upper case, it will not be done with PROPER function only. In that case, you need to use the below formula: You can get an idea of how this formula works from this tutorial. Some useful things to know about the PROPER Function: Other Useful Excel Functions:
Excel FIND Function: Excel FIND function can be used when you want to locate a text string within another text string and find its position. It returns a number that represents the starting position of the string you are finding in another string. It is case-sensitive. Excel LOWER Function: Excel LOWER function can be used when you want to convert all uppercase letter in a text string to lowercase. Numbers, special characters, and punctuations are not changed by it. Excel UPPER Function: Excel UPPER function can be used when you want to convert all lowercase letter in a text string to uppercase. Numbers, special characters, and punctuations are not changed by it. Excel REPLACE Function: Excel REPLACE function can be used when you want to replace a part of the text string with another string. It returns a text string where a part of the text has been replaced by the specified string. Excel SEARCH Function: Excel SEARCH function can be used when you want to locate a text string within another text string and find its position. It returns a number that represents the starting position of the string you are finding in another string. It is NOT case-sensitive. Excel SUBSTITUTE Function: Excel SUBSTITUTE function can be used when you want to substitute text with new specified text in a string. It returns a text string where an old text has been substituted by the new one.
