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’ll get all first names in uppercase text. What if you need both first and last names in upper case? To do that, you just need to concatenate the first and the last names with a space inside the UPPER function. This formula can be as flexible as you need. For example, I can easily reverse these 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 the UPPER function, you only need to provide a single argument: the text you’d like 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 a 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 using a formula. Once I add the names, I need to add the “@” symbol and the domain. Technically, the domain doesn’t need to be inside the LOWER function, since it’s already lower case, 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 always convert all text to lower case before capitalizing, so it doesn’t matter if the text you provide is upper case, lower case, 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.
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.
