Translate abbreviated years to a sort data column

[JNowell]

Application/Platform: Excel 2011 for Mac

I have an Excel spreadsheet with thousands of rows of data that I need to sort in descending year order. I am very new to this level of Excel manipulation. The problem is the current values can be a range of abbreviated years, a single year or all years. I need to evaluate the value of the cell and deliver a result. If the cell has ALL it needs to deliver the year 1900. If it has a 4 digit year it needs to deliver those 4 digits. If it has a range of years as the 96-14 it needs to deliver just the first year as 4 digits.

I was thinking:
if the first digit is 1 or 0 deliver "20"+the first two digits
IF 6,7,8,9, deliver "19" + the first two digits.


Example of cell values:

ALL
2014
96-14
10-14
09-15
89-15

I need the result for the above cells to be:
1900
1996
2010
2009
1989

Can anyone please help me with this?

 

[RonB1111]

Try:

=--IF(LEN(A1)=3,1900,IF(LEN(A1)=4,A1,IF(--LEFT(A1,2)>30,19,20)&LEFT(A1,2)))