Sort alphanumeric data with - in number

[jellevansoelen]

Hi Guys,

Hopefully you can help me with a sort issue.
I have more than 4000 rows with numbers year-number (1990-1).
They randomly in the excel document.
I want to sort on this number. But when I sort, al numbers 1 come be first.

Example:

Book2
	E
11	1990-1
12	1990-15
13	1991-1
14	1991-5
15	1990-2
16	1991-14
Sheet1

When i sort:

Book2
	E
11	1990-1
12	1990-15
13	1990-2
14	1991-1
15	1991-14
16	1991-5
Sheet1

And what i want is the following sort:

Book2
	E
11	1990-1
12	1990-2
13	1990-15
14	1991-1
15	1991-5
16	1991-14
Sheet1

I there a way to do that in Excel?

Tnx

 

[mart37]

With a helper column: format --> YYYY-00 or YYY-000

 

[jellevansoelen]

With a helper column: format --> YYYY-00 or YYY-000

Sorry, I don't know what to do now.

 

[mart37]

In an extra column: =LEFT(E11,4)&"-"&TEXT(MID(E11,6,999),"00")

 

[jellevansoelen]

In an extra column: =LEFT(E11,4)&"-"&TEXT(MID(E11,6,999),"00")

Hi Mart,

Tnx! Exactly what I need.