sort

[always confused]

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?

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[always confused]

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.

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

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?

 

[Pete_UK]

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

Pete

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.

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

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?