Microsoft Word gives you a command to convert text to Upper or Lower case. There is no such command in Excel. Today, in Episode #1356, Bill shows us one of two methods for converting text to Upper Case, Lower Case, or Proper Case in Excel.
...This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
For all of your Microsoft Excel needs visit MrExcel.com -- Your One Stop for Excel Tips and Solutions.
...This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
For all of your Microsoft Excel needs visit MrExcel.com -- Your One Stop for Excel Tips and Solutions.
Transcript of the video:
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.
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.
