SKUs not sorting by numerical order

[MM1287]

I've seen this asked many times on the internet but I can't seem to find someone asking how to sort part numbers that also contain text. For example, I have the following part numbers and want them sorted in this way:

5-part
7-part
156-part
691-part

When sorting, from smallest to largest, it does the following:

156-part
5-part
691-part
7-part

It looks like it uses the first digit when I instead want it to sort by the full part number. Is the only way to do this by extracting the part number from the whole SKU into its own new column, make sure the cells are formatted as a number and then sort by this new column?

 

[Fluff]

Either that or change the part numbers to include leading zeros where needed.

 

[shg]

You could use a small user-defined function:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Input​

[/td][td="bgcolor:#F3F3F3"]

Sort​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]156-part[/td][td="bgcolor:#CCFFCC"]156-part[/td][td="bgcolor:#CCFFCC"]B2: =PadNum(A2, 3)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]5-part[/td][td="bgcolor:#CCFFCC"]005-part[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]691-part[/td][td="bgcolor:#CCFFCC"]691-part[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]7-part[/td][td="bgcolor:#CCFFCC"]007-part[/td][td][/td][/tr]
[/table]

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