SKUs not sorting by numerical order

MM1287

New Member
Joined
Jun 8, 2014
Messages
19
Office Version
  1. 2016
Platform
  1. Windows
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?
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Either that or change the part numbers to include leading zeros where needed.
 
Upvote 0
You could use a small user-defined function:

A​
B​
C​
1​
Input
Sort
2​
156-part156-partB2: =PadNum(A2, 3)
3​
5-part005-part
4​
691-part691-part
5​
7-part007-part

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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

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