PROPER function

The PROPER function in Excel is used to capitalize the first letter of each word in a text string, while making all other letters lowercase. This is particularly useful for formatting names, titles, or any text where each word should start with a capital letter, like “John Doe” or “The Great Gatsby”.

Syntax:

=PROPER(text)

Parameters:

• text (required): The text string or cell reference containing the text you want to capitalize.

Key Points:

• Capitalize First Letter of Each Word: The function capitalizes the first letter of each word and changes all other letters to lowercase.
• Words Defined by Spaces: The function treats any sequence of characters separated by spaces as a “word,” capitalizing the first letter of each sequence.
• Non-Letter Characters: Punctuation and numbers are not affected by the PROPER function. It only changes the letter case of alphabetic characters.

Example Usage:

1. Converting a Full Name to Proper Case

If cell A1 contains the text “john doe”, applying the formula:

=PROPER(A1)

This will return:

John Doe

The first letter of each word (“John” and “Doe”) is capitalized, and the remaining letters are made lowercase.

2. Handling Mixed Case Text

If cell B1 contains the text “tHis IS a tEsT”, applying the formula:

=PROPER(B1)

This will return:

This Is A Test

Each word has its first letter capitalized, and the rest are converted to lowercase.

3. Multiple Spaces Between Words

If cell C1 contains the text “hello world”, applying the formula:

=PROPER(C1)

This will return:

Hello World

PROPER automatically handles extra spaces and still capitalizes the first letter of each word.

Notes:

• Multiple Words: The function handles multiple words separated by spaces. It will capitalize the first letter of every word, even if there are extra spaces between them.
• Non-Alphabetic Characters: The function does not alter numbers or punctuation marks, and will leave them as they are.
• Exceptions: Common words like “and”, “or”, “the”, and “in” are still capitalized, even though they are typically not capitalized in titles or headlines. If you need more control over capitalization in titles, you might need a custom formula or manually adjust the text.

Related Functions:

• UPPER: Converts all text in a string to uppercase.
• LOWER: Converts all text in a string to lowercase.
• TEXT: Allows you to format numbers, dates, and text with specific formatting.
• TRIM: Removes extra spaces from text (useful in conjunction with PROPER when dealing with inconsistent spacing).

The PROPER function is particularly useful for ensuring consistent capitalization in names, titles, and other text fields that need to follow standard case conventions.