Hi, this is Wayne again with a topic “Fixing Improper Capitalization in Excel Using the Proper Function”.
In this short excel tutorial, I want to show you a great function. That’S one of the lesser-known functions in Excel and it’s the function called proper. Now, what proper does is it fixes problems like what we have here in column? A let’s say. I recently received this spreadsheet from somebody else and it’s obviously someone that didn’t follow the conventions of writing in English.
You can see that some of these movie titles are in all caps. Others are in all lowercase. Others follow some crazy other formula for what to capitalize and what not to capitalize so fixing. This is going to be pretty hard for me and pretty time-consuming unless I know about the proper function. So, let’s take a look at how to fix these capitalization irregularities in Excel. The first thing I would do is I would right click on the column to the right of the column that has the problem so I’ll right, click there on the column letter and choose insert to get a blank column, that’s right! Next to the column with the problem and then I’ll just go to the first available cell I’ll type in the equals sign and the word proper, then I’ll put the left parenthesis and I need to select a cell that has improper capitalization. So here it is cell a2. I just click there. I should put in a right parenthesis, but it’s not necessary, so I’m not going to do it and then I tap enter on the keyboard and look what it did.
It took this text and made it proper. Now the good news is, I don’t have to do that same formula again and again and again for each of these cells that have a problem instead, all I have to do is click on the formula that I just did, and it looks like words. So if you doubt it’s a formula, you can just double click on it and you’ll see the formula.
Of course. You can also look here at the formula bar, but anyway, I’m just going to click on it and then go to the lower right corner of that cell. You can see there’s a little green square and if you put your mouse pointer right on that square, you should get a black plus sign.
Not a white plus sign, but a black plus sign just click and hold the click and then drag down the page. And then release the mouse click and look what it did. It took all of my text and it made it all.
Proper watership down is now capitalized correctly Chicago same thing: it’s no longer all caps, it fixed elf and made that proper notice. This one here Uncle Buck, that’s all lowercase but proper, fixed it and made it capitalized, but not all caps. We do have an interesting case here, though, with Back to the Future 2, and you probably can tell why that that was a problem.
It’S because whoever made this spreadsheet put two eyes in a row upper case and that’s how they wrote to Back to the Future 2, and so the proper function thought that that second eye should not be capitalized. So every once in a while proper does make a mistake, but in this case it’s a very logical mistake, ok, great now, the only problem is. I have two columns that are duplicates one that has lots of improper capitalization and one that is really good.
You’Re gon na be tempted to right-click and delete column a. But if you do look, what happens you mess up the data in the column that you fixed so instead, this is what you should do: click on column B to highlight the entire column, and then you can right click copy and then right, click on column a But don’t paste if you do, there will be some errors instead go to paste special and click and switch it from paste all to paste values. So it’s gon na paste, just the values from column B, not the formulas.
So I click on values, click, OK, and now these are values. They are not formulas. If you look in the formula bar there’s no formula there, it’s just text copied from column B. So at this point I can right click and delete column B. Don’T need that anymore, and now I have the movie titles and they’re not dependent at all on any other column or data, so I’ve cleaned up the irregular capitalization thanks for watching this video, I hope you found it to be useful if you did.
Please click the like button below and consider connecting with me on my social media accounts like Facebook, Pinterest and Twitter, and definitely do subscribe to my youtube channel for more videos about technology for teachers and students and when you do subscribe, please click the bell icon. Next to the subscribe button, if you do that, you’ll be notified whenever I post another video and watch for another video from me at least every Monday, if you’d like to support my youtube channel, please become a patron of mine through my patreon account and you’ll find A link to that in the description below .