Excel 2010 A-Z sort question

Discussion in 'Math' started by Andreas, Jul 23, 2013.

  1. Andreas

    Thread Starter Active Member

    Jan 26, 2009
    68
    4
    Hi,

    I have several hundred entries in an Excel spreadsheet that I need to sort alphabetically from A-Z. Sounds easy eh, but...

    The problem is that EVERY entry starts with 8 numerical digits, 3 alphabet characters and an underscore followed by a string of letters.

    eg: 20130201ABC_jackson_bob

    It is the string after the first underscore that I need Excel to sort from A-Z not the number before it as it tries to do!

    Is there a way to customise the cells so that it ignores the first 12 characters or or do I need to write a Macro (VBA) to do this :confused:

    Tnx for any help you may have.
     
  2. WBahn

    Moderator

    Mar 31, 2012
    17,788
    4,808
    Neither. Use a formula in a parallel column to parse out the part you want to sort on and then sort both columns on the contents of the parallel column. Since the first character you want to sort on is always the 13th character, this will be easy using the MID() function.
     
    Andreas likes this.
  3. Andreas

    Thread Starter Active Member

    Jan 26, 2009
    68
    4
    Thanks. That worked and it was easier than I thought. A little help goes a long way.
     
  4. WBahn

    Moderator

    Mar 31, 2012
    17,788
    4,808
    Excel (spreadsheets in general) are amazingly powerful tools if you are willing to spend some time getting familiar with the functions that are available combined with learning how to look at problems from a somewhat different perspective. Seldom do you need to resort to VBA or some equivalent unless you want to get really fancy or make a GUI or control an instrument or something like that.
     
  5. atferrari

    AAC Fanatic!

    Jan 6, 2004
    2,653
    768
    Yes, it pays to check them from time to time.

    The last I seen used is conditional formating that helps a lot in visualizing ranges amongst results. Very effective. Have to apply that myself at any moment.
     
Loading...