Forum Discussion

Makoman295's avatar
Makoman295
Copper Contributor
Sep 27, 2022

Trying to organize by numbers in excel

Hello, I've been searching around for a while now but I can't seem to find the answer or I'm just doing it wrong. I'm trying to sort a column I have listed like I5, I6, I7, I8, I13, I16, I17, I18 and I19 alphabetically but it organizes it like I13, I16, I17, I18, I19, I5, I6, I7, I8.

It's not just one letter, I have these from C-V.
The closest I've come to was doing =Len(B3) in another column and then organizing that way but that still doesn't organize it by the Alphanumerically.
 

Is there anyway I can organize it like how I intend for it to be?

 

Thank you for your help.

excel
Formulas and Functions
office 365

1 Reply

  • alannavarro's avatar
    alannavarro
    Iron Contributor
    Sep 28, 2022

    Makoman295  I think there might be a simple solution with lambda or dynamic arrays, here is something that I found online, it works with vba, is going to create a function called "num" that is going to create an index starting from 1 for the range you select. Attached is an example.


    https://www.extendoffice.com/documents/excel/3451-excel-sort-numbers-with-text-alpha-text-prefix-suffix.html

     

    Function num(rng As Range) As Integer
    Dim n As Integer
    For n = 1 To Len(rng)
    If Mid(rng, n, 1) Like "[0-9]" Then
    num = num & Mid(rng, n, 1)
    End If
    Next n
    End Function

Resources