When you import a list of customer names from a website form into Microsoft Excel, the formatting is often a disaster. Customers frequently type their names entirely in lowercase letters (“john smith”) or entirely in screaming uppercase (“JOHN SMITH”). If you intend to use this data for a professional mail merge or an email marketing campaign, sending an email to “dear JOHN SMITH” looks incredibly unprofessional. Instead of manually retyping thousands of names, you can instantly standardize the capitalization using the PROPER function.
How the PROPER Function Works
The PROPER function is a specialized text-formatting formula. It scans a text string, identifies the very first letter of every single word within that string, and forces that specific letter into uppercase. It simultaneously forces every other subsequent letter in the word into lowercase.
The syntax is incredibly simple: =PROPER(text)
- text: The cell containing the improperly formatted text (e.g., A2).
How to Capitalize Names Automatically
Imagine column A contains 5,000 messy customer names.
- Create a new, blank column immediately to the right of your data (e.g., Column B).
- Click into cell B2 and type:
=PROPER(A2) - Press Enter.
If cell A2 contained “johN sMiTh”, Excel will instantly evaluate the string, capitalize the J, lowercase the rest of the first name, capitalize the S, and lowercase the rest of the last name, outputting a perfectly formatted “John Smith”.
To apply this to your entire database, click on cell B2 again. Double-click the small green square (the Fill Handle) in the bottom right corner of the cell. Excel will copy the formula down all 5,000 rows in less than a second.
Limitations of the PROPER Function
While the PROPER function is perfect for 95% of standard names, it operates on very strict, blind logic: it always capitalizes the first letter after a space, and it always lowercases everything else. This means it struggles with complex names that require internal capitalization.
For example, if you feed it the name “McDonald”, the function will incorrectly output “Mcdonald”. If you feed it the name “O’Connor”, it will actually output “O’connor”. If your database contains a large number of these complex surnames, you will need to manually review the final output or use advanced nested IF statements to handle the exceptions.