Summary

To format names in "proper case" (i.e. first letter of each name capitalized) you can use a formula based on PROPER function. In the worksheet shown, the formula in D5 is:

``````=PROPER(B5)
``````

As the formula is copied down, it returns each name in column B formatted in proper case.

Generic formula

``=PROPER(A1)``

Explanation

The goal in this example is to reformat names that appear in mixed upper and lower case letters into "proper case", defined as each word in the name beginning with a capital letter. This can easily be done in Excel with the PROPER function.

PROPER function

The PROPER function automatically reformats text so that all words are capitalized. At the same time, it lowercases all other text. For example:

``````=PROPER("ben franklin") // returns "Ben Franklin"
=PROPER("ben FRANKLIN") // returns "Ben Franklin"``````

In the example shown, the formula in cell D5 is:

``=PROPER(B5)``

As the formula is copied down, it returns the names in column B with each word capitalized. In cases where a name is all uppercase, it converts the name to lowercase, and then capitalizes each word.

Removing extra space

If names contain extra space characters, you can normalize spaces and convert to proper case in one step by nesting the TRIM function inside PROPER like this:

``````=PROPER(TRIM("ben  franklin ")) // returns "Ben Franklin"
``````

The TRIM function removes leading and trailing spaces and converts runs of spaces to a single space. The result is returned to PROPER, which capitalizes each word as before.

Last name first

It is also possible to restructure the name so that the last name appears first, followed by the first and middle name as seen in the workbook below. The formula in cell D5 is:

``````=PROPER(TEXTAFTER(B5," ",-1)&", "&TEXTBEFORE(B5," ",-1))
``````

Working from the inside out, the TEXTAFTER function extracts the last name:

``````TEXTAFTER(B5," ",-1) // returns "JOHNSON"
``````

The TEXTBEFORE function extracts the first and middle names:

``````TEXTBEFORE(B5," ",-1) // returns "EMILY MARIE"
``````

Next, the two values are joined together with concatenation:

``````="JOHNSON"&", "&"EMILY MARIE"
="JOHNSON, EMILY MARIE"``````

Finally, the PROPER function capitalizes each name:

``````=PROPER("JOHNSON, EMILY MARIE")
="Johnson, Emily Marie"``````