Ah, misunderstood. If you want the sort order shown in col A below, you can use a UDF in col B as shown, and sort by that:

A
B
C
1
 ToBeSorted Sort
2
 1 001 B2: =PadNums(A2, 3)
3
 2 002
4
 3 003
5
 4 004
6
 5R1 005R001
7
 6R1 006R001
8
 7R2 007R002
9
 8 008
10
 9R1 009R001
11
 10 010
12
 11 011
13
 12 012
14
 13R2 013R002
15
 14 014
16
 15 015
17
 16 016
18
 17R1 017R001
19
 17R2 017R002 


Function PadNums(sInp As String, Optional ByVal 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 iChr    As Long
    Dim iNum    As Long
    Dim sChr    As String
    Dim bNum    As Boolean
    
    sFmt = String(IIf(iLen < 1, 1, IIf(iLen > 15, 15, iLen)), "0")

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