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"
Both TEXTBEFORE and TEXTAFTER have many options. For more information, see these links:
Related formulas
Remove leading and trailing spaces from text
Join first and last name
Get first name from name
Get last name from name
Get middle name from full name
Related functions
PROPER Function
The Excel PROPER function capitalizes each word in a given text string. Numbers, punctuation, and spaces are not affected.
TRIM Function
The Excel TRIM function strips extra spaces from text, leaving only a single space between words and no space characters at the start or end of the text.
TEXTBEFORE Function
The Excel TEXTBEFORE function returns the text that occurs before a given substring or delimiter. In cases where multiple delimiters appear in the text, TEXTBEFORE can return text before the nth occurrence of the delimiter.
TEXTAFTER Function
The Excel TEXTAFTER function returns the text that occurs after a given substring or delimiter. In cases where multiple delimiters appear in the text, TEXTAFTER can return text after the nth occurrence of a delimiter....
Related videos
