Sort AlphaNumeric Text By Numbers

  • Hi


    Take for example ABC10 in Cell A1, ABC199 in cell A2 and ABC9 in cell A3


    How am i able to sort such that ABC9 appears in cell A1 followed by ABC10 in cell A2 and ABC199 in cell A3?


    VBA advice would be appreciated.


    Thanks.

  • Re: Sorting Numbers Before Alphabets Within A Cell


    Use the Custom Function here called Extract Numbers From AlphaNumerics In another Column. Then Copy Edit>Paste Special - Values and sort both columns by the numeric column.

  • Re: Sort AlphaNumeric Text By Numbers


    Thanks for the swift reply.


    However, Due to circumstances, I am not allowed to create an additional column. Would there be a macro to do all this together?


    Cheers

  • Re: Sort AlphaNumeric Text By Numbers


    Quote from dtwk

    ...However, Due to circumstances, I am not allowed to create an additional column. ...


    Not even temporarily? Add the column with the formula, sort, delete the added column?


    Regards,

  • Re: Sort AlphaNumeric Text By Numbers


    Hi DTWK -


    Clarify again -
    are the first 3 letters ALWAYS "ABC"?
    are the numbers ALWAYS 3 digits?
    would it be acceptable to change the numbers into 3 digit numbers where by ABC9 would become ABC009


    If all of the above are true, its not that difficult to write VBA, if either rule is broken, it becomes a little more complicated to write a generic routine.


    Ger

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

  • Re: Sort AlphaNumeric Text By Numbers


    Use this code

  • Re: Sort AlphaNumeric Text By Numbers


    or maybe just something simple like


    Code
    Sub sortwithletters()
    With Range("a1", Range("a1").End(xlDown))
       .Replace What:="ABC", Replacement:="99999999", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False
        .Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
          .Replace What:="99999999", Replacement:="ABC", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False
    End With
    End Sub
  • Re: Sort AlphaNumeric Text By Numbers


    nice idea....

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

  • Re: Sort AlphaNumeric Text By Numbers


    dtwk,


    Another way. Uses blank rows on the sheet to split and sort.



    This version is for a single column of data


    This version works on multiple columns of data


    Cheers,


    dr

  • Re: Sort AlphaNumeric Text By Numbers


    Quote from Badger101

    or maybe just something simple like


    Code
    Sub sortwithletters()
    With Range("a1", Range("a1").End(xlDown))
       .Replace What:="ABC", Replacement:="99999999", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False
        .Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
          .Replace What:="99999999", Replacement:="ABC", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False
    End With
    End Sub

    DOH! :)

  • Re: Sort AlphaNumeric Text By Numbers


    Nice Badger... It works perfectly... Thanks a lot to all u guys... been a great help since i registered to this website... Thanks to Dave and all... /bow /salute

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!