Friday, February 03, 2012

Reformatting a date column with a Time Stamp in Microsoft Excel

Today I've been working to clean up a database and it is has not been easy. I downloaded several spreadsheets of customer data from SWReg.  They have a nasty habit of putting Time information in the middle of the date field.

It looks like this: Feb 17 16:40:19 2011

My challenge was to create a column that formatted the date correctly, without the time stamp. Here's the solution I came up with.

To format the date from cell B2, it column C2 I entered:

=(LEFT(B2,7)&" "&(RIGHT(B2,4))

This pulled out the date from the left (I had a space in front of the date, so that's why I set it to read the first 7 characters of cell B2) and the year from the right (4 characters on the right).

Maybe there's a better way to do it. I really wish Microsoft would make it easier to clean your data in Excel.

Maybe you can help me out... One thing I was really struggling with:  How to use Find and Replace within a specified column only. I couldn't figure that out.

Good luck!

No comments: