Summary
The Excel PROPER function capitalizes each word in a given text string. This function is particularly useful for formatting names and addresses or for correcting capitalization issues in text.
Syntax
=PROPER(text)
- text: The text string you want to convert, where each word is capitalized.
Return value
A text string with the first letter of each word in uppercase and all other letters in lowercase.
How to use
PROPER is commonly used in data cleaning and preparation, especially when dealing with user-input data, like names, where consistent capitalization is needed. It helps in ensuring a standardized format for text data.
Examples
Simple PROPER
Capitalizing Words in a Text String: To capitalize each word in a text string:
=PROPER(A1)
If A1 contains “john doe”, this formula returns “John Doe”.
PROPER with CONCATENATE
Combining PROPER with CONCATENATE: Creating title-cased concatenated strings:
=PROPER(CONCATENATE(D2, " ", E2))
Merges the contents of D2 and E2 and converts them to title case.
PROPER for Uniform Formatting
Ensuring Uniform Text Format in Lists: Using PROPER for consistent text format across a dataset:
=PROPER(F2)
Especially useful in scenarios where text data, such as names, come from different sources and need standardization.
Additional Notes
- PROPER doesn’t affect numbers or punctuation and doesn’t capitalize after an apostrophe.
- For converting text to uppercase or lowercase, use UPPER or LOWER functions respectively.
- PROPER can be combined with TRIM to remove extra spaces while capitalizing words.