Forum Discussion

ElJarrod's avatar
ElJarrod
Copper Contributor
Dec 05, 2019

How to sort column (of character strings starting with MMDDYY) by date?

Hello everyone,

I'm sure there's an efficient way to do this but I'm stumped, so I turn to the experts.

 

I have a spreadsheet where one of the columns is filled with character strings that begin with MMDDYY, as something of a prefix. Like this below:

 

080919-xyz-11p-oyster-project-vid
080815-xyz-11p-backyard-housing-new-vid
072219-xyz-4p-theft-vid
091117-xyz-6p-apts-vid
091319-xyz-no-mail-vid
092718-xyz-6pm-babba-hayhousing-vid
070116-xyz-plastic-vid
071119-xyz-gates-vid
093016-xyz-11a-sidewalk-vid
082819-xyz-5a-camp-vid

 

What I'm trying to do is sort the column by date. And the problem is that when I "Sort A to Z" the months and days are fine, but the years are out of order. (If all those "dates" ended in "19" or "18" etc. it would work).

 

What would be a good approach for sorting this column by date? I thought I might be able to do something savvy with Find/Replace but wildcard characters don't seem to work in Find. Is there some way to batch delete rows based on criteria? Some other feature or trick that could help? I'm stumped...

excel

5 Replies

  • ElJarrod's avatar
    ElJarrod
    Copper Contributor
    Dec 05, 2019

    Thank you for the suggestions, they will likely come in handy another time.

    I kept playing with the wildcards and I'm not sure why what I initially tried didn't work but eventually I did searches for:
    *14-

    *15-

    *16-

    *17-

    *18-

     

    And replaced them all with:

    001-

     

    My ultimate goal was a little more nuanced than I described. What I needed to do was isolate only the 2019 records. So I sorted the column A-Z, which moved all of those non-2019 records to the top. Then I just shift-selected all of them and deleted them.

     

    Thanks again.

    • crazyshoots's avatar
      crazyshoots
      Brass Contributor
      Dec 05, 2019
      @euarrod if the comments were of help in any way, please like the post.. am asking because while most contributors are contributing out of Goodwill; having the likes is a way of appreciating the time and effort invested and motivates them more to help out in future 🙂
  • crazyshoots's avatar
    crazyshoots
    Brass Contributor
    Dec 05, 2019

    ElJarrodthe way I look at it, if you could switch the date format to YYMMDD it should helps. Reason being the sorting methology usually works that way vs MMDDYY (alternatively you could use DDMMYY but I hardly think it works as well as YYMMDD)

     

    The moment you do that, you can sort.. see screen shot below.