1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Help with parsing excel columns

Discussion in 'Windows - Software discussion' started by KillerBug, Mar 14, 2013.

Thread Status:
Not open for further replies.
  1. KillerBug

    KillerBug Active member

    Joined:
    May 21, 2006
    Messages:
    3,802
    Likes Received:
    0
    Trophy Points:
    66
    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 these free apps in the wild that will do this for me?
     
Thread Status:
Not open for further replies.

Share This Page