How to get excel to sort 2-number dates correctly

[BrianR1805]

I'm working with a spreadsheet with over 28,000 rows of video/audio tape IDs that typically take this format: ID tag / Date (2 numbers) / Number. So, the 3rd Corporate tape from 1998 would be CORP98-0003. I want to sort this spreadsheet by ID tag first then numerically by date. The problem is that Excel automatically sorts the year 2000 (00) before the year 1999 (99) instead of doing it by date. This makes it take SOOO long to manually sort individual groups of NEW tapes into the 28,000-row spreadsheet. It would so much easier and faster if I could get Excel to automatically sort tapes my way. Just to be clear, I would like it to sort tapes this way:

CORP99-0099
CORP99-0100
CORP99-0101
CORP00-0001
CORP00-0002
CORP00-0003
etc. 

 How can I do that? I'm still fairly new to Excel. Thanks!

[#2]

@BrianR1805 Assuming you have Excel 365.

I would use a helper column which extends the year to 4 positions based on a cut-off date. I assumed 30. If your code is in cell A1:

=LET(DashPos,FIND("-",A1),lead,LEFT(A1,DashPos-3),yr,MID(A1,DashPos-2,2),trailing,MID(A1,DashPos+1,LEN(A1)),lead&IF(yr*1>30,"19"&yr,"20"&yr)&"-"&trailing)

Then sort on this new column. 

[#3]

@JKPieterse The formula worked perfectly on my test spreadsheet. Here's the issue I'm just realizing. Some of my Item ID's don't have two-digit numbers like this one (ADAT-0001). Can you add to the formula so that it ignores items without two digit dates but is still able to sort correctly? Thanks!

Oh, and just to put it out there, some of my items have even further numerical divisions so they look like this:

AE99-0173
AE99-0174_001

AE99-0174_002

AE99-0174_003

AE99-0175

etc. 

Is that going to mess with your formula?

[#4]

@BrianR1805 

Some of my Item ID's don't have two-digit numbers like this one (ADAT-0001). Can you add to the formula so that it ignores items without two digit dates but is still able to sort correctly? Thanks!

It always helps to have the full picture at the start. I'm not @JKPieterse but I am going to anticipate a question he'll probably have. WHERE in the sort would you put ADAT-0001 when sorting along with (presumably) ADAT99- and ADAT00- and so forth? You say "ignore" but you also want it sorted correctly, and ignoring isn't a way to get to correct sorting. So, on behalf of @JKPieterse , please give the full picture. Maybe even a sample file with full examples of the raw data you're dealing with AND how you expect the formula to interpret "correct sorting" when the code is missing the two digits.

Oh, and just to put it out there, some of my items have even further numerical divisions

For the record, his formula would still work with those trailing extra numerical digits. It's the ones that don't have the two year digits that you need to explain more fully.

[#5]

@mathetes I can’t currently provide any sample documents because I don’t go back to work til Friday. Fortunately, there are almost no examples of IDs with both 2-number dates and no date. In those rare cases I would sort the no-date IDs first before any of the 2-number dates. I still want everything to be sorted by ID FIRST, then by two-number date, then trailing numbers afterward.

[#6]

OK, That answers the question but it hadn't been clear before you stated that. One learning point regarding programming in general: you need to be able to state in clear English (or whatever language is one's native language) what is to be done before you can program it, whether in Excel or any other system. It's easy to take for granted what is clear to you, but really an assumption that hasn't been articulated.

I'll leave it to @JKPieterse to modify his excellent answer to the opening description of the need.

[#7 · Accepted solution]

Thanks to @mathetes for expanding on the discussion and clarifying our needs.

@BrianR1805 next time make sure your example data contains as many variations as you expect there are. It helps us spotting the exceptions.

I believe this formula should do it:

=LET(
DashPos,FIND("-",A1),
lead,LEFT(A1,DashPos-3),
yr,MID(A1,DashPos-2,2),
trailing,MID(A1,DashPos+1,LEN(A1)),
lead&IFERROR(IF(yr*1>30,"19"&yr,"20"&yr),yr)&"-"&trailing)

[#8]

It works perfectly, thanks so much! I'll try to be more specific up front next time I have an issue.