Transcripts for the above video clip:

Date-Text-to-Column

Sometimes in Excel you may receive data that is not something that your machine can deal with so, for example, here I have dates. However my machine works with the assumption that dates are represented by day month year and as you can see this data has come through as month day year. Now instead of going through the whole process of  deconstructing this and then reconstructing it to get the date right, you can trick Excel using the Text to Columns field. Now normally this button is used to take a whole bunch of text and using Delimination or Fixed Width, force it into various columns but  we can actually use it to change the date. So what I would do is highlight the cells that concern me and under Data you’ll see there is Text to Columns – click on it and in this case because we only have one column we actually trick Excel – neither of these  (Delimited or Fixed Width) matter so click Next, the next window  doesn’t matter, click Next. What is key here is that we can now tell Excel that these cells are actually dates, but more importantly in the drop down box , you can now say the dates are not day month year – they are actually month, day, year. So  I tell Excel to bring these through  in one column (although they are actually already in one column). These are representative of the dates and this is actually month day year. When I click Finish, you’ll see what Excel does is brings it through and converts it into a date that my machine can actually work with.