Dec 05 2019 03:54 PM
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...
Dec 05 2019 04:18 PM
Dec 05 2019 05:39 PM
@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.
Dec 05 2019 05:48 PM
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.
Dec 05 2019 06:29 PM
Dec 06 2019 09:56 AM