Sometimes, working with an Excel data import can be a rocky horror show. This month, I’ve been working with a client who’s pulling together data from several accounting systems.
The project is extra exciting because each system stores the data in a different format, and we have to assemble it into a common file. I’m sure you’ve had to deal with a similar challenge, and used your mad scientist Excel skills to clean up the mess.
In one of the import files that my client uses, the date is stored in a YYYYMMDD format. From that number, we have to calculate the transaction date, so Excel can understand it. You can use a few Excel functions to extract the year, month and day, and turn that time warp into a valid date.
Just a Jump to the Left
In the screen shot below, the imported date is in column A. The year is at the left, in the first four characters. Use the Excel LEFT function to pull those four digits into column C, to show the year.
Then a Step to the Right
The transaction day is shown in the two characters at the right of the date in column A. Use the Excel RIGHT function to pull those two digits into column E, to show the day.
Put Your Hands on Your MID
The final step is to use the Excel MID function to pull a specific number of characters from the middle of the string in cell A2. The month number starts at the 5th character, and is 2 characters long.
More Excel Text Functions
To see a few more Excel text functions, and a sample workbook, you can visit Ken Puls’ website, and download his Text functions teaching workbook. The workbook explains the LEFT, RIGHT, MID, LEN, and FIND functions.
Watch the LEFT, RIGHT, MID Video
To see the steps for using the LEFT, RIGHT and MID functions in Excel, please watch this short Excel video tutorial.