Unlike other Office Applications, Excel does not auto-suggest capitalization when the text strings are in lowercase. But, you wouldn’t want to leave those words as it is, right? Especially if you have Proper nouns (For eg: june, texas, lily, marketing, etc) in your data.
In Excel, you cannot find the Change Case menu in the Ribbon to format the capitalization of a word. But, there is a PROPER function built-in to do just that. Using a single formula, you can convert each initial letter of a word into capital. Having said that, PROPER function is also useful when using CONCAT or TRIM functions.
Arguments for PROPER Function
Excel’s PROPER function returns every first letter of a text string in upper case whereas the remaining letters in lowercase. Remember, this function also capitalizes the initial letter of the articles such as an, or, has, the etc.
Syntax: PROPER(text)The PROPER function just takes one single argument. It could be a text string or a cell reference with a value you want to capitalize the initial letter.
Things You Should Know Before You Use PROPER Function
- If you pass down a word or text string in the PROPER function, you must enclose them within the double quotation mark. Your formula would be
=PROPER(“text”). In case you do not use double quotation marks, the PROPER function will result in #NAME? Error. - For cell reference, there’s no need to use double quotation marks. Your formula would be
=PROPER(Cell Reference). Now, here, if you double-quote cell references, it will return the exact text string. For example, if you enter=PROPER(“A2”), you will get A2 as output instead of the value inside the cell. - We do not recommend you use the PROPER function if you have existing Uppercase letters. For example, if you use
=PROPER(“new york, USA”)formula, it will return “New York, Usa.” You might not want to change the USA to Usa. - The PROPER function won’t be effective for words that have an apostrophe symbol. This is because the function will convert the letter after the apostrophe to a capital letter too. For Example,
=PROPER(“nisha’s pdf”)formula would give you “Nisha’S Pdf” as a result. Noticed the capital S after the apostrophe?
Examples of Using PROPER Function
| Example | Word/Sentence | Formula | Description |
| Text String | jANUAry | =PROPER(“jANUAry”) | The PROPER function converts all other text strings into lowercase and returns the first letter in Uppercase. As a result, we got January. |
| Cell Reference | tRainINg | =PROPER(A3) | Here, the formula returns the initial letter of the cell A3 as capital and the rest of the text strings in lowercase which is Training. The best part of using cell reference in a formula is you don’t have to re-enter the formula. Just drag the flash-fill handle (+ cursor) to apply the formula to the rest of the cells. |
| Article Sentence | Excel has all types of shapes such as basic, rectangles, block arrows, flowcharts, callouts, and many more. | =PROPER(A4) | PROPER function changes every first letter of the sentence in the capital along with the articles. It returned “Excel Has All Types Of Shapes Such As Basic, Rectangles, Block Arrows, Flowcharts, Callouts, And Many More” sentence. |
How to Use the PROPER Function Nested With Other Functions?
CONCAT and PROPER
If you need to combine values that are in lowercase, it is best you use CONCAT and PROPER functions together. When use the nest CONCAT function inside the PROPER function, it’ll return the first letter of the joined texts in capital.
For Example, we have parts of a street address in each cell. But, all of the values are in lowercase. Now, to merge and convert them into first letter capital, we entered the formula as
=PROPER(CONCAT(A2, " ", B2, " ", C2))
In the above formula, the CONCAT function first returns the joined value of cells A2, B2, and C2 with spaces in between which is “5331 rexford court montgomery.” Then, the PROPER function capitalizes each first letter and returns 5331 Rexford Court Montgomery as the final output.
TRIM and PROPER
Sometimes, you may have irregular spacing between the text strings in your spreadsheet. In that case, when you solely use the PROPER function to capitalize the first letter, it’ll have spaces in the result too. Although it could be unnoticeable, having extra spaces in your values can be problematic.
To address this, you can nest the TRIM and PROPER functions together. In the given example, we have entered the PROPER function as
=PROPER(TRIM(A2))
The TRIM function returns the value with unnecessary spaces eliminated. Then, the PROPER function capitalizes the initial letter. As a result, you get “When You Calculate The Sum Of Number.”