Learn Excel – Changing Case in Excel: Lower, Upper, Proper: Podcast #1356

Learn Excel – Changing Case in Excel: Lower, Upper, Proper: Podcast #1356

Hey, welcome back to the MrExcel netcast. I’m Bill Jelen from MrExcel.com. Today’s episode 1356. We’re talking about Changing Case in Excel:
Upper, Lower and Proper. You know, one of the frustrating things with
Microsoft Excel is that there’s no easy way to change from upper to lower to proper. It’s there in Word, but it’s not there
in Excel. I’m going to show you two methods today. We’re going to talk about a formula method
and tomorrow we’ll look at a macro method. So, we have this customer name here and wherever
it came from, they just typed everything in lower case. I hate when that happens. I’d like to see if I can change that to
upper case. So I’m going to have to insert a new column. I always insert a column here, to the right. You can put it wherever you want. I’m going to right-click and say insert. So there’s our new column. And I’m going to put a formula in here called
‘=UPPER’. UPPER is one of Excel’s functions. It takes text and converts it to upper case. I’ll click on cell B2 and then a closing
parentheses. Now, I’m going to press Control Enter. Control+Enter puts that formula in and keeps
me right there in the current cell. I need to copy that formula down. The fast way to do that is go to this little
square dot on the lower right-hand corner of the cell, double-click and it shoots it
right down. Now, if you’re in Excel 2010, that’s certainly
a safe thing to do. If you’re in Excel 2007, you want to make
sure to copy it all the way down to the bottom, so we do Control down-arrow and just
make sure that it went all the way to the bottom of your dataset. Back in Excel 2007, sometimes a blank cell
would cause it to stop or something like that. But, hey, we’re not done yet. We have live formulas there and we need to convert
those formulas to their current values before we can move them back to column B. If we move them back to column B now, we’d
be in trouble. We’d have reference errors everywhere. So I want to choose all of that data again. Control+Shift+Down-arrow and I’m going to
copy that data. Either Control+C or the Copy icon up here
and then we want to do a special paste. A Paste Special and choose values. Now, if you’re in Excel 2010, you have all
of these icons. Choose this one for Paste Values. Back in Excel 2007, you want to look for the
item, the text that says Paste Values. And you’ll see here in the formula bar now,
that changed from a live formula to the actual value. I can now cut, Control+X or the Cut icon. And come back here to column B. Paste. And now, I can delete the Column C. Alright, so that’s the UPPER function. There’s two more functions you might like:
LOWER would take it back to lower case; and PROPER would take it to proper case. Let’s take a look at PROPER. ‘=PROPER(B2)’. Again, double-click the fill handle to shoot
it down and you see that we get the first letter of each word capitalised. It’s not perfect. Sometimes if you have an Irish name like McDonalds
or something like that, it doesn’t get that second… the letter after the small C. You
have to go through and fix those manually. But gets you pretty close. So again here, we would Copy, Paste, Paste
Values and then Cut and Paste over in column B. Great way to go. Delete the extra column C. Now tomorrow, in episode 1357, we’ll take
a look at a way to record a macro that you can then put on your quick access toolbar
to solve this problem. Well, hey, I want to thank you for stopping
by. We’ll see you tomorrow for another netcast
from MrExcel.

99 thoughts on “Learn Excel – Changing Case in Excel: Lower, Upper, Proper: Podcast #1356

  1. This was great! Would love to know if there was a way to convert text when it is combined with numbers as well. (such as a model #) 524HR623 vs 524hr623.
    Any suggestions?

  2. I still don't believe this. It's like "OK, here's Your car, but if You'd like to shift gears during driving, please pull over, open the gearbox with the supplied tools, and change gears. No problem! What? We already have a gear shifter in other cars of our make? Well, yes, but we kinda never thought You might need it in this car…" Disgrace. Is it really too much to ask for a simple "shift+F3" in the 21st century software?

  3. When I copy the formula downwards by clicking the corner of the cell, it just duplicates the same word as the cell all the way down.

  4. can´t you just format the text box form the beginning, so you don´t have to insert an ekstra collum and code.

  5. Tks, when you don't use excel on a regular basis, it's easy to forget formula's etc – I knew there was a way to change casement; but do you think I could remember!!! this makes it easy…

  6. Bear in mind that the source and also the cell where you are entering the formula must not be in "text" format – it should be general, number etc.

  7. I keep getting an error just pops up with #NAME, I am on a mac not sure if that makes a difference, please help!

  8. Maybe so, but people's names should always always always be in Mixed Case.

    The reason why it isn't is because we Humans are slaves to Zion.

  9. Bill this was an excellent tutorial and it was just what I needed to get a task accomplished at work…. Keep up the great work!

  10. Thanks so much for posting this video, it's going to save me a lot of time, I used to do it one by one. God Bless

  11. Hi, do you have a video sharing on query function? (If you had more examples on the usage of this function). I like your video, is clear and informative . Hope to hear from you soon. Thanks



  13. this vedio is a Very helpful for working at office work in excel sheet…. thank you so much for make this vedio….:)

  14. Awesome stuff, Bill. But for some strange reason double clicking the fill handle doesn't shoot down for me. I'm using Excel 10.

  15. Thanks a lot for this tips. Basically doing it one by one and it take so much time as you're concerned. This tips will saves a lot of time.

  16. Change case for letters to upper case or lower case or proper case or capitalize letters for Excel cells values using Dose for Excel Add-In, plus more than 100 new features and functions, check it out:

Leave a Reply

Your email address will not be published. Required fields are marked *