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))

Formula to put names into proper case with last name first

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 function capitalizes each name:

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

Both TEXTBEFORE and TEXTAFTER have many options. For more information, see these links:

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.