Using Text to Columns to Separate Text in Excel

I found myself with a conundrum when trying to fix dates so they could be imported correctly into a database.  The existing database was exporting dates like this “1943-08-24T00:00:00Z” I need the dates in DD/MM/YYYY format.

The first thing I tried was to use the left function to extract the first 10 characters which gets me the date.  However for some reason the left function would not let me change the output to the right date formula. 

Then a friend of mine suggested I try Excel Text to Columns .  Here is how you do that.

  1. Go to the Data tab.

  3. Select the column(s) you want to split.

  5. Click Text to Columns

  7. Select Delimited from the next screen. Text to Columns Screen Step 1 of 3

  9. Select your delimiter. This is the character that will be used to break the current column into multiple columns. In my case I needed a custom delimiter of "T" Text to Columns Screen Step 2 of 3 You can also see what your end result will look like, based on the delimiter your select.

  11. In the third and final step you select where your new columns should go and how they should be formatted. In my case I select the correct date format. Text to Columns Screen Step 3 of 3

With just a few clicks I was able to solve my problem.