how to get the correct sort order

how to get the correct sort order. when i tried i get the erroneous one.pls see the attached

Hi,

I used helper column. See the file.

Originally Posted by martindwilson

depends on how many vol you have as well
this as a helper will allow you to sort from
vol-1-1 up to
vol-99-999
=LEFT(B2,4)&TEXT(SUBSTITUTE(LEFT(B2,FIND("-",B2,6)-1),"VOL-",""),"00")&"-"&TEXT(MID(B2,FIND("-",B2,5)+1,10),"000")

thanks for the formula. but one querry. my original data is in col-A.even though i have changed all B2 in your formula to A2 nothing new happened.

Originally Posted by misrasomendra

Hi,

I used helper column. See the file.

thanks for the direction. need somemore explanation. i checked with your formula. i get only the numbers minus the vol number.

Originally Posted by newdoverman

If the VOL numbers increase into the double digits, the problem is compounded because computers sort values from left to right. That is why you will get 20 sorted before 3 and 30 before 4.

To get around this, copy the original data into column B and use Text to columns on column B. Use - as the delimiter. Don't import VOL. Click Finish. Now, sort on column B and add column C as a second sort.

This shows the results after adding volume numbers higher than 10 for demonstration purposes.

	A	B	C
1	original	H1	H2
2	VOL-1-1	1	1
3	VOL-1-2	1	2
4	VOL-1-3	1	3
5	VOL-1-4	1	4
6	VOL-1-5	1	5
7	VOL-1-6	1	6
8	VOL-1-7	1	7
9	VOL-1-9	1	9
10	VOL-1-49	1	49
11	VOL-1-50	1	50
12	VOL-2-26	2	26
13	VOL-11-1	11	3
14	VOL-11-15	11	15
15	VOL-11-11	11	30
16	VOL-20-18	20	18

thanks for the suggestion.(see my post.it is vol number along with the track numbers) what you suggest is that we can not put vol number along with the tracknumbers?need to put in different columns?

Originally Posted by martindwilson

here it is in your workbook
you need to use the helper column to sort by
select columns a:b sort by column b

thanks for the reply. so this is a two step solution. am i right?assume that the data is in col A. first put the formula in col B and draw downwards.then select both col a and b and sort on col B. done.

thanks newdoverman for the suggestion and explanation.have a nice day.

Originally Posted by martindwilson

@ FlameRetired how would that sort this ?
VOL-2-1
VOL-1-1
VOL-33-11
VOL-4-11

Hmmm. Good question since mine only sorts on the last group of digits!!