Fix an Excel Time Warp With LEFT RIGHT MID

rockyhorrorshow 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.

=LEFT(A2,4)

LeftRightMid01

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.

=RIGHT(A2,2)

LeftRightMid02

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.

=MID(A2,5,2)

LeftRightMid03

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.

____________

You may also like...

4 Responses

  1. Jason Morin says:

    One trick I have always liked is the use of the TEXT function to coerce a number into a date that Excel will recognize. This seems to work:

    =TEXT(A2,”0000-00-00?)+0

    However it looks like you’re intentionally putting the year, month, and day into separate columns for this project.

  2. Dave Patton says:

    Text to Columns would change the text to a proper date.

  3. Ken Puls says:

    Thanks for the link, Deb! For reference, if people want to skip the blog page and go right to the download page, the file is at the bottom of this page.

    Jason, that’s a cool little trick too!

  4. Prakash says:

    Dave’s method is fast! I also believe that is a much smarter way of doing it. Your’s was not bad as well.

Leave a Reply

Your email address will not be published. Required fields are marked *