Exceljet

Quick, clean, and to the point
 

This video comes from our online video training for Excel.

How to change case with formulas

Tags 
Summary 
If you want to change case in Excel, you'll need to use the UPPER, LOWER, or PROPER functions. Together with other functions they give you the tools to transform case.
Video Transcript 

If you want to change case in Excel, you'll need to use one of it's special text functions for doing so: UPPER, LOWER, or PROPER. Together with techniques like concatenation, they give you everything you need to transform case.

UPPER, LOWER, and PROPER

When you're working with text in Excel, you'll frequently need to change case.

In this video, we'll look at three functions that allow you to easily change case of text in Excel: upper, lower, and proper.

In this worksheet, we have two columns that contain names. Column B contains last names in uppercase text, and column C contains first names with the first letter capitalized.

In column D, I'll add a formula that capitalizes the first name using the UPPER function. The UPPER function takes just one argument, the text you want in upper case. When I add the formula and copy it down, we get all first names in uppercase text.

What if you need both first and last names in uppercase? To do that, you just need to concatenate the first and last names, with a space, inside inside the UPPER function.

This formula can be as flexible as you need. For example, I can easily reverse the names, and add a comma between them, instead of a space.

In column E, let's convert the names to lower case using the LOWER function. Just like UPPER, you only need to provide a single argument -- the text you want in lower case.

To take this example a bit further, let's assume you need to create an email address for each person in the list, using first and last names, separated by a period.

First, I'll add the domain name above the list to keep this information in only one place. I'll use the generic name "acme.com" for the domain. I'll also go ahead and name this cell "domain" to make the email address formula easier to read.

Now I can assemble the email address inside the LOWER function. Once I add the names, I need to add the "@", and the domain.

Technically, the domain doesn't need to be inside the LOWER function, since it's already lowercase, but there's no harm in putting it there.

Finally, let's try out the PROPER function. The PROPER function capitalizes the first letter only of each word you provide. It just takes one argument, and it will it will always convert all text to lower case before capitalizing, so it doesn't matter if the text you provide is uppercase , lowercase, or any combination of the two.

Using PROPER, I can simply concatenate the first and last names with a space, and get a full name that uses standard capitalization.

 

Author 
Dave Bruns