The PROPER Function is categorized under Excel Text functions. PROPER will capitalize the first letter in a text string and any other letters in text that follow any character other than a letter.
In financial analysis, we often import data from external sources. The PROPER function will help us convert text into the proper case, if required. We can use it to capitalize each word in a given string. Using the function will not affect numbers and punctuations. It will convert all other letters to lowercase.
Text is the required argument. Text needs to be enclosed in quotation marks, a formula that returns text, or a reference to a cell containing the text you want to partially capitalize.
How to use the PROPER Function in Excel?
The TODAY function was introduced in Excel 2007 and is available in all subsequent Excel versions. To understand the uses of the function, let’s consider a few examples:
Let’s see how the PROPER function will behave when we give the following formula:
We get the results below:
Suppose we wish to clean up names that are not in a proper case, we can use a simple formula based on the PROPER function.
Suppose we are given the data below:
The formula to use is:
We get the results below:
The PROPER function will automatically reformat the text so that all words are capitalized. At the same time, it will convert to lowercase all other text.
If we also need to strip out extra spaces in the names, we can wrap PROPER in the TRIM function: =TRIM(PROPER(name). TRIM will remove any leading and trailing spaces, and convert runs of spaces into a single space.
Things to remember about the PROPER Function
The first letter of every word, letters following numbers, or other punctuation are converted to uppercase by the PROPER function. It may be in places that we may not expect. For example, the function converts the “s” in the text string “Bob’s Boat” to uppercase (i.e. “Bob’S Boat”).
Numbers and punctuation characters are not affected.
Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial modeling. To learn more, check out these additional CFI resources:
Take your learning and productivity to the next level with our Premium Templates.
Upgrading to a paid membership gives you access to our extensive collection of plug-and-play Templates designed to power your performance—as well as CFI's full course catalog and accredited Certification Programs.
Already have a Self-Study or Full-Immersion membership? Log in
Access Exclusive Templates
Gain unlimited access to more than 250 productivity Templates, CFI's full course catalog and accredited Certification Programs, hundreds of resources, expert reviews and support, the chance to work with real-world finance and research tools, and more.