PROPER Function

What is the PROPER Function?

The PROPER function in Excel returns a text string, where the first letter of the text is capitalized while the rest of the letters in the word are set to lowercase.

The Excel files we receive from external sources often have text values in irregular formats. For example, the automated file generated by the stock brokers may have your name as "StePHEn STRangE." 

The essence of data management is that you cannot store the data as it is if it contains irregularities. These irregularities can include additional spaces, extra characters, or random capitalized letters in the text value.

Different functions achieve different objectives as the TRIM function removes the additional spaces. For example, you can use the combination of several text functions to remove extra characters, and you can use this function to return text strings in the 'proper' case.

This article will show how to use this function to maintain a cleaner data version.

Understanding the PROPER function

The PROPER is categorized as a text function that takes in a text argument and returns the first letter of word/word in uppercase, while the rest of the letters are set to lowercase.

For example, if you have the word "WALLStreeTOasIS," then by using the PROPER function, you will get the result as "Wallstreetoasis." 

Notice it is entirely one word? If we separate the word into two components, i.e., "WALLStreeT" and "OasIS" separated by space as "WALLStreeT OasIS," then you will get the result as "WallStreet Oasis." 

This means that if you enclose a sentence inside the function, every world will begin with a capitalized letter followed by lowercase letters. The process is available on all the Excel versions from the latest Excel 365, Excel 2019, Excel 2013, and up to Excel 2000.

PROPER Function Formula

The syntax for the function is:

=PROPER(text)

where,

text = (required) a text string enclosed inside the quotation marks, a cell reference for the text strings, or a formula that returns a text value.

PROPER Function Example

Suppose that you have various types of text strings, as illustrated below:

Then, we will see how the PROPER function affects different data types. Although we already know that the process primarily works on text values, it is essential to understand how other data formats are affected. Hence, you know how to tackle such situations if any arise.

The formula that we will use in the spreadsheet is

=PROPER(B3)

and drag is down up to cell C13, which will give you the result as:

As you can see, all the words always have a capitalized first letter, followed by lowercase letters. If the rumors are separated by space, the logic repeats, i.e., the first letter will be in uppercase followed by lowercase letters.

You can also see that if most letters are capitalized, excluding the first, they are set to lowercase. So, for example, 'MONEY' in cell B4 changes to 'Money.'

Another thing that you can notice is that if a particular character is used in the sentence, then the function will capitalize the letter after the unique character. For example, if you have 'mo#ney' and use the process on the text, you will get the result 'Mo# Ney'

The numbers are entirely unaffected by the function; however, if you reference the date or time inside the PROPER function, it will return the serial number for the data and decimal value (0-1 for a 24-hour clock), respectively.

Practical Example 1

We bet you've seen this several times, "Are you working on Excel? Try this! TikTok video." The essence of those videos is that you can directly make the changes using a single formula.

This is a textual representation of the same. For example, suppose you have the employee names in Excel:

Here, rather than changing the value 'JOSHua MilLEr' to 'Joshua Miller by typing it in cell B3 and then changing the next value and so on, you can directly use the formula

=PROPER(B3)

in cell E3 and drag it down till E17 which will give you the result:

Once you get the properly formatted text values, you can PasteSpecial them into the B column using the keyboard shortcut of Ctrl + C first and then Ctrl + Alt + V +V into cell B3.

Practical Example 2

If you don't like using the PROPER function on the text now and then, we have a VBA code that you can use to convert the text into its 'proper' format automatically.

• Go into the Developer tab and click on Visual Basic.
• Click on Sheet1 under the VBA Project (or any other sheet on which you need to return the text values in their proper format.
• Change the object to 'Worksheet' and procedure to 'change' in Sheet1. This will open up a Private Sub for you in the VBA.
• You must type in the code below and save the file as Macro Enabled Workbook.
• Click on any cell and type the text in either entirely uppercase or lowercase, and you will see that Excel returns the text in the correct format.

Practical Example 3

You already know that you can't reference two different cells inside the PROPER function. But what if you receive an Excel file where the first and last name needs to be reformatted?

Do we individually reference those cells into the PROPER function and then concatenate the result? Not!

Suppose that you have the data as illustrated below:

Then, we will use the formula

=PROPER(B3&" "&C3)

which will concatenate the result for both cell B3 and C3 and return it to cell D3. Yes, you can use a little hack if the text strings are in different cells. After using the formula, the spreadsheet will look as illustrated below:

Researched and Authored by Akash Bagul | Linkedin

Reviewed and edited by Parul Gupta | LinkedIn

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• HYPERLINK Function
• IFERROR Function
• MAX Function
• MINIFS Function
• NORM.DIST Function