Custom Sorting w/ 1, 1A, 1b,2,3,3a, etc.

[Mstg007]

I am trying to setup a custom sort that can put these in order. Right now, when I sort the list, all the number and letter objects sort at the bottom. Am I looking at this wrong?

The source list:

10
8
5A
9
4
8A
7
8B
6
5
2A
2
1
3
2B


What I would like it to be:

1
2
2A
2B
3
4
5
5A
6
7
8
8A
8B
9
10


When I sort:
1
2
3
4
5
6
7
8
9
10
2A
2B
5A
8A
8B



Thanks for any help!

 

[Macropod]

A quick & dirty way would be to copy & paste the range into Word as a table, then sort the table as text before copying & pasting to sorted data back into Excel.

 

[shg]

UDF?

	A​	B​	C​
1​	Input​	Sort​	​
2​	10​	010	B2: =PadNum(A2, 3)
3​	8​	008
4​	5A	005A
5​	9​	009
6​	4​	004
7​	8A	008A
8​	7​	007
9​	8B	008B
10​	6​	006
11​	5​	005
12​	2A	002A
13​	2​	002
14​	1​	001
15​	3​	003
16​	2B	002B

Sort by the Sort column.

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

  ' Expands numbers in a string to iLen characters for sorting; e.g.,
  '   PadNum("13A1U3", 2)    = "13A01U03"
  '   PadNum("1.2.3.15", 3)  = "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"

  ' All characters other than digits 0-9 are returned as-is

  Dim sFmt          As String
  Dim iChr          As Long
  Dim sNum          As String
  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   ' the +1 flushes a trailing number
    sChr = Mid(sInp, iChr, 1)
    If sChr Like "#" Then
      bNum = True
      sNum = sNum & sChr
    Else
      If bNum Then
        bNum = False
        
        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
      End If
      PadNum = PadNum & sChr
    End If
  Next iChr
End Function

 

[Mstg007]

Awesome. Thank you for the info. If I have any probs, ill let you know! Again, Thank you!

 

[Peter_SSs]

Another code option to try, assuming ..
- Data starts in A2
- The leading numbers are positive integers
- The leading numbers are <= 5 digits (easily increased by changing the AL.Add line of code)

Test in a copy of your workbook as the data will be over-written.

Sub SortEm()
  Dim AL As Object
  Dim a As Variant, e As Variant

  Set AL = CreateObject("System.Collections.ArrayList")
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    a = .Value
    For Each e In a
      If Len(e) > 0 Then
        AL.Add Format(Val(e), "00000") & Replace(e, Val(e), "") & ";" & e
      End If
    Next e
    AL.Sort
    Application.ScreenUpdating = False
    .Value = Application.Transpose(AL.ToArray)
    .TextToColumns DataType:=xlDelimited, Tab:=False, Semicolon:=True, _
                   Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 9), Array(2, 1))
    Application.ScreenUpdating = True
  End With
End Sub