How to Use the PROPER Function in Excel? 3 Easy Examples
(Note: This guide on how to use the PROPER function is suitable for all Excel versions including Office 365)
The PROPER function in Excel capitalizes the first letter of each word in a given string. It means that the first character is in uppercase while the rest of the characters are in lowercase. In particular, this function is applied to a person’s name or place to capitalize the first letter. It is categorized in the Text function in Excel.
You’ll Learn:
- Syntax
- Parameter
- When to Use the PROPER Function?
- How to Use the PROPER Function in Excel?
- How to Pass the Arguments to the PROPER Function?
- Points To Remember
Syntax
| =PROPER(text) |
Parameter
The PROPER function takes a text enclosed in quotation marks, a formula that returns text, or a reference to a cell containing the text that you want to capitalize.
Related:
- Excel DATEVALUE – A Step-by-Step Guide
- How to Fix the #Div/0 Error in Excel? 2 Easy Methods
- How to Use ISTEXT in Excel? With 3 Different Methods
When to Use the PROPER Function?
The PROPER function finds its purpose in formatting:
- Names of the person
- Places
- Companies
- Trademarks
- Days and months
- Seasons
Such details are obtained from different sources and the PROPER function is used to maintain consistency. The basic formatting method is to capitalize the first letter of the names of people, places, and the list mentioned above.
How to Use the PROPER Function in Excel?
The Proper takes just one argument, the text, which can be a text value or cell reference. First, it turns the uppercase letters into lowercase letters and then capitalizes each word in the provided text string. While converting, the numbers, punctuations, and spaces are not affected.
The Proper function analyzes non-text characters in a string. It capitalizes on the next following character when it finds a non-text character. For example, if you use the Proper function on the text – Tom’s car, it will give the result as Tom’S Car as it capitalizes the character after the non-text character. Here, it sees the non-text character, the ‘apostrophe’, and capitalizes the following character which is ‘S’.
PROPER Function with Date
The Proper function formats the date, if a date is passed as an argument. The date format is lost and it will return the date as a number as the dates are represented as serial numbers in Excel’s date system.
January 1, 1900, is considered to be the starting date and represented as number 1. And, January 1, 1901, is represented as number 366. The more recent dates are much larger numbers, such as January 1, 2010, which is 40179 in number.
Similarly, in the example below, the date 29-05-2022 is represented as the number 44710.
Suggested:
- How to Use Excel Split Screen? 3 Simple Ways
- How to Create a Venn Diagram in Excel? 2 Easy Ways
- How to Create a Step Chart in Excel?
How to Pass the Arguments to the PROPER Function?
The Proper function takes the text directly as the argument or the cell reference. In the example below, the text to be capitalized is given as the argument to the function and it returns the result with the first letter capitalized.
You can also use the cell reference to identify what to capitalize.
The Proper function can be invoked by clicking on the Formula -> Text -> Proper and mentioning the argument as shown below. Instead of mentioning the function in the formula bar, either the text or the cell reference is mentioned in the dialog box.
Combine Multiple Columns
The Proper function can combine two or more columns to capitalize the first letter of the text in each column. In the example below, the first letter of the first name and the last name is capitalized, combined, and displayed.
In the example below, the concatenation operator ‘&’ combines columns A and B, and the quotation marks “ “ are used to display space between the first name and the last name when it is combined.
The first letter of the day of the week and the month name are also capitalized and combined using the ‘&’ operator and the result is displayed.
In the example below, the company names are capitalized and displayed.
Combine With the Trim Function
You can combine the Proper function in Excel with the Trim function to remove the extra space in the given text. The Trim function removes any leading and trailing spaces in the given text and converts the series of spaces into a single space.
Also Read:
- Short Date Format in Excel – 3 Different Methods
- How to Graph a Function in Excel? 2 Easy Ways
- How to use EDATE in Excel? 11 Easy Examples
Points To Remember
- The function converts the ‘s’ in the text Tom’s car to uppercase (i.e Tom’S Car).
- Numbers, special characters, and punctuations are not affected.
- The Proper function changes only the first character of every word in a string. All the other characters are unchanged.
- If you use a null character or refer to an empty cell, it will return a null character.
Closing Thoughts
The PROPER function in Excel capitalizes the first letter of the text and displays it. The function takes a text, a sentence, or a cell reference to capitalize the first letter.
In this article, we saw what would happen to the date if it is passed as an argument to the function. We also saw how to combine two columns and trim the extra spaces in the string.
If you need more high-quality Excel Guides, please check out our free Excel resources center. Simon Sez IT has been teaching Excel for ten years. For a low, monthly fee you can get access to 130+ IT training courses. Click here for advanced Excel courses with in-depth training modules.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!