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

R

rtemen

New Member
Joined
Sep 23, 2013
Messages
36
I have a sheet with a couple of columns.

[TABLE="width: 163"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]IndexID[/TD]
[TD]Part Number[/TD]
[/TR]
[TR]
[TD]1,1[/TD]
[TD="align: right"]3807[/TD]
[/TR]
[TR]
[TD]1,2[/TD]
[TD="align: right"]6680[/TD]
[/TR]
[TR]
[TD]1,3[/TD]
[TD="align: right"]4440[/TD]
[/TR]
[TR]
[TD]1,4[/TD]
[TD="align: right"]2074[/TD]
[/TR]
[TR]
[TD]1,5[/TD]
[TD="align: right"]5021[/TD]
[/TR]
[TR]
[TD]1,6[/TD]
[TD="align: right"]2298[/TD]
[/TR]
[TR]
[TD]1,7[/TD]
[TD="align: right"]1572[/TD]
[/TR]
[TR]
[TD]1,8[/TD]
[TD="align: right"]3379[/TD]
[/TR]
[TR]
[TD]1,9[/TD]
[TD="align: right"]8832[/TD]
[/TR]
[TR]
[TD]1,10[/TD]
[TD="align: right"]2627[/TD]
[/TR]
[TR]
[TD]1,11[/TD]
[TD="align: right"]6802[/TD]
[/TR]
[TR]
[TD]1,12[/TD]
[TD="align: right"]4395[/TD]
[/TR]
[TR]
[TD]2,1[/TD]
[TD="align: right"]7504[/TD]
[/TR]
[TR]
[TD]2,2[/TD]
[TD="align: right"]4109[/TD]
[/TR]
[TR]
[TD]2,3[/TD]
[TD="align: right"]9803[/TD]
[/TR]
[TR]
[TD]2,4[/TD]
[TD="align: right"]5461[/TD]
[/TR]
[TR]
[TD]2,5[/TD]
[TD="align: right"]2052[/TD]
[/TR]
[TR]
[TD]2,6[/TD]
[TD="align: right"]2779[/TD]
[/TR]
[TR]
[TD]2,7[/TD]
[TD="align: right"]3397[/TD]
[/TR]
[TR]
[TD]2,8[/TD]
[TD="align: right"]7861[/TD]
[/TR]
[TR]
[TD]2,9[/TD]
[TD="align: right"]8898[/TD]
[/TR]
[TR]
[TD]2,10[/TD]
[TD="align: right"]9587[/TD]
[/TR]
[TR]
[TD]2,11[/TD]
[TD="align: right"]3924[/TD]
[/TR]
[TR]
[TD]2,12[/TD]
[TD="align: right"]6043[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 163"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]IndexID[/TD]
[TD]Part Number[/TD]
[/TR]
[TR]
[TD]1,7[/TD]
[TD="align: right"]1572[/TD]
[/TR]
[TR]
[TD]2,5[/TD]
[TD="align: right"]2052[/TD]
[/TR]
[TR]
[TD]1,4[/TD]
[TD="align: right"]2074[/TD]
[/TR]
[TR]
[TD]1,6[/TD]
[TD="align: right"]2298[/TD]
[/TR]
[TR]
[TD]1,10[/TD]
[TD="align: right"]2627[/TD]
[/TR]
[TR]
[TD]2,6[/TD]
[TD="align: right"]2779[/TD]
[/TR]
[TR]
[TD]1,8[/TD]
[TD="align: right"]3379[/TD]
[/TR]
[TR]
[TD]2,7[/TD]
[TD="align: right"]3397[/TD]
[/TR]
[TR]
[TD]1,1[/TD]
[TD="align: right"]3807[/TD]
[/TR]
[TR]
[TD]2,11[/TD]
[TD="align: right"]3924[/TD]
[/TR]
[TR]
[TD]2,2[/TD]
[TD="align: right"]4109[/TD]
[/TR]
[TR]
[TD]1,12[/TD]
[TD="align: right"]4395[/TD]
[/TR]
[TR]
[TD]1,3[/TD]
[TD="align: right"]4440[/TD]
[/TR]
[TR]
[TD]1,5[/TD]
[TD="align: right"]5021[/TD]
[/TR]
[TR]
[TD]2,4[/TD]
[TD="align: right"]5461[/TD]
[/TR]
[TR]
[TD]2,12[/TD]
[TD="align: right"]6043[/TD]
[/TR]
[TR]
[TD]1,2[/TD]
[TD="align: right"]6680[/TD]
[/TR]
[TR]
[TD]1,11[/TD]
[TD="align: right"]6802[/TD]
[/TR]
[TR]
[TD]2,1[/TD]
[TD="align: right"]7504[/TD]
[/TR]
[TR]
[TD]2,8[/TD]
[TD="align: right"]7861[/TD]
[/TR]
[TR]
[TD]1,9[/TD]
[TD="align: right"]8832[/TD]
[/TR]
[TR]
[TD]2,9[/TD]
[TD="align: right"]8898[/TD]
[/TR]
[TR]
[TD]2,10[/TD]
[TD="align: right"]9587[/TD]
[/TR]
[TR]
[TD]2,3[/TD]
[TD="align: right"]9803

[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 163"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]IndexID[/TD]
[TD]Part Number[/TD]
[/TR]
[TR]
[TD]1,1[/TD]
[TD="align: right"]3807[/TD]
[/TR]
[TR]
[TD]1,10[/TD]
[TD="align: right"]2627[/TD]
[/TR]
[TR]
[TD]1,11[/TD]
[TD="align: right"]6802[/TD]
[/TR]
[TR]
[TD]1,12[/TD]
[TD="align: right"]4395[/TD]
[/TR]
[TR]
[TD]1,2[/TD]
[TD="align: right"]6680[/TD]
[/TR]
[TR]
[TD]1,3[/TD]
[TD="align: right"]4440[/TD]
[/TR]
[TR]
[TD]1,4[/TD]
[TD="align: right"]2074[/TD]
[/TR]
[TR]
[TD]1,5[/TD]
[TD="align: right"]5021[/TD]
[/TR]
[TR]
[TD]1,6[/TD]
[TD="align: right"]2298[/TD]
[/TR]
[TR]
[TD]1,7[/TD]
[TD="align: right"]1572[/TD]
[/TR]
[TR]
[TD]1,8[/TD]
[TD="align: right"]3379[/TD]
[/TR]
[TR]
[TD]1,9[/TD]
[TD="align: right"]8832[/TD]
[/TR]
[TR]
[TD]2,1[/TD]
[TD="align: right"]7504[/TD]
[/TR]
[TR]
[TD]2,10[/TD]
[TD="align: right"]9587[/TD]
[/TR]
[TR]
[TD]2,11[/TD]
[TD="align: right"]3924[/TD]
[/TR]
[TR]
[TD]2,12[/TD]
[TD="align: right"]6043[/TD]
[/TR]
[TR]
[TD]2,2[/TD]
[TD="align: right"]4109[/TD]
[/TR]
[TR]
[TD]2,3[/TD]
[TD="align: right"]9803[/TD]
[/TR]
[TR]
[TD]2,4[/TD]
[TD="align: right"]5461[/TD]
[/TR]
[TR]
[TD]2,5[/TD]
[TD="align: right"]2052[/TD]
[/TR]
[TR]
[TD]2,6[/TD]
[TD="align: right"]2779[/TD]
[/TR]
[TR]
[TD]2,7[/TD]
[TD="align: right"]3397[/TD]
[/TR]
[TR]
[TD]2,8[/TD]
[TD="align: right"]7861[/TD]
[/TR]
[TR]
[TD]2,9[/TD]
[TD="align: right"]8898

[/TD]
[/TR]
</tbody>[/TABLE]

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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
UDF:

Code: 
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:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]
A​
[/td][td="bgcolor:#C0C0C0"]
B​
[/td][td="bgcolor:#C0C0C0"]
C​
[/td][td="bgcolor:#C0C0C0"]
D​
[/td][/tr][tr][td="bgcolor:#C0C0C0"]
1​
[/td][td="bgcolor:#F3F3F3"]
IndexID​
[/td][td="bgcolor:#F3F3F3"]
Part Number​
[/td][td="bgcolor:#F3F3F3"]
Sort​
[/td][td="bgcolor:#F3F3F3"]
​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
2​
[/td][td]1,1[/td][td]
3807​
[/td][td="bgcolor:#E5E5E5"]01,01[/td][td]C2: =PadNum(A2, 2)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
3​
[/td][td]1,2[/td][td]
6680​
[/td][td="bgcolor:#E5E5E5"]01,02[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
4​
[/td][td]1,3[/td][td]
4440​
[/td][td="bgcolor:#E5E5E5"]01,03[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
5​
[/td][td]1,4[/td][td]
2074​
[/td][td="bgcolor:#E5E5E5"]01,04[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
6​
[/td][td]1,5[/td][td]
5021​
[/td][td="bgcolor:#E5E5E5"]01,05[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
7​
[/td][td]1,6[/td][td]
2298​
[/td][td="bgcolor:#E5E5E5"]01,06[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
8​
[/td][td]1,7[/td][td]
1572​
[/td][td="bgcolor:#E5E5E5"]01,07[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
9​
[/td][td]1,8[/td][td]
3379​
[/td][td="bgcolor:#E5E5E5"]01,08[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
10​
[/td][td]1,9[/td][td]
8832​
[/td][td="bgcolor:#E5E5E5"]01,09[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
11​
[/td][td]1,10[/td][td]
2627​
[/td][td="bgcolor:#E5E5E5"]01,10[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
12​
[/td][td]1,11[/td][td]
6802​
[/td][td="bgcolor:#E5E5E5"]01,11[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
13​
[/td][td]1,12[/td][td]
4395​
[/td][td="bgcolor:#E5E5E5"]01,12[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
14​
[/td][td]2,1[/td][td]
7504​
[/td][td="bgcolor:#E5E5E5"]02,01[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
15​
[/td][td]2,2[/td][td]
4109​
[/td][td="bgcolor:#E5E5E5"]02,02[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
16​
[/td][td]2,3[/td][td]
9803​
[/td][td="bgcolor:#E5E5E5"]02,03[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
17​
[/td][td]2,4[/td][td]
5461​
[/td][td="bgcolor:#E5E5E5"]02,04[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
18​
[/td][td]2,5[/td][td]
2052​
[/td][td="bgcolor:#E5E5E5"]02,05[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
19​
[/td][td]2,6[/td][td]
2779​
[/td][td="bgcolor:#E5E5E5"]02,06[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
20​
[/td][td]2,7[/td][td]
3397​
[/td][td="bgcolor:#E5E5E5"]02,07[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
21​
[/td][td]2,8[/td][td]
7861​
[/td][td="bgcolor:#E5E5E5"]02,08[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
22​
[/td][td]2,9[/td][td]
8898​
[/td][td="bgcolor:#E5E5E5"]02,09[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
23​
[/td][td]2,10[/td][td]
9587​
[/td][td="bgcolor:#E5E5E5"]02,10[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
24​
[/td][td]2,11[/td][td]
3924​
[/td][td="bgcolor:#E5E5E5"]02,11[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
25​
[/td][td]2,12[/td][td]
6043​
[/td][td="bgcolor:#E5E5E5"]02,12[/td][td][/td][/tr]
[/table]
 
Upvote 0
Oops, pasted old version:

Code: 
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
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top