Hi, this is Wayne again with a topic “Excel Functions to Help Fix Bad Text”.
In this article, I will show you several Excel functions to help you clean up your text and, at the end, I’m going to throw in a bonus function that you may have never heard of or used before. Let’S get started so here I am in an Excel spreadsheet. I’Ve got a list of companies. These are some of the biggest companies in the world and you can see the list of companies here at the left has some problems. Somehow, when I got this data either the source didn’t follow, proper or typical, spacing or case rules like uppercase, lowercase or sometimes something happens in the import process, where the data may get mixed up or not interpreted properly.
However, this happened. I’Ve ended up with a list of companies, yes, but your typical text rules have not been followed, so let’s take a look at how to fix them. Let’S start by fixing the spacing problems that we have here, so I’m going to click on Cell B2 and type equals, so this cell equals and I’m just going to to type in trim, left parenthesis and then I’ll either click on A2 or I could just type A2 in the cell, I should put in a right parenthesis, but I don’t really have to and then I’m going to tap enter on the keyboard and this formula now is going to trim out the Extra Spaces that maybe at the beginning of this word and maybe After the word or between words like in this case, so I’ll tap enter and it fixed the problem, the Extra Spaces are gone now I can do the same thing for these other companies here, one of the quickest ways to do. That, though, is to just select the first cell where the trim function has been used and then just double.
Click on the autofill handle this little green Square in the lower right corner of the cell. So I’ll double click on that and it applies that same formula. All the way down the spreadsheet okay: this is an improvement. Next, let’s look at how to convert what we see here into all lowercase letters. There are times when you just need lowercase and that’s what you’re looking for. So how would you do that? You would just go to cell C2 type equals lower, left parenthesis and B2.
This time, I’m not going to put in the right parenthesis, I’ll tap, enter on the keyboard, and then I can select C2 double click on the green square, and now all of those companies are in lowercase. Let’S do the same thing, but for uppercase equals upper left. Parenthesis C2 tap enter and then I can click on Apple in this case in D2, double click on the green square. Now all of the companies are in uppercase, I’m going to click and drag to highlight all of the column, letters and then I’ll.
Double click between any one of these columns just to space this out a little bit better – and this is nice – I’ve been able to get rid of the Extra Spaces. I’Ve been able to make those company names all lowercase, all uppercase, which is very useful. In certain circumstances, but what if I just want typical Standard English rules for capitalization? Let’S, let’s look now at how to make the capitalization proper, so here on Cell E2, I’m going to click I’ll type equals proper. You probably guessed it left parenthesis and then I’ll select Apple D2 tap enter and then I can click on E2 double click on the green square and finally, now I do have all of the companies with proper capitalization with no Extra Spaces. Let’S look now at a bonus, Excel function that sometimes comes in handy we’re going to see if this text that started out improper here at the left, all sorts of problems we’re going to see if it now matches the official company names that we have here at The right, so I’m going to click here on F2 type, equals exact, left parenthesis and Excel, gives us a hint here as to what to put next Excel, wants one piece of text, comma and then another piece of text, and it’s going to compare the two. So E2, I’m just going to click on that comma H2.
I should put in my right parenthesis: tap enter on the keyboard and Excel reports that that is true. These are equal I’ll, just click on E2 and double click on the green square. The fill handle and I can double click between f and g, to make sure that that fits perfectly, and so now I can see most of the results match perfectly they’re, almost all true, but there are a few that because of how I used the proper tool, The capitalization doesn’t match up the official names of the companies, so here we have one. Here we have one.
The word of shouldn’t have been capitalized. Mobile should have been capitalized here so that does expose some minor flaws in the use of the proper function, and this exact function has really helped to point out those little flaws, I’m going to undo much of what I’ve just done by holding Ctrl and tapping Z. I’M going to do that a few times, because I want you to see that if, instead of just making these changes in a regular Excel range, what if I had clicked and dragged and turned this data into a table or maybe just started with it as a Table in my case, though, I’m going to convert that range that I’ve just selected to a table here on the Home tab in the Styles group. I have format as table and I’ll just pick. Let’S say orange my table does have headers. These are the headers here, I’ll click, OK and I’ll double click between any two columns to make sure that the text fits within the columns now watch what happens if I click here on B2 and type equals trim left parenthesis A2 tap enter. Not only does it Fix Apple, but it fixes all of the spacing all the way down. My spreadsheet. Everything in the table is fixed automatically, so I didn’t have to double click on the autofill handle same with lowercase just tap enter everything’s fixed same with uppercase proper. Just tap enter and also with exact, so that’s it’s a little added tip. If you want this to go faster, you can convert your data into a table thanks for watching this video on Excel functions to help clean up your text. I hope you found this tutorial to be helpful.
If you did please like follow And subscribe, and when you do click the Bell so you’ll be notified. When I post another video, if you’d like to support my channel, consider clicking the thanks button below the video or you can support me through my patreon account or by buying Channel merch and you’ll, see more information about those options. In the description below the video .