+ Reply to Thread
Results 1 to 3 of 3

Sort Letter Number

  1. #1
    Registered User
    Join Date
    12-12-2020
    Location
    Los Angeles
    MS-Off Ver
    Excel 2007
    Posts
    1

    Sort Letter Number

    Hello
    I don't know how to sort an letter with numbers. I need to create a sequence of these audio tapes from number to the end
    Example
    t100-panel.mp3
    t101-kashouty-haynes.mp3
    t102-haynes.mp3
    t103-hayes-meherwan.mp3
    t104-stevnes.mp3
    t10-gim-mcgraw.mp3
    t10-gim-mcgraw-alt.mp3
    t10-gim-mcgraw-fin.mp3
    t11-gim-mcgraw.mp3
    t11-gim-mcgraw-fin.mp3
    t11-gim-mcgraw-fin_01.mp3
    t12-v-small.mp3
    t139-hopkinson.mp3
    t13-lud-dimpfl.mp3
    t140-filis.mp3
    t23-adi dubash-a-cohen.mp3
    t23-side-b-adi-dubash-minoo-kharas.mp3
    t23-side-b-allan-cohen.mp3
    t24-a-cohen.mp3
    t24-side-a-allan-cohen.mp3
    t24-side-b-bill-lepage.mp3
    t25-dubash-abzar.mp3
    t25-rhoda-dubash-side-a.mp3
    t25-side-b-naosherwan-anzar.mp3

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2404 Win 11 Home 64 Bit
    Posts
    23,856

    Re: Sort Letter Number

    1. In Cell B1 enter this formula =SUBSTITUTE(A1, "-", "|", 1) and copy down
    2. Highlight column B and copy and then paste special Values back onto Column B. This allows you then split column by the "|" (Pipe) delimeter.
    3. Highlight Column B, On the Data Tab, Text to Columns. Delimeter is "|". Finish with data starting in Column B
    4. Insert a new column C so you have an empty column
    5. Highlight column B and Data Tab, Text to column. Fixed Width, Make it one so that the T separates from the number
    6. Now Highlight the whole range starting with Cell A1.
    7. On the Data Tab, select Sort, Select Column C and select smallest to largest
    8. Delete all columns except Column A.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,704

    Re: Sort Letter Number

    It would be a lot easier if you were able to arrange the same number of digits after the initial "t", by inserting leading zeros, so that t100... would remain the same whereas t10... and t23... would become t010... and t023... respectively. If you could do that then they would sort without problems.

    If you can't do that then you would need to extract the numeric part (i.e. after the t and before the first hyphen) into an adjacent cell, and then you could sort both columns using that number as the sort field.

    Hope this helps.

    Pete

+ 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. Replies: 17
    Last Post: 07-16-2009, 03:12 AM
  2. Sort by number not by letter (C1, A2, B3, D4)
    By dukemeiser in forum Excel General
    Replies: 3
    Last Post: 05-04-2006, 09:45 PM
  3. Format:change headers from letter to number/number to letter
    By Biff in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2005, 04:05 AM
  4. Replies: 2
    Last Post: 05-09-2005, 04:06 PM

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