+ Reply to Thread
Results 1 to 8 of 8

Sort by numbers like 1.1.2....

  1. 11-29-2012, 07:58 PM #1
    zamehan
    zamehan is offline
    Registered User
    Join Date
    11-29-2012
    Location
    paris, france
    MS-Off Ver
    Excel 2010
    Posts
    2

    Sort by numbers like 1.1.2....

    Hi!


    I am a complete nexbie in Excel.

    So I have a list of files in folders.

    When I make the index of those folder, i have an excel list with in column A these numbers

    1.1.1.1 (so the file 1 in the 1st subsubfolder in the 1st subfolder in the first folder)
    1.1.1
    1.2.1.1
    1.2.2
    1.3.1


    etc... The list goes on with 1000s rows.

    And in colum B I have the names of the files

    Unfortunately, when i sort the spreadsheet with the number on column A, the order is not good....

    So I would like to find a solution to sort things in the good order.

    Thank you !
    Reply With QuoteRegister To Reply
  2. 11-29-2012, 08:02 PM #2
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Sort by numbers like 1.1.2....

    what do you have and what is the expected result
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula
    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC
    Reply With QuoteRegister To Reply
  3. 11-29-2012, 09:10 PM #3
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Sort by numbers like 1.1.2....

    ...and why do you want to sort the table?

    Many people who want to see similar items grouped together think they need to sort, when filtering the data by one or more columns gives them what they want more easily. Maybe not in your case but worth asking what is your end goal.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
    Reply With QuoteRegister To Reply
  4. 11-29-2012, 10:30 PM #4
    Teethless mama
    Teethless mama is offline
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: Sort by numbers like 1.1.2....

    Try this:

    See the attached
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 11-30-2012, 04:44 AM #5
    zamehan
    zamehan is offline
    Registered User
    Join Date
    11-29-2012
    Location
    paris, france
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Sort by numbers like 1.1.2....

    Thank you mama !

    However, the lenght of the numbers can be much longer like 5.5.19.3.4.3.2.1.1.1

    And some are really short like 5.5.20

    So the formula seems not to work with longer numbers.
    Reply With QuoteRegister To Reply
  6. 11-30-2012, 07:23 AM #6
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Sort by numbers like 1.1.2....

    try this udf,i came across it when looking for something to sort ip addresses ,insert in a new module then use as =NormDigits(a1) drag down then sort by that column
    1.3.2.1
    1.2.3
    1.2.3.1
    5.1.3
    5.5.19.3.4.3.2.1.1.1
    5.3.1
    5.1
    1.2.1
    1.1.2
    5.5.18.3.4.3.2.1.1.2

    sorts as
    1.1.2
    1.2.1
    1.2.3
    1.2.3.1
    1.3.2.1
    5.1
    5.1.3
    5.3.1
    5.5.18.3.4.3.2.1.1.2
    5.5.19.3.4.3.2.1.1.1


    code from here http://dmcritchie.mvps.org/excel/sorttcp.htm#chaptsort
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  7. 11-30-2012, 10:06 AM #7
    Teethless mama
    Teethless mama is offline
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: Sort by numbers like 1.1.2....

    =(substitute(a1,".","")&rept(0,11-len(substitute(a1,".",""))))+0
    Reply With QuoteRegister To Reply
  8. 11-30-2012, 10:30 AM #8
    WHER
    WHER is offline
    Valued Forum Contributor
    Join Date
    07-29-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2003/Excel 2010
    Posts
    534

    Re: Sort by numbers like 1.1.2....

    One more UDF:
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1