Gary asks about converting text from lower case to upper case an vice versa. Episode 533 shows you how.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Alright! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today, we have a question sent in by Gary from England.
If you have a question for the podcast please either leave a voicemail or an email and we'll get to you on a future podcast.
Gary wants to know, if there's any way to change text that's all uppercase to lowercase or vice versa.
There's three great functions that will help with this, here I have data in column A that is all upper case.
I'm going to use a function in column B called the lower function, equal LOWER of A1.
I want to copy that down to all of the cells in the column, so I'll double click the fill handle and you'll see that it converts the uppercase to lowercase.
There's also another function called the upper function, equal UPPER of B1, will convert lowercase back to uppercase.
The other interesting function that we have is called the proper function and this would be great if you had a series of names for example, if we put Bill Jelen in and then use =PROPER(D1), it will convert to proper case that is the first letter of every word will be capitalized.
So you have =LOWER, =UPPER and =PROPER.
Now, Doug's question was actually, "How do I change the text in column A from uppercase to lowercase?" Once you've created that formula, you can copy the formula and paste special values.
So Edit , Paste Special Values, that of course, the quick way to do that is just to it select the formula, right click on the border, drag it to the new location and when you let go say copy here as Values Only, that will replace the original values in the column with the new values from the formula.
You can then go through and delete all the extra formulas.
So, there you have it.
The upper lower and proper functions in order to convert case of your text in Excel.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Today, we have a question sent in by Gary from England.
If you have a question for the podcast please either leave a voicemail or an email and we'll get to you on a future podcast.
Gary wants to know, if there's any way to change text that's all uppercase to lowercase or vice versa.
There's three great functions that will help with this, here I have data in column A that is all upper case.
I'm going to use a function in column B called the lower function, equal LOWER of A1.
I want to copy that down to all of the cells in the column, so I'll double click the fill handle and you'll see that it converts the uppercase to lowercase.
There's also another function called the upper function, equal UPPER of B1, will convert lowercase back to uppercase.
The other interesting function that we have is called the proper function and this would be great if you had a series of names for example, if we put Bill Jelen in and then use =PROPER(D1), it will convert to proper case that is the first letter of every word will be capitalized.
So you have =LOWER, =UPPER and =PROPER.
Now, Doug's question was actually, "How do I change the text in column A from uppercase to lowercase?" Once you've created that formula, you can copy the formula and paste special values.
So Edit , Paste Special Values, that of course, the quick way to do that is just to it select the formula, right click on the border, drag it to the new location and when you let go say copy here as Values Only, that will replace the original values in the column with the new values from the formula.
You can then go through and delete all the extra formulas.
So, there you have it.
The upper lower and proper functions in order to convert case of your text in Excel.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
