How to get excel to sort some strings like they were numbers

[rtemen]

I have a sheet with a couple of columns.

IndexID	Part Number
1,1	3807
1,2	6680
1,3	4440
1,4	2074
1,5	5021
1,6	2298
1,7	1572
1,8	3379
1,9	8832
1,10	2627
1,11	6802
1,12	4395
2,1	7504
2,2	4109
2,3	9803
2,4	5461
2,5	2052
2,6	2779
2,7	3397
2,8	7861
2,9	8898
2,10	9587
2,11	3924
2,12	6043

<colgroup><col><col></colgroup><tbody>
</tbody>

When I sort by Part number, I get the following:

IndexID	Part Number
1,7	1572
2,5	2052
1,4	2074
1,6	2298
1,10	2627
2,6	2779
1,8	3379
2,7	3397
1,1	3807
2,11	3924
2,2	4109
1,12	4395
1,3	4440
1,5	5021
2,4	5461
2,12	6043
1,2	6680
1,11	6802
2,1	7504
2,8	7861
1,9	8832
2,9	8898
2,10	9587
2,3	9803

<colgroup><col><col></colgroup><tbody>
</tbody>

But, when I try to sort by the IndexID column I get the following:

IndexID	Part Number
1,1	3807
1,10	2627
1,11	6802
1,12	4395
1,2	6680
1,3	4440
1,4	2074
1,5	5021
1,6	2298
1,7	1572
1,8	3379
1,9	8832
2,1	7504
2,10	9587
2,11	3924
2,12	6043
2,2	4109
2,3	9803
2,4	5461
2,5	2052
2,6	2779
2,7	3397
2,8	7861
2,9	8898

<colgroup><col><col></colgroup><tbody>
</tbody>

I need the numbers in the IndexID to be back like it started at the top of my description.
Is there a way to have it sort the columns as if they were numbers rather than strings?
Also, I need a solution that does NOT include adding columns to partially divide up the strings.
If we need a VBA solution, I am open to that as well.

Thanks,
Rich

 

[shg]

UDF:

Function PadNum(sInp As String, Optional iLen As Long = 1) As String
  ' shg 2003

  ' Expands numbers in a string to iLen characters for sorting; e.g.,
  '   PadNum("13A1U3", 2)    returns "13A01U03"
  '   PadNum("1.2.3.15", 3)  returns "001.002.003.015"

  ' Numbers are not shortened below their minimal representation:
  '   PadNum("1.123.2.3", 2) = "01.123.02.03"

  ' Returns unpadded values if iLen <= 1 or omitted
  '   PadNum("01.123.02.03") = "1.123.2.3"

  ' Digit strings > 15 characters are not modified, because
  ' formatting would cause loss of digits
  
  ' All characters other than digits 0-9 are returned as-is

  Dim sFmt          As String   ' format string
  Dim iChr          As Long     ' character index to sInp
  Dim sNum          As String   ' digit string
  Dim sChr          As String   ' sInp character
  Dim bNum          As Boolean  ' sNum contains digit string

  sFmt = String(IIf(iLen < 1, 1, IIf(iLen > 15, 15, iLen)), "0")

  For iChr = 1 To Len(sInp) + 1   ' the +1 flushes a trailing number
    sChr = Mid$(sInp, iChr, 1)
    If sChr Like "#" Then
      bNum = True
      sNum = sNum & sChr
    Else
      If bNum Then
        If Len(sNum) <= 15 Then
          ' it's coercible to a Double without loss of digits
          PadNum = PadNum & Format(CDbl(sNum), sFmt)
        Else
          ' it isn't; use as-is
          PadNum = PadNum & sNum
        End If
        
        sNum = vbNullString
        bNum = False
      End If
      PadNum = PadNum & sChr
    End If
  Next iChr
End Function

Used like this:

	A​	B​	C​	D​
1​	IndexID​	Part Number​	Sort​	​
2​	1,1	3807​	01,01	C2: =PadNum(A2, 2)
3​	1,2	6680​	01,02
4​	1,3	4440​	01,03
5​	1,4	2074​	01,04
6​	1,5	5021​	01,05
7​	1,6	2298​	01,06
8​	1,7	1572​	01,07
9​	1,8	3379​	01,08
10​	1,9	8832​	01,09
11​	1,10	2627​	01,10
12​	1,11	6802​	01,11
13​	1,12	4395​	01,12
14​	2,1	7504​	02,01
15​	2,2	4109​	02,02
16​	2,3	9803​	02,03
17​	2,4	5461​	02,04
18​	2,5	2052​	02,05
19​	2,6	2779​	02,06
20​	2,7	3397​	02,07
21​	2,8	7861​	02,08
22​	2,9	8898​	02,09
23​	2,10	9587​	02,10
24​	2,11	3924​	02,11
25​	2,12	6043​	02,12

 

[shg]

Oops, pasted old version:

Function PadNum(sInp As String, Optional iLen As Long = 1) As String
  ' shg 2003

  ' Expands numbers in a string to iLen characters for sorting; e.g.,
  '   PadNum("13A1U3", 2)    returns "13A01U03"
  '   PadNum("1.2.3.15", 3)  returns "001.002.003.015"

  ' Numbers are not shortened below their minimal representation:
  '   PadNum("1.123.2.3", 2) = "01.123.02.03"

  ' Returns unpadded values if iLen <= 1 or omitted
  '   PadNum("01.123.02.03") = "1.123.2.3"

  ' Digit strings > 15 characters are not modified, because
  ' formatting would cause loss of digits
  
  ' All characters other than digits 0-9 are returned as-is

  Dim sFmt          As String   ' format string
  Dim sChr          As String   ' a character in sInp
  Dim iChr          As Long     ' character index to sInp
  Dim sNum          As String   ' digit string from sInp

  sFmt = String(IIf(iLen < 1, 1, IIf(iLen > 15, 15, iLen)), "0")

  For iChr = 1 To Len(sInp) + 1   ' the +1 flushes a trailing number
    sChr = Mid$(sInp, iChr, 1)
    If sChr Like "#" Then
      sNum = sNum & sChr
    Else
      If Len(sNum) Then
        PadNum = PadNum & IIf(Len(sNum) <= 15, Format$(sNum, sFmt), sNum)
        sNum = vbNullString
      End If
      PadNum = PadNum & sChr
    End If
  Next iChr
End Function