MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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:
I need to return....101a,101b,101c, 103,105,106. ect...
Thanks in advance

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
Dim iCount As Integer


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:

And in B1 put:

Now sort the 3 Columns by Number then Letter.


OzGrid Business Applications