numeric alpha sort

Hi all,
I have an application where there is a column A with values of F1 through
F83. There is data in columns B,C,D,E,F AND G.
From time to time there is an addition in column A, the value being F1A or F2A etc.
The rows then have to be sorted so that F1A follows F1 and the next row would start with F2 rather than F10.
Can anyone help?
Thanks

Hi,

unfortunately, alphabetic sort puts mixed values in this order:

F01
F01A
F1
F10
F11
F12
F13
F14
F15
F16
F17
F18
F19
F1A
F2
F20
F3

You may need to introduce a helper column for the correct sort order. Or, introduce a leading zero in your F1 to make it F01, then F01A would follow before F02, etc....

HTH

Know how to plug in a UDF? This function will expand the numeric part of strings specifically for sorting. Then you can delete the column (or keep it)

Function PadNums(sInp As String, Optional iLen As Long = 1) As String
    ' shg 2003-1115

    ' Expands numbers in a string to iLen characters for sorting; e.g.,
    '   PadNums("13A1U3", 2)    = "13A01A03"
    '   PadNums("1.2.3.15", 3)  = "001.002.003.015"
    
    ' Numbers are not shortened below their minimal representation:
    '   PadNums("1.123.2.3", 2) = "01.123.02.03"
    
    ' Returns unpadded values if iLen omitted
    '   PadNums("01.123.02.03") = "1.123.2.3"
    
    ' All non-numeric characters are returned as-is
    
    Dim sFmt    As String
    Dim i       As Long
    Dim iNum    As Long
    Dim sChr    As String
    Dim bNum    As Boolean
    
    sFmt = String(iLen, "0")

    For i = 1 To Len(sInp)
        sChr = Mid(sInp, i, 1)
        If sChr Like "#" Then
            bNum = True
            iNum = iNum * 10 + CInt(sChr)
        Else
            If bNum Then
                PadNums = PadNums & Format(iNum, sFmt)
                iNum = 0
                bNum = False
            End If
            PadNums = PadNums & sChr
        End If
    Next
    If bNum Then PadNums = PadNums & Format(iNum, sFmt)
End Function