# Translate abbreviated years to a sort data column

#### JNowell

##### New Member
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

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

#### RonB1111

##### Well-known Member
Try:
Code:
``=--IF(LEN(A1)=3,1900,IF(LEN(A1)=4,A1,IF(--LEFT(A1,2)>30,19,20)&LEFT(A1,2)))``

Replies
0
Views
406
Replies
1
Views
529
Replies
3
Views
158
Replies
1
Views
461
Replies
10
Views
393

1,195,674
Messages
6,011,096
Members
441,582
Latest member
Topkapi

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back