modified Sort

Posted by Paul Magruder on May 13, 2001 2:18 PM

Is there code will allow me to sort by number and letter? Example:
101a
105
103
101b
106
101c
I need to return....101a,101b,101c, 103,105,106. ect...
Thanks in advance
Paul

Posted by Mark W. on May 13, 2001 2:26 PM

Format the values as Text.

Posted by Dave Hawley on May 13, 2001 2:27 PM

Hi Paul

You would need to seperate the numbers from the text first. You can get the number through some Nexted Text formulas or use this custom function of mine.

Function ExtractNumber(rCell As Range)
'Wriiten by OzGrid Business Applications
'www.ozgrid.com
Dim iCount As Integer

'Application.Volatile

For iCount = Len(rCell) To 1 Step -1
ExtractNumber = Left(rCell, iCount)
If IsNumeric(ExtractNumber) Then Exit For
Next iCount

ExtractNumber = CInt(ExtractNumber)

End Function

Then if the text is in A1 you would use:
=ExtractNumber(A1)

And in B1 put:
=RIGHT(A1,LEN(A1)-LEN(B1))

Now sort the 3 Columns by Number then Letter.

Dave

OzGrid Business Applications