Close the Skill Gap -> Enroll to be a Certified Financial Modeling & Valuation Analyst (FMVA)® Today!

PROPER Function

Capitalizes the first letter in a text string

What is the PROPER Function?

PROPER function is categorized under 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 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.

 

Formula

=PROPER(Text)

 

Text is 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 us consider a few examples:

 

Example 1

Let’s see how the PROPER function will behave when we give the following formula:

 

PROPER Function

 

We get the results below:

 

PROPER Function - Example 1

 

Example 2

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:

 

PROPER Function - Example 2

 

The formula to use is:

 

PROPER Function - Example 2a

 

We get the results below:

 

PROPER Function - Example 2b

 

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

  1. 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”).
  2. Numbers and punctuation characters are not affected.

 

Click here to download the sample Excel file

 

Additional resources

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 resources:

  • Excel Functions for Finance
  • Advanced Excel Formulas Course
  • Advanced Excel Formulas You Must Know
  • Excel Shortcuts for PC and Mac

Free Excel Tutorial

To master the art of Excel, check out CFI’s FREE Excel Crash Course, which teaches you how to become an Excel power user.  Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.

 

Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!