PROPER

What does this function do?

This function converts the first letter of each word to uppercase and all subsequent letters to lowercase.

The original case of letters doesn’t matter and is not taken into account.

Any character that is not a letter is considered a word separator, meaning it can be not only a space but any punctuation mark, digit, or special character.

Syntax

=PROPER(text)

Formatting

Like all text functions, the function converts numeric values to string format. Dates and date-time will become natural numbers in text format, percentages will be converted to decimal fractions, and financial values will lose both the unit of measurement and characteristic formatting.

This should be taken into account when processing large data arrays where cells may contain numeric values in addition to text.

The string format on output is easily recognized – the result of applying the function in a cell is left-aligned, unlike numbers which are right-aligned.

Usage example. Make first word in cell capitalized

How to make only the first word capitalized in Excel? Not all words, but only the first? The PROPER function will help us with this, but in combination with others.

The task is solved algorithmically in several simple steps:

1. Extract the first word from the original string
2. Apply the PROPER function to it
3. Extract from the original string the part remaining after the first word (in other words, remove the first word from the cell and look at the result)
4. Combine these two fragments

The first step is described in detail here in example 1:

Extract first word from cell in Excel using LEFT

The formula for cell A1 would be:

=LEFT(A1, SEARCH(" ", A1) - 1)

The second step is to apply the PROPER function to this expression:

=PROPER(LEFT(A1, SEARCH(" ", A1) - 1))

The third step is here:

Remove first word in Excel cell

The formula here would be:

=REPLACE(A1, 1, SEARCH(" ", A1), " ")

The fourth step – combining these expressions – can be done using the ampersand symbol:

PROPER(LEFT(A1, SEARCH(" ", A1) - 1)) & REPLACE(A1, 1, SEARCH(" ", A1), " ")

But unfortunately, this formula is not yet perfect and won’t work if the cell contains only one word (it will return a search error). Therefore, we’ll need to wrap it with the IFERROR function and apply the regular PROPER function in case of such an error:

=IFERROR(PROPER(LEFT(A1, SEARCH(" ", A1) - 1)) & REPLACE(A1, 1, SEARCH(" ", A1), " "), PROPER(A1))

Is this formula perfect for all cases? Unfortunately, no, because it inherits an undesirable feature of the PROPER function. It changes the case regardless of the original.
For example, if the first word is an acronym and all letters in it are uppercase, the function will change the case of all letters in the acronym except the first to lowercase.

Is there a solution to this problem? Yes, my add-in offers it.

Read more about how to change character case and get the desired result: change case in Excel.

This post is also available in RU.