Video instructions and help with filling out and completing Can Form 2220 Columns

Instructions and Help about Can Form 2220 Columns

Hello and welcome to another tutorial video this time around we're going to be looking at an excel related topic which is how to clean up theta now over here in the screen I have some address data so these might have been addresses from customer orders or addresses from employees or customers in our database or something like that but in any case the address data is all here these are all US addresses and you can look at it and you can see that it's really horribly formatted the spacing is all off there extra spaces some of the states are capitalized some are not capitalized some are partially capitalized the zip codes are all over the place there are leading spaces in the beginning so bottom line is that there are a lot of problems with this data and what we like to do when we get data like this is go in and make it easier to manipulate and then fix all these problems so fix the spacing make it so that we can separate out all the different data so we have the street address and then the city name the state and the zip code all in different columns and go and make it much easier to manipulate and analyze like that and of course the reason why this happens the reason this topic is relevant is because oftentimes you'll be pasting in data from websites or PDFs or other sources and you get a lot of these problems you get a lot of ugly formatting you get a lot of extra spaces non-printable characters other things like that another issue is as is the case here in a lot of cases you may have data grouped together in a way that is not very useful and this happens all the time on the job in finance clients will send you documents and data that are horribly formatted that are extremely user unfriendly because they're not used to always outputting the data and easy to use and manipulate format so if you go in and take a few minutes to fix it it makes it a hundred times easier to work with it makes your whole life a whole lot easier when you're using it in models analysis later on and as I say here one option would be to go in and manually fix it so we could go over and we could say you know what forget about doing this the smart way let's just go in and delete some of these spaces right here and there we go the only problem is that sure if you have say 10 or 11 or 12 okay that's a problem but what if you had more like I just opened another file here with other address data what if you had something like a thousand and one addresses is that something that you actually want to go in and do manually probably not you probably want to jump off the roof of a tall building if someone asks you to go do something like that so to avoid committing suicide you probably want to learn about how to properly format this data in Excel using built-in functions that will save you a lot of time and that's we're going to cover in this lesson we're going to go over a couple of these key functions I have them listed over here on the side trim is for removing extra spaces propper is for making the first letter in each word uppercase clean removes non-printable characters next upper and lower capitalized or make all letters lowercase in all words in the text you've selected and then value in text we're not really going to look at those here but those are other very useful text manipulation functions and then down here I have a couple of the other functions and shortcuts we're going to be using text to columns copy and paste formulas and values so that's we're going to be covering in this lesson now what I want to do is just jump into this exercise and show you how to do this so the first thing we want to do here is we want to remove all the extra spaces and capitalize each individual word with trim and proper we could even add clean for good measure although we don't actually need it here so to do this let's first start by creating some extra space and I'm just going to select this column with ctrl spacebar and then I can press ctrl shift plus a couple times to add some extra column space here and let's go and do this so I'm going to type proper trim clean and then apply it to this data closed out ctrl C and then a bunch of waiting copy this down I prefer alt ESF even though it's from an older version of Excel and this certainly looks a lot better so we have now fixed this issue with the tons of extra spaces and we still need to fix some more but we're making progress we've made progress here and this looks much better than the initial data that we were starting with so we're doing well but now the next step as I say here is we need to separate everything into columns because first off we want to have the state's the zip codes the string addresses and so on in different columns and then second we still have some issues if you look at the underlying data for example the state abbreviations these are not all consistently capitalized some of them are partially capitalized some them or not so we need to go in and fix that the first thing we do here is we actually copy and paste everything here as values and the reason this is important and I have a note down here that you have to