Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-1051716%22%20slang%3D%22en-US%22%3EHow%20to%20sort%20column%20(of%20character%20strings%20starting%20with%20MMDDYY)%20by%20date%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1051716%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone%2C%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EI'm%20sure%20there's%20an%20efficient%20way%20to%20do%20this%20but%20I'm%20stumped%2C%20so%20I%20turn%20to%20the%20experts.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20spreadsheet%20where%20one%20of%20the%20columns%20is%20filled%20with%20character%20strings%20that%20begin%20with%26nbsp%3BMMDDYY%2C%20as%20something%20of%20a%20prefix.%20Like%20this%20below%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E080919-xyz-11p-oyster-project-vid%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E080815-xyz-11p-backyard-housing-new-vid%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E072219-xyz-4p-theft-vid%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E091117-xyz-6p-apts-vid%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E091319-xyz-no-mail-vid%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E092718-xyz-6pm-babba-hayhousing-vid%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E070116-xyz-plastic-vid%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E071119-xyz-gates-vid%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E093016-xyz-11a-sidewalk-vid%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E082819-xyz-5a-camp-vid%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I'm%20trying%20to%20do%20is%20sort%20the%20column%20by%20date.%20And%20the%20problem%20is%20that%20when%20I%20%22Sort%20A%20to%20Z%22%20the%20months%20and%20days%20are%20fine%2C%20but%20the%20years%20are%20out%20of%20order.%20(If%20all%20those%20%22dates%22%20ended%20in%20%2219%22%20or%20%2218%22%20etc.%20it%20would%20work).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20would%20be%20a%20good%20approach%20for%20sorting%20this%20column%20by%20date%3F%20I%20thought%20I%20might%20be%20able%20to%20do%20something%20savvy%20with%20Find%2FReplace%20but%20wildcard%20characters%20don't%20seem%20to%20work%20in%20Find.%20Is%20there%20some%20way%20to%20batch%20delete%20rows%20based%20on%20criteria%3F%20Some%20other%20feature%20or%20trick%20that%20could%20help%3F%20I'm%20stumped...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1051716%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1051794%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20sort%20column%20(of%20character%20strings%20starting%20with%20MMDDYY)%20by%20date%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1051794%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F479415%22%20target%3D%22_blank%22%3E%40ElJarrod%3C%2FA%3E%26nbsp%3B-%3C%2FP%3E%3CP%3EYou%20could%20add%20a%20helper%20column%20to%20get%20the%20'YY'.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DMID(C3%2CFIND(%22-%22%2CC3%2C1)-2%2C2)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20476px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F160334i4319B4C9C4D393B9%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222.PNG%22%20title%3D%222.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1051958%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20sort%20column%20(of%20character%20strings%20starting%20with%20MMDDYY)%20by%20date%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1051958%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F479415%22%20target%3D%22_blank%22%3E%40ElJarrod%3C%2FA%3Ethe%20way%20I%20look%20at%20it%2C%20if%20you%20could%20switch%20the%20date%20format%20to%20YYMMDD%20it%20should%20helps.%20Reason%20being%20the%20sorting%20methology%20usually%20works%20that%20way%20vs%20MMDDYY%20(alternatively%20you%20could%20use%20DDMMYY%20but%20I%20hardly%20think%20it%20works%20as%20well%20as%20YYMMDD)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20moment%20you%20do%20that%2C%20you%20can%20sort..%20see%20screen%20shot%20below.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20423px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F160338i99147768E3BB92B2%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22dateformat.PNG%22%20title%3D%22dateformat.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1051981%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20sort%20column%20(of%20character%20strings%20starting%20with%20MMDDYY)%20by%20date%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1051981%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20for%20the%20suggestions%2C%20they%20will%20likely%20come%20in%20handy%20another%20time.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20kept%20playing%20with%20the%20wildcards%20and%20I'm%20not%20sure%20why%20what%20I%20initially%20tried%20didn't%20work%20but%20eventually%20I%20did%20searches%20for%3A%3CBR%20%2F%3E*14-%3C%2FP%3E%3CP%3E*15-%3C%2FP%3E%3CP%3E*16-%3C%2FP%3E%3CP%3E*17-%3C%2FP%3E%3CP%3E*18-%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20replaced%20them%20all%20with%3A%3C%2FP%3E%3CP%3E001-%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20ultimate%20goal%20was%20a%20little%20more%20nuanced%20than%20I%20described.%20What%20I%20needed%20to%20do%20was%20isolate%20only%20the%202019%20records.%20So%20I%20sorted%20the%20column%20A-Z%2C%20which%20moved%20all%20of%20those%20non-2019%20records%20to%20the%20top.%20Then%20I%20just%20shift-selected%20all%20of%20them%20and%20deleted%20them.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20again.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1052025%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20sort%20column%20(of%20character%20strings%20starting%20with%20MMDDYY)%20by%20date%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1052025%22%20slang%3D%22en-US%22%3E%40euarrod%20if%20the%20comments%20were%20of%20help%20in%20any%20way%2C%20please%20like%20the%20post..%20am%20asking%20because%20while%20most%20contributors%20are%20contributing%20out%20of%20Goodwill%3B%20having%20the%20likes%20is%20a%20way%20of%20appreciating%20the%20time%20and%20effort%20invested%20and%20motivates%20them%20more%20to%20help%20out%20in%20future%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1053228%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20sort%20column%20(of%20character%20strings%20starting%20with%20MMDDYY)%20by%20date%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1053228%22%20slang%3D%22en-US%22%3EAbsolutely!%3C%2FLINGO-BODY%3E
Highlighted
ElJarrod
Occasional Contributor

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

5 Replies

@ElJarrod -

You could add a helper column to get the 'YY'.

 

=MID(C3,FIND("-",C3,1)-2,2)

 

2.PNG

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

 

dateformat.PNG

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.

@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
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
50 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
32 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
15 Replies
Dev channel update to 80.0.355.1 is live
josh_bodner in Discussions on
67 Replies