Hi. In this video we'll connect text to columns with certain unclean dates, dates, which Excel cannot understand and which is very much essential for us to proceed with after Of course correcting the dates. So I go to case one opening up this group button. On the left hand side column A the fonts which are in red are our deeds as per our standard, but Excel may not be able to understand that, in case you have gone through our videos on date, you would remember that his number is one formula which helps us detect whether it's a date because technically speaking, every valid date is a number. It's false. Now what is the difference?

If you have not changed the control panel settings of your PC, the default settings or dates essentially in excess mind are read in the MB vi format. I double click on the next cell I get the same view for eight which indicates fourth of August. So how do we ensure that the date and the month flip their pool And automatically the slash or the French group takes the position of dot. Let's find out. I choose the data, I press Ctrl H, yes, you may have thought about it, you would say find word dot replace with what forward stroke. And you will say replace all.

Whoa, it seems that has changed something But wait, has it corrected all the date? I'm using the same number formula to find so Nope. In fact, it has got a double whammy to it. Why? Because it has converted the date from fourth of August to eighth of April and from ninth of May to fifth of September. So this is not what I want.

What I want can be achieved through text to columns. Let's find out after having chosen the data of the go to data, text to columns. Now in step one of three I'm keeping it as delimited. In step two, I just need to ensure that none of the other checkboxes are turned on. All the toggles harmless are done. point in time.

Let me go to next, I'm in step three of three of text to column wizard. Here lies the solution. Since the sequence of mistake of dates is in the DMY format, I'm going to confess that mistake in a similar manner. So I choose this option. Why? Because it says, This screen that means step three of three, lets you select each column.

I've already chosen that column and set the data format, which I'm going to set it as a date format, but now we need to choose from the drop down, which format is it currently in? It's currently in the sequence of DMCA. Once I do that, all I need to do is press finish. Notice, all of them flip their position, the date and the month. Now, if I apply a certain format by saying right click Format Cells, and I choose the option which displays the month in three letters. As soon as I do so, I get full properties which I can easily read and differentiate between the correct and incorrect date.

So this was a quick trick using text to column and I will be referring this as my confession box approach. Confess the mistake can test the sequence of mistakes dm y or y MB. We'll see motivations in the next video.