sort
 

I'm trying to use the sort comand on excel so that my list looks like this
05-024
AB05-543
05-678
002-23

What was asked of me is to sort the list so that letters are not taken into
consideration and so that it is sorted by the number of digits to the left of
the hyphen then by value.
Does anyone know how to do this?

sort
 

With your sample data in A1:A4, enter the following UDF in a normal Module:

Function sNum(g As Range)
Application.Volatile
f = g.Value
For i = 1 To Len(f)
If Mid(f, i, 1) Like "[A-Z]" Then GoTo skip
If Mid(f, i, 1) = "-" Then Exit For
sNum = sNum & Mid(f, i, 1)
skip:
Next i
End Function

Then in B1 enter:

=LEN(sNum(A1))&sNum(A1)

and copy down to B4

In C1 enter:

=MID(A1,FIND("-",A1)+1,255)

And copy down to C4.

Now sort by Column B then by Column C

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"always confused" wrote in
message ...
I'm trying to use the sort comand on excel so that my list looks like this
05-024
AB05-543
05-678
002-23

What was asked of me is to sort the list so that letters are not taken
into
consideration and so that it is sorted by the number of digits to the left
of
the hyphen then by value.
Does anyone know how to do this?

sort
 

I guess my list is a ittle more complicated than I though. It started to
work and went wrong somewhere. thank yo for your help though.

"Sandy Mann" wrote:

With your sample data in A1:A4, enter the following UDF in a normal Module:

Function sNum(g As Range)
Application.Volatile
f = g.Value
For i = 1 To Len(f)
If Mid(f, i, 1) Like "[A-Z]" Then GoTo skip
If Mid(f, i, 1) = "-" Then Exit For
sNum = sNum & Mid(f, i, 1)
skip:
Next i
End Function

Then in B1 enter:

=LEN(sNum(A1))&sNum(A1)

and copy down to B4

In C1 enter:

=MID(A1,FIND("-",A1)+1,255)

And copy down to C4.

Now sort by Column B then by Column C

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"always confused" wrote in
message ...
I'm trying to use the sort comand on excel so that my list looks like this
05-024
AB05-543
05-678
002-23

What was asked of me is to sort the list so that letters are not taken
into
consideration and so that it is sorted by the number of digits to the left
of
the hyphen then by value.
Does anyone know how to do this?

sort
 

You have a response at your earlier post. Perhaps you can post further
examples of your data.

Pete

"always confused" wrote in
message ...
I guess my list is a ittle more complicated than I though. It started to
work and went wrong somewhere. thank yo for your help though.

"Sandy Mann" wrote:

With your sample data in A1:A4, enter the following UDF in a normal
Module:

Function sNum(g As Range)
Application.Volatile
f = g.Value
For i = 1 To Len(f)
If Mid(f, i, 1) Like "[A-Z]" Then GoTo skip
If Mid(f, i, 1) = "-" Then Exit For
sNum = sNum & Mid(f, i, 1)
skip:
Next i
End Function

Then in B1 enter:

=LEN(sNum(A1))&sNum(A1)

and copy down to B4

In C1 enter:

=MID(A1,FIND("-",A1)+1,255)

And copy down to C4.

Now sort by Column B then by Column C

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"always confused" wrote in
message ...
I'm trying to use the sort comand on excel so that my list looks like
this
05-024
AB05-543
05-678
002-23

What was asked of me is to sort the list so that letters are not taken
into
consideration and so that it is sorted by the number of digits to the
left
of
the hyphen then by value.
Does anyone know how to do this?