Sorting Alphanumeric values in excel based on the Numerical Value

[EAG1]

Hello all,

Could anyone advise how we could sort the following list of numerical and alphanumerical values in a numerical way?

Issue: when sorted in excel the values with alphabetical characters in are dropped to the bottom of the list. See data below:

275
453
523
531
622
644
1046
1055
1055XL
485XL
585XL
685XL

The way we want the data sorted is below. So just taking into account the numerical value in the cell.

275
453
485XL
523
531
585XL
622
644
685XL
1046
1055
1055XL

Hope the above makes sense. Any help would be greatly appreciated.

Thanks, EAG1

 

[Anonymous1378]

Try this? I'm assuming your cells will only contain capital letters:

 

[Fluff]

Similar but slightly shorter

Fluff.xlsm
	A	B
1
2	275	275
3	453	453
4	523	485XL
5	531	523
6	622	531
7	644	585XL
8	1046	622
9	1055	644
10	1055XL	685XL
11	485XL	1046
12	585XL	1055
13	685XL	1055XL
Sheet4

Cell Formulas
Range	Formula
B2:B13	B2	=SORTBY(A2:A13,--TEXTBEFORE(A2:A13,CHAR(SEQUENCE(26,,65)),,,1))
Dynamic array formulas.

 

[Anonymous1378]

Well that's embarrassing, I misremembered "A" as CHAR(67). Also, TIL what the match_end criteria does.

 

[Legacy 143009]

The most unefdicient way:

Sub test()

  Dim lRow As Long, myArr() As Variant
  lRow = Cells(Rows.Count, "A").End(xlUp).Row
  myArr = Range("A1:A" & lRow)
  ReDim Preserve myArr(1 To UBound(myArr), 1 To 3)
 
  For i = 1 To UBound(myArr, 1)
    For j = 1 To 2
      myArr(i, j + 1) = splitAlphaNumeric(myArr(i, 1), j)
    Next
  Next
 
  Dim tmp As Variant
  For i = 1 To UBound(myArr, 1) - 1
    For j = i + 1 To UBound(myArr, 1)
      If CLng(myArr(j, 2)) < CLng(myArr(i, 2)) Then
        For k = 1 To 3
          tmp = myArr(i, k)
          myArr(i, k) = myArr(j, k)
          myArr(j, k) = tmp
        Next
      End If
    Next
  Next
 
  For i = 1 To UBound(myArr, 1) - 1
    If myArr(i, 2) = myArr(i + 1, 2) Then
      If myArr(i + 1, 3) < myArr(i, 3) Then
        tmp = myArr(i, 1)
        myArr(i, 1) = myArr(i + 1, 1)
        myArr(i + 1, 1) = tmp
      End If
    End If
  Next
 
  Range("A1").Resize(UBound(myArr, 1)).Value2 = Application.Index(myArr, 0, 1)
End Sub
Function splitAlphaNumeric(ByVal alphanumeric As String, ByVal mode As Integer) As Variant

  Select Case mode
  Case 1
    For c = 1 To Len(alphanumeric)
      If IsNumeric(Mid(alphanumeric, c, 1)) Then
        splitAlphaNumeric = splitAlphaNumeric + Mid(alphanumeric, c, 1)
      End If
    Next
  Case 2
    For c = 1 To Len(alphanumeric)
      If Not IsNumeric(Mid(alphanumeric, c, 1)) Then
        splitAlphaNumeric = splitAlphaNumeric + Mid(alphanumeric, c, 1)
      End If
    Next
  End Select

End Function

 

[EAG1]

Thank you all for the responses. They are very helpful.

@Fluff we have used yours with our data set and it worked however I forgot to mention that some the data has letters at the start (with numbers after) and some have letters at the start, numbers in the middle and letters at the end. As well as the odd "." here and there. Would you be able to modify the formula to take this into account?

Current sort format in excel:

275
453
523
531
622
644
1046
1055
1055XL
485XL
585XL
685XL
C100
C50
C60
MX100A
MX100C
MX80C
T6.120
T6.140
T6020
T6040

Sort format required:

275
453
485XL
523
531
585XL
622
644
685XL
1046
1055
1055XL
C50
C60
C100
MX80C
MX100A
MX100C
T6.120
T6.140
T6020
T6040

Thank you all again for your assistance.
EAG1

 

[Fluff]

You should have given all those examples at the start as it is a completely different problem & you will probably need a macro.

 

[Anonymous1378]

I'm not dealing with any more shifting goalposts after this:

=LET(
_a,E1:E22,
_b,TEXTBEFORE(_a,SEQUENCE(10,,0)),
_c,TEXTBEFORE(_a,CHAR(SEQUENCE(26,,65)),,,1),
_d,IFERROR(--(_b&_c),_b),
_e,TEXTAFTER(_a,SEQUENCE(10,,0),-1),
_f,--SUBSTITUTE(SUBSTITUTE(_a,_d,""),_e,""),
_g,SORTBY(_a,_d,1,_f,1,_e,1),_g)

 

[EAG1]

@Anonymous1378 that works. Thank you.