Sort by truncated year in serial number

Welcome to the Forum!

You thought you were asking a simple question, didn't you?

How are users viewing an Excel file on a Unix mainframe? The answer may affect what solution will work. If they can't run macros then there may be other limitations as well.

Who is going to sort--you before you post the file, or the users after they retrieve it?

How do want to sort--as a numeric sort, or textual sort? That is, if you have

199801
1997001

Which one do you want to come first? In a numeric sort, you end up in the order I showed. In a textual sort, it would be the reverse. Because your numbers are not all the same length, it matters.

When you read my suggestion you will see why I am asking all this. One option is to add a second column with this formula:

=IF(LEFT(A1,2)>="77",19,20)&A1

and then sort on that column. If you want you can delete the column after sorting. Alternatively you can make the font in this column white so the user won't see it, unless they highlight any cells in that column.

One problem with that solution is that your data is numeric but you have formatted display to show leading zeroes but only in some cases. It's hard to figure out what you did. So if you see

000954

the result of grabbing the first two digits is not 00, it's 95. Therefore the formula doesn't work on those. In fact, nothing works because the special formats you are using are not detectable using Excel functions, so there is no for formulas to tell the difference between these values:

954
0954
00954

Sorry to respond to your question with so many questions.

This is just an extension of the points made by 6SJ above...

Originally Posted by MPICrusher

last three or four digits are the actual serial number

I suspect based on the data that prior to 2000 the codes were 3 digits and thereafter 4, correct ?

However, even if that is indeed the case you still can't distinguish between:

85104

085104

Given the latter's leading zero is the result of a custom format rather than a literal character (a point already made by 6SJ)

These two issues combined precludes you from establishing the year via the two leftmost characters of the underlying value
with the above values both would return 85 and we'd thus assume 3 digit code

So in short - as is - I don't think you can do what you want without use of some code - can you run XLM for ex. if you save as .xls ?

With B1 the active cell you could create the following name:

Name: =_TextValue
RefersTo: =GET.CELL(53,$A1)

Then

B1:
=YEAR("1-1-"&LEFT(_TextValue,2))&TEXT(REPLACE(_TextValue,1,2,"")+0,"0000")
copied down

but again this may not even be feasible for you and based on OP you may not even want to use an additional column

(using the above in 2007 format would necessitate .xlsm extension)