+ Reply to Thread
Results 1 to 10 of 10

Sort alphanumeric input alphabetically and numerically

  1. #1
    Registered User
    Join Date
    05-12-2022
    Location
    Europe
    MS-Off Ver
    Professional Plus 2016
    Posts
    4

    Sort alphanumeric input alphabetically and numerically

    Hello

    Input:
    C1, C2
    C4
    C10
    C5
    C56,C78
    D2
    C658
    C101
    D1
    C6


    all entries are unique (LNNN L-letter, NNN-number; there is only one combination LNNN in database e.g. there is no other entry which contain C10 for example)

    Desired output
    C1, C2
    C4
    C5
    C6
    C56, C78
    C658
    C101
    D1
    D2

    What Excel does :
    C1, C2
    C10
    C101
    C4
    C5
    C56, C78
    C6
    C658
    D1
    D2



    Desired sort criteria : sort by first character : first character is a letter so sort alphabetically from A to Z;
    if there are more rows which have identical first letter, then sort by second character which is a number;
    sort these entries as numbers , treat them as numbers not as letters.; e.g. 1,2,3..., 10, 11..., 20... not 1, 10, 100, 2, 20, 200 ....

    Can anyone help me with some VBA code ?
    I have several columns like that one, so a solution that can accept multiple columns would be even better
    The columns have variable length

    Thank you

  2. #2
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: Sort alphanumeric input alphabetically and numerically

    If your original range is in A1:A10 then does this give the right sort order?

    Please Login or Register  to view this content.
    WBD
    Office 365 on Windows 11, looking for rep!

  3. #3
    Registered User
    Join Date
    05-12-2022
    Location
    Europe
    MS-Off Ver
    Professional Plus 2016
    Posts
    4

    Re: Sort alphanumeric input alphabetically and numerically

    I'm not sure

    LET is not recognized as formula
    should it be LEFT ?
    I tried with LEFT but, it seems it does not recognize the other letters
    Could you tell me what are the r,l,a,t,n from the formula?

    Also due to my local language, I have replaced some commas (,) with dot and comma ( ; ) as arguments separator , I hope I have separated them properly

    Please Login or Register  to view this content.
    Last edited by fionut; 05-12-2022 at 07:30 PM.

  4. #4
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2402 and WPS V2022
    Posts
    3,318

    Re: Sort alphanumeric input alphabetically and numerically

    Cell B1 formula

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    05-12-2022
    Location
    Europe
    MS-Off Ver
    Professional Plus 2016
    Posts
    4

    Re: Sort alphanumeric input alphabetically and numerically

    Nope , it does not compute it

  6. #6
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2402 and WPS V2022
    Posts
    3,318

    Re: Sort alphanumeric input alphabetically and numerically

    ANS. POST#5

    Maybe it's a different country, so change " , " to " ; "

    Try again

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by wk9128; 05-13-2022 at 01:53 AM.

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Sort alphanumeric input alphabetically and numerically

    Do you ACTUALLY have Office 365?? Are you testing the solution on a PC with an older Excel version?

    See file.
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  8. #8
    Registered User
    Join Date
    05-12-2022
    Location
    Europe
    MS-Off Ver
    Professional Plus 2016
    Posts
    4

    Re: Sort alphanumeric input alphabetically and numerically

    Sorry, I think that was the problem
    I have Office Proffesional Plus 2016

  9. #9
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: Sort alphanumeric input alphabetically and numerically

    That's the sound of tumbleweed blowing down the street. We gave you solutions for your reported version of office. Not sure how you'd achieve the same in your actual version - you will probably need a helper column which you can then sort by. See attached for an example of generating this column.

    WBD
    Attached Files Attached Files

  10. #10
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2402 and WPS V2022
    Posts
    3,318

    Re: Sort alphanumeric input alphabetically and numerically

    @fionut Since you provided a personal profile clearly states Office 365 before, so I will provide a 365 solution. At present, you have updated to 2016, which can only be handled by the auxiliary column method.

    you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower
    left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

    If you finally get a solution please mark your thread as SOLVED:
    - Click Thread Tools above your first post,you will see the word PREFIX on the upper left, press the button to select [SOLVED] select "Mark your thread as Solved"
    Last edited by wk9128; 05-13-2022 at 08:32 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro to Order Numerically Then Alphabetically
    By smuntl in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-13-2014, 03:09 PM
  2. Replies: 7
    Last Post: 07-02-2012, 07:23 PM
  3. RANK numerically and then alphabetically
    By liam-the-1-n-only in forum Excel General
    Replies: 7
    Last Post: 05-12-2010, 10:30 AM
  4. 2 columns, can I sort them numerically?
    By Gol in forum Excel General
    Replies: 2
    Last Post: 08-05-2009, 08:10 PM
  5. sort alphabetically and numerically, then sort rows
    By luke20allen in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-01-2008, 07:00 AM
  6. I need to sort alphabetically and then numerically
    By luke20allen in forum Excel General
    Replies: 3
    Last Post: 09-18-2008, 10:22 PM
  7. Replies: 1
    Last Post: 01-17-2007, 06:43 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1