Sorting Alphanumeric values in excel based on the Numerical Value

EAG1

New Member
Joined
Feb 12, 2021
Messages
23
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Try this? I'm assuming your cells will only contain capital letters:

1690540798987.png
 
Upvote 0
Similar but slightly shorter
Fluff.xlsm
AB
1
2275275
3453453
4523485XL
5531523
6622531
7644585XL
81046622
91055644
101055XL685XL
11485XL1046
12585XL1055
13685XL1055XL
Sheet4
Cell Formulas
RangeFormula
B2:B13B2=SORTBY(A2:A13,--TEXTBEFORE(A2:A13,CHAR(SEQUENCE(26,,65)),,,1))
Dynamic array formulas.
 
Upvote 0
Well that's embarrassing, I misremembered "A" as CHAR(67). Also, TIL what the match_end criteria does.
 
Upvote 0
The most unefdicient way:
VBA Code:
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
 
Last edited by a moderator:
Upvote 0
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
 
Upvote 0
You should have given all those examples at the start as it is a completely different problem & you will probably need a macro.
 
Upvote 0
I'm not dealing with any more shifting goalposts after this:

Excel Formula:
=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)
 
Upvote 0
Solution

Forum statistics

Threads
1,223,948
Messages
6,175,573
Members
452,652
Latest member
eduedu

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