Viewers: in countries Watching now:
In this course, Dennis Taylor explores the functions, commands, and techniques in Excel that restructure data, remove unwanted characters, convert date data into the desired format, and prepare data for efficient analysis. This course helps get data from a business management system file, other database software, a text file, or a poorly designed Excel worksheet into optimal shape for working with in Excel.
There are many different ways in which you can enter a date data into an Excel worksheet; however, a lot of different layouts don't lend themselves to working with Excel dates efficiently. To work with dates efficiently in Excel, they must be considered values, so that we can work with them computationally. Date, the way we see it in column A and in column C isn't really set up properly. But if we've got a ton of entries like this, we want to be able to convert them into actual dates, and usually this takes a separate column.
Now, a function you wouldn't necessarily know much about, but relatively easy to use, is a function called date, and this function needs a year, month and day. Now the date in Column A doesn't have any days associated with it, so we'll just use the first day of the month. So what can we do with this information? From the right-hand side of each of those entries there, the four rightmost characters consist of the year. So here's a function called right, and its sole purpose is to pull data out of the right-hand side of these cells.
And we want to pull off the four rightmost characters, and that will constitute the actual year that we're looking for. Now we need the month, but recognize that some of them months, as in A2, are two characters, but in A3 and A4, it's one character. But it wouldn't pick up the space as well, so what do we need now? From the left side of cell A2, right there, comma, let's pick up two characters. That's going to give us the month. And now we need the day, comma. Let's just put in the number 1. A right parenthesis.
So what we're doing is gathering some of the information there and inserting 1 for the day to come up with a date, and this is what we see. And that looks good. We'll double-click. Check out the others here and there. Now if some of these dates, by the way, are impossible here, if somebody put in a 13th month or at a three-digit year here and there, we would have some other problems. That appears not to be the case. Now we're not quite finished with this, so what we also need to do in this example here is to take these results and either paste them back onto the original data--but let's say we want to hold on to that at least for the moment.
A quick way to convert these into actual values is, after selecting them all, use the right mouse button and drag, for example, upward and then downward right back on top of itself with the right mouse button. Copy Here as Values Only, so we see the data looking this like this. It's all cleaned up. And once you've seen one example like this--now let me press Ctrl+Z to undo. Once you've seen one of these--I'll make the column a bit wider--you begin to gather some sense of how you can pull together information from different locations. And the example in Column C is very similar.
I think once you saw that, you would realize we could easily put in Column D a function similar to this, a collection of various date, right, and left combinations to come up with dates there as well too. Column E, perhaps a little bit trickier, and you might even need to set up a table over here. And converting this into a date that Excel could use, what we need to do here is to somehow look up what those three letters are, or work with some other techniques. So, a quick look at this might be the following.
We need from here to gather the actual year, so once again we'll use this date function. We want to gather the year from the four leftmost characters, so we'll use the left characters from here. That's going to give us the year. Now, how do we get the month? The next thing we need to do is to pull out characters that start in the sixth position, reading from the left, and then three characters. So here's a function you might not have used. It's called mid.
And, by the way, if you want more information on how to use some of these text functions, you might want to check out the Excel 2010 or 2007 course on advanced formulas and functions, and learn more about text functions like Left and Mid. Think of the word middle here. What we're going to be doing is pulling out data from the middle here, and that's going to be starting in the sixth position from the left. So we're looking at cell E2, 6 position, Starting there, we want to pull out three characters.
Now in this particular case, we'll be looking at Sep. And now we want to use, by using by using vlookup, we want to take that Sep value, comma, and look it up in this list that we see over here, using a vlookup, comma. And then we want to get our data out of the second column of that list. That's a 2. So there's a lot going on here, and I wouldn't expect anybody to remember this. And if you haven't seen the other functions, this is quite a stretch all of a sudden.
Following the 2, then we need to put in a 0, indicating it's an exact match, and you should also see the word false down there. 0 is a little bit shorter. It also works. So that's going to give us the actual number of the month, comma. And now the actual day is going to be the two rightmost characters out of E2. And again, recognize that some of the other days that we see there have only one character, so that means we'll be using the space there. So the last thing we need here in this combination of functions here is to get the day out of that by choosing right, meaning from the right side of this cell, comma, 2, right parenthesis, right parenthesis.
And there is the date. That's the way we see it. And let me make this, as I scroll to the right, make the column a little bit wider, so we can see this even better. There is a lot going on there. The result tells the story more than anything. But a combination of functions, and this may seem a bit involved and pretty tricky, and yet the payoff is, a lot of times when you encounter dates like this, it's not just having 8 or 10, the way we see here, but thousands of these. And so it's worth the effort, and the work sometimes to turn these into dates that Excel really can use.
The one thing that needs to be changed here before we can copy this down the column is to make sure that as we copy this with the reference to are table data over there, which you do have to set up manually, is referenced right here. J2 to K13 must be made absolute, and you can do that by pressing the F4 key. And now, one more time here, this is complete, we'll double-click to copy this down the column. So at different times, you will need a variety of techniques, possibly using text functions, to convert unusual date formats into actual Excel dates that can be used computationally in your Excel worksheets.
Find answers to the most frequently asked questions about Cleaning Up Your Excel 2010 Data .
Here are the FAQs that matched your search "" :
Sorry, there are no matches for your search "" —to search again, type in another word or phrase and click search.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.