Complex Parsing Question

I get spreadsheets with thousands of lines and one particular column that is a real pain in the ***. This column contains 4 dates, 2-4 times, and two cities with state abreviations. Sorting these is a nightmare. I was thinking of maybe writing something in python, but thought I would ask here first before I reinvent the wheel. I placed a couple of examples from this column below:

2013-03-16 08:00:00.0 2013-03-18 10:26:38.0 ABERDEEN MD USA,2013-03-18 13:15:00.0 2013-03-18 13:15:00.0 HAGERSTOWN MD USA

2013-03-21 2013-03-21 12:00:00.0 HANOVER PA USA,2013-03-21 15:00:00.0 2013-03-22 23:59:00.0 SUTHERLAND VA USA

2013-03-18 09:00:00.0 2013-03-18 23:58:00.0 LOCK HAVEN PA USA,2013-03-18 09:01:00.0 2013-03-19 23:58:00.0 GORDONSVILLE VA USA

So, ideally I want to make:
1 column for the first date
1 column for the first time...but it should be blank if the first time comes after the second date
1 column for the second date, blank if it comes before the city name
1 column for the second time, blank if it comes before the city name
1 column for the first city name (might be multiple words)
1 column for the first state abreviation
1 column for the third date
1 column for the third time
1 column for the fourth date
1 column for the fourth time
1 column for the second city name
1 column for the second city abreviation

There are a few more things I would probably implement with a python script...but I figure this would get me close enough. Does Excel or OpenOfficeCalc or some other app have such functions built in, or are there free apps in the wild that will do this for me?

Wow...thank you. Now, if it isn't too much bother, how can I do it on lots of cells instead of 1 at a time 2800 times?


Lock Haven, PA:
http://goo.gl/maps/fgvDC

Awesome...I never knew Excel had such features built in. Your new code gives error 28 after 349 lines, but I sorted it and it works good...I expected it to fail at 6980 lines, but it worked fine on a test of 11,000 lines...and that's several times the largest sheet I've gotten so far.

Please Login or Register  to view this content.