✨ Part 1: Introduce
The PROPER function in Microsoft Excel is a text function that capitalizes the first letter of each word in a given text string. It is commonly used to format names, titles, or any text requiring proper capitalization.
🎯 Purpose: The purpose of the PROPER function is to convert text to the proper case by capitalizing the first letter of each word and converting the remaining letters to lowercase.
📚 Syntax & Arguments: The syntax of the PROPER function is as follows:
=PROPER(text) text: This is the required argument representing the text string or cell reference you want to convert to the proper case.
🔍 Explain the Arguments in the function:
text: It can be either a text string enclosed in double quotation marks (“”) or a reference to a cell containing the text you want to convert to a proper case.
💡 Return Value: The PROPER function returns a new text string with the first letter of each word capitalized and all other letters converted to lowercase.
📝 Remarks:
- The PROPER function does not capitalize letters that follow numbers, symbols, or punctuation marks.
- If the text string contains numbers, the PROPER function treats them as part of the word and capitalizes the first letter after the number.
✨ Part 2: Examples
Let’s walk through three examples to illustrate the usage of the PROPER function:
1️⃣ Example 1: Capitalizing Names
| A | B | |
|---|---|---|
| 1 | Name | Proper Case |
| 2 | john doe | =PROPER(A2) |
| 3 | MARY JANE | =PROPER(A3) |
| 4 | mike smith | =PROPER(A4) |
In this example, we have a list of names in column A. We use the PROPER function in column B to convert the characters to proper cases. The formula =PROPER(A2) in cell B2 capitalizes the first letter of the name “john doe” to “John Doe”. Similarly, the words “MARY JANE” and “mike smith” are converted to “Mary Jane” and “Mike Smith,” respectively.
2️⃣ Example 2: Formatting Titles
| A | B | |
|---|---|---|
| 1 | Title | Proper Case |
| 2 | the great gatsby | =PROPER(A2) |
| 3 | to kill a mockingbird | =PROPER(A3) |
| 4 | lord of the rings | =PROPER(A4) |
In this example, we have a list of book titles in column A. We use the PROPER function in column B to convert the titles to proper cases. The formula =PROPER(A2) in cell B2 converts the title “the great gatsby” to “The Great Gatsby.” Similarly, the labels “to kill a Mockingbird” and “Lord of the Rings” are converted to “To Kill a Mockingbird” and “Lord of the Rings,” respectively.
3️⃣ Example 3: Combining with Other Functions
| A | B | |
|---|---|---|
| 1 | Text | Modified Text |
| 2 | hello world | =PROPER(LEFT(A2, 5)) & RIGHT(A2, 5) |
In this example, the text string “hello world” is in cell A2. We use the PROPER function in combination with other parts. The formula =PROPER(LEFT(A2, 5)) & RIGHT(A2, 5) in cell B2 converts the first five letters of the text string to the proper case using the PROPER function and then appends the remaining five letters as they are. The result is “Hello world.”
4️⃣ Example 4: Formatting Customer IDs
| A | B | |
|---|---|---|
| 1 | Customer ID | Formatted Customer ID |
| 2 | cust123 | =PROPER(“cust” & RIGHT(A2, 3)) |
In this example, we have a list of customer IDs in column A. We want to format them by adding the prefix “cust” and capitalizing the last three digits. The formula =PROPER("cust" & RIGHT(A2, 3)) in cell B2 concatenates the prefix “cust” with the last three digits of the customer ID and converts it to the proper case. The result is “Cust123.”
5️⃣ Example 5: Generating Email Addresses
| A | B | |
|---|---|---|
| 1 | First Name | Generated Email |
| 2 | john | =LOWER(LEFT(A2,1)) & PROPER(RIGHT(A2,LEN(A2)-1)) & “@company.com” |
In this example, we have a list of first names in column A, and we want to generate email addresses by combining the first name with a domain name. The formula =LOWER(LEFT(A2,1)) & PROPER(RIGHT(A2,LEN(A2)-1)) & "@company.com" cell B2 converts the first letter of the first name to lowercase, capitalizes the remaining letters, and appends “@company.com” to create the email address. For example, if the first name is “john,” the formula generates “[email protected].”
6️⃣ Example 6: Formatting Dates
| A | B | |
|---|---|---|
| 1 | Date | Formatted Date |
| 2 | 2023-01-15 | =PROPER(TEXT(A2,”dd mmmm yyyy”)) |
In this example, we have a list of dates in column A, and we want to format them as “Day Month Year.” The formula =PROPER(TEXT(A2,"dd mmmm yyyy")) in cell B2 uses the TEXT function to convert the date to a specified format and then applies the PROPER function to capitalize the first letter of each word in the formatted date. The result is “15 January 2023.”
7️⃣ Example 7: Handling Product Codes
| A | B | |
|---|---|---|
| 1 | Product Code | Formatted Product Code |
| 2 | ABC123 | =IF(LEN(A2)=6,PROPER(LEFT(A2,3)) & RIGHT(A2,3),A2) |
In this example, we have a list of product codes in column A. We want to format the product codes by capitalizing the first three letters if the code is six characters long. The formula =IF(LEN(A2)=6,PROPER(LEFT(A2,3)) & RIGHT(A2,3),A2) cell B2 checks the length of the product code using the LEN function. If the length is six, it applies the PROPER function to the first three letters and combines them with the remaining three. Otherwise, it returns the original product code.
8️⃣ Example 8: Formatting Currency
| A | B | |
|---|---|---|
| 1 | Amount | Formatted Amount |
| 2 | 1500.75 | =PROPER(TEXT(A2,”$#,##0.00″)) |
In this example, we have a list of amounts in column A, and we want to format them as currency with proper case. The formula =PROPER(TEXT(A2,"$#,##0.00")) in cell B2 uses the TEXT function to format the amount as currency with two decimal places. It applies the PROPER function to capitalize the first letter of the currency symbol. The result is “$1,500.75.”
9️⃣ Example 9: Cleaning Data
| A | B | |
|---|---|---|
| 1 | Text | Cleaned Text |
| 2 | abc def ghi | =TRIM(PROPER(SUBSTITUTE(A2,” “,” “))) |
In this example, a text string in column A contains extra spaces. We want to clean the text by removing excess spaces and capitalizing each word. The formula =TRIM(PROPER(SUBSTITUTE(A2," "," "))) cell B2 uses the SUBSTITUTE function to replace consecutive spaces with a single space, applies the PROPER function to capitalize each word, and uses the TRIM function to remove any leading or trailing spaces. The result is “Abc Def Ghi.”
🔟 Example 10: Proper Case for Initials
| A | B | |
|---|---|---|
| 1 | Initials | Formatted Initials |
| 2 | j.d. | =PROPER(SUBSTITUTE(A2,”.”,”. “)) |
In this example, we have a list of initials in column A. We want to format the initials by capitalizing each letter and adding a space after each period. The formula =PROPER(SUBSTITUTE(A2,".",". ")) in cell B2 uses the SUBSTITUTE function to replace periods with periods followed by a space and applies the PROPER function to capitalize each letter. The result is “J. D.”
These examples demonstrate the versatility of the PROPER function when combined with other parts commonly used in business scenarios. You can achieve customized and formatted results in your Excel spreadsheets by leveraging nested functions.
✨ Part 3: Tips and Tricks
- If you want to convert the text in place (without using a separate column), you can use the Paste Special feature to overwrite the original text with the converted proper case text.
- If you have a range of cells with text strings and want to convert them all to proper cases, you can apply the PROPER function to the entire range using an array formula. Select the range of cells, enter the formula with Ctrl+Shift+Enter, and Excel will automatically apply the formula to each cell in the field.
- The PROPER function is case-insensitive, meaning it treats uppercase and lowercase letters the same when capitalizing the first letter of each word.
These tips will help you effectively utilize the PROPER function in Excel and enhance your text formatting capabilities.