Hello. As promised previously, this is an ongoing series of data cleaning exercise using text to column and particularly in the area of dates. Let me take case two this time. case two has certain dates which has been posted by the ER p system that the company is using. The point is they provide the data in this format where the URL is mentioned the four characters then the month 01 indicates January and 09 indicates ninth ninth of which month January. Now, unless it is provided in a certain format, which Excel understands, you will not be able to analyze the data.
This is just a small sample. But my question is, is this word the actual data spanning thousands of rows, how do you convert into the column B output. At this moment, I need to ensure a couple of things one it is in the correct format MD why there are proper slashes in between them so as to differentiate between month and date. To do that, I choose the data. I go to Data tab I go to text to columns, yes, text to columns. If you recollect our steps from the previous video, I need to go to step two, ensure that none of these checkboxes are on them to step three, in which in which I need to check this radio button on which says date.
I want to set the data format to date. And I need to confess the mistake meaning my sequence of mistake is your first then month then date. So here I go y m d, as soon as I say finish, there, it appears in the correct form. Although you now have the power to change the appearance by right clicking on it going to format cells and then choosing the desired display value. As I do so, and press OK. This is what I get as similar as in column B. case three, something similar as I open case three.
Now what kind of mistakes mistake has been committed here? It's dry. Perfect. I choose this to sample cells, the one which I've underlined, I go to data, text to column. As usual, I'm going to step inside step three of three, step three of three. And this is where I confessed a mistake, the mistake being BM VI, and finish.
Yeah, now those are corrected. If you double click, you'll find the MD vi format in which the Excel is storing it. And in fact, especially for this case, where the date is mentioned, with no space in between, then the exact month in letters in three letters have been mentioned. And then the year there's one more strategy that can be applied. It is very peculiar strategy very typical to such data. And that strategy is a formula called date value.
As I write the formula date value, I simply need to choose the cell, close the bracket. And in press enter, although it may look like a number, something like 40,000 50,000 you may just change the format of the cells, change it to a date. form any form and press OK. There you go. So you have two strategies one is the formula based strategy specially for this kind of a data set and one is x two column strategy. If you want more clarity on these concepts, please refer to our videos on dates.