Sorting help needed

Ref. Attached DroughtPrint

Column A has survey numbers and i want to sort them in ascending order from 1-1a, 1-1b, 2-2a............... like that. But unable to do so. for example the out put is 250, 349, 1-4, 1-5. In some instances 113-10, then comes 113-4.

Pls help.

You shall split your "numbers" into groups/subgroups/.... then sort on several keys, so for instance
343-8d2a
or
53-19-a

shall be seen as:
D | E | F | G | H
343 | 8 | d | 2 | a
53 | 19 | a

or may be you see the latter as
53 | 19 | | | a
to emphasize it a format 53-19x-NNNa with letter x being empty and number NNN empty too

Unfortunately, because there are diferent styles (see above with small letters or dash or mixed) it will not be easy to do it automatically

BTW: isn't the latter (data from row 529) a mistake? it is the only one with a second dash in description.

Originally Posted by Kaper

You shall split your "numbers" into groups/subgroups/.... then sort on several keys, so for instance
343-8d2a
or
53-19-a

shall be seen as:
D | E | F | G | H
343 | 8 | d | 2 | a
53 | 19 | a

or may be you see the latter as
53 | 19 | | | a
to emphasize it a format 53-19x-NNNa with letter x being empty and number NNN empty too

Unfortunately, because there are diferent styles (see above with small letters or dash or mixed) it will not be easy to do it automatically

BTW: isn't the latter (data from row 529) a mistake? it is the only one with a second dash in description.

I thought this will be easy to do. Anyway, is there any other formula / function / application to do this?

Create helper column d
d2=text(left(substitute(a2,"-",rept(" ",10)),10),"0000")&iferror("-"&if(len(lookup(10^10,left(mid(substitute(a2,"-",rept(" ",10)),10,15),{1,2,3,4,5,6})+0))=1,0,""),"")&trim(mid(substitute(a2,"-",rept(" ",10)),10,15))

=text(left(substitute(a2,"-",rept(" ",10)),10),"0000")&iferror("-"&if(len(lookup(10^10,left(mid(substitute(a2,"-",rept(" ",10)),10,15),{1,2,3,4,5,6})+0))=1,0,""),"")&trim(mid(substitute(a2,"-",rept(" ",10)),10,15))

try this and copy to the end
now sort data based on column d

Originally Posted by nflsales

Create helper column d
d2=text(left(substitute(a2,"-",rept(" ",10)),10),"0000")&iferror("-"&if(len(lookup(10^10,left(mid(substitute(a2,"-",rept(" ",10)),10,15),{1,2,3,4,5,6})+0))=1,0,""),"")&trim(mid(substitute(a2,"-",rept(" ",10)),10,15))

=text(left(substitute(a2,"-",rept(" ",10)),10),"0000")&iferror("-"&if(len(lookup(10^10,left(mid(substitute(a2,"-",rept(" ",10)),10,15),{1,2,3,4,5,6})+0))=1,0,""),"")&trim(mid(substitute(a2,"-",rept(" ",10)),10,15))

try this and copy to the end
now sort data based on column d

Yes. It did. Its a great solution for me. Thanks a lot Siva.