How can you change names to proper case, with the first letter of each name capitalized? Episode 671 shows you a few different ways to solve this problem.
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!
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!
Transcript of the video:
Hey welcome back to the MrExcel netcast.
I'm Bill Jelen.
Interesting question came in. Someone said.
Hey, I have a huge database and have a list of names over in column A and I want to convert that to proper case.
Is there any way to do it?
Well, You know there's a couple of ways to do that. I'm going to make a copy of this worksheet.
We'll talk about the different ways.
So first of all, the way that I would attack this is I insert a new column so insert column and then use the proper function =PROPER I want to copy that formula down to all of my cells.
So I double click the fill handle and then we just Ctrl C to copy and "Edit" "Paste Special" "Values".
Ofcourse if you're in excel 2007 this is "Home" "Paste" "Paste values" and then take that data and replace the original cells, then delete the extra column.
Really, it doesn't take that long to go through and do it.
Now there's people that sell you utilities Bob Flanagan and it add-ins and Jaywalk has his utility that will basically go through and take those capitals and convert them to proper case.
If you don't like the hassle of inserting the extra column, we could do this with a couple of lines of code.
Let's take all of our cells. Will select those cells.
I'll flip over to VBA, will insert a new module.
For each cell in seletion.
cell.value = application.WorksheetFunction.Proper(Cell.value) Next cell Alright, so we have our selection there.
Run the "Go" Macro and it will in place very quickly copy all those.
So there you go couple lines of code.
I'm sure we can streamline that code but if you had a bunch to do well you could even put this in your personal macro workbook.
Select the range hit the hot key and it will convert all your capitals to proper values.
Hey, thanks for stopping by. We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Interesting question came in. Someone said.
Hey, I have a huge database and have a list of names over in column A and I want to convert that to proper case.
Is there any way to do it?
Well, You know there's a couple of ways to do that. I'm going to make a copy of this worksheet.
We'll talk about the different ways.
So first of all, the way that I would attack this is I insert a new column so insert column and then use the proper function =PROPER I want to copy that formula down to all of my cells.
So I double click the fill handle and then we just Ctrl C to copy and "Edit" "Paste Special" "Values".
Ofcourse if you're in excel 2007 this is "Home" "Paste" "Paste values" and then take that data and replace the original cells, then delete the extra column.
Really, it doesn't take that long to go through and do it.
Now there's people that sell you utilities Bob Flanagan and it add-ins and Jaywalk has his utility that will basically go through and take those capitals and convert them to proper case.
If you don't like the hassle of inserting the extra column, we could do this with a couple of lines of code.
Let's take all of our cells. Will select those cells.
I'll flip over to VBA, will insert a new module.
For each cell in seletion.
cell.value = application.WorksheetFunction.Proper(Cell.value) Next cell Alright, so we have our selection there.
Run the "Go" Macro and it will in place very quickly copy all those.
So there you go couple lines of code.
I'm sure we can streamline that code but if you had a bunch to do well you could even put this in your personal macro workbook.
Select the range hit the hot key and it will convert all your capitals to proper values.
Hey, thanks for stopping by. We'll see you next time for another netcast from MrExcel.
