From the course: Excel: Advanced Formulas and Functions

UPPER, LOWER, and PROPER

From the course: Excel: Advanced Formulas and Functions

Start my 1-month free trial

UPPER, LOWER, and PROPER

“

- [Instructor] When we're working with data, it can show up in all kinds of ways that require some type of cleanup. Here we're going to go through upper, lower and proper. And what I really want to get at is to show you some traps, some things to watch out for. Let's look at the data real quick. We've got the name of a hotel, Apple Hotel Shoreline, AuraLaura Studios, celia hornet, all lowercase, got de la Croix, lowercase d, lowercase l, capital C, Dubuffet, ronald g. murray is all lowercase, Tatum is proper case, but then KEYES is all caps. So let's go and do an upper. What I really want to do right now is put this into a table. I'm going to highlight here and then go home. Format as table. Let's grab this nice green apple color. Okay, table does have headers. All right, good, get rid of the filter buttons. Click here. Upper equals upper. Open parentheses. Highlight the value, Enter. Let's look at what it did. Everything is all caps. Okay, before we review, let's go and do lower equals lower, double click, @Value, and Enter. Okay, everything is all lowercase. Now proper equals proper double-click, value and Enter. I really want to stress that you have to know your data. Let's say that we know LauraAura Studios, that's the right way it should be in B 4 with a capital A, capital L, upper, lower, and proper did not obey that, and there isn't a way to tell them that. You've got to know your data to go back into the data and check for these kind of things. And then, let's say celia hornet, that's kind of a branding thing, and we know celia hornet spells her name all lowercase. Then look at de la Croix. Of the cross, okay? So cross would be capitalized. We don't want the proper version with the capital D, capital L. Look at PO Box. Proper gave us an o. No, that should be capital because it stands for post office box. Notice in cell E 13, PhD., properly it would be capital P, lowercase h, capital D. But proper tried to help us. So those are some of the things that you have to really watch out for. If you had a whole lot of names, and I used to have to deal with this, and they're coming in in mixed cases, uppercase, lowercase, proper case, you can apply proper and clean that up, but you have to know your data to know to go back in and correct things like de la Croix and AuraLaura Studios and PO Box.

Contents