Sorting Columns

  • Thread starter Thread starter heather
  • Start date Start date
H

heather

Hi- My data in this column starts with alpha characters, followed by
numbers. (abc1, abc2, abc3 ... or abc001, abc002, abc003 ... , etc.) How
can I sort the column so that abc31 comes before abc300?

Thanks. -h
 
Heather,
As an example you put abc1, abc... My question to you is this?
Is this preceding text fixed in length (ie always a certain # of alpha
characters)

Dan E
 
Here it goes,

Here is a custom function to take the end of the string out as a number.

Public Function GetEndNumber(Cell As Range) As Integer
'Returns # if a number is found
'Returns 0 if a number is not found
Dim a, i, j As Integer
a = 32767
InText = Cell.Value
For i = 0 To 9
j = InStr(1, InText, CStr(i), vbTextCompare)
If j <> 0 And j < a Then a = j
Next
If a <> 32767 Then
NewText = Right(InText, Len(InText) - a + 1)
GetEndNumber = CInt(NewText)
Else
GetEndNumber = 0
End If
End Function


To make this a function
press Alt + F11
right click your project and add a module
paste the above code into your module
close the VB window

In your workbook you should find a new function called GetEndNumber in the
user defined function portion

Use this function like = GetEndNumber(A1) and drag down for all of your data

Sort using "Data" -> "Sort" and sort based on the new column of numbers

Dan E
 
Back
Top