Sorting Alphanumeric Data in Excel

Discussion in 'Computing and Networks' started by Brownout, Jun 27, 2013.

  1. Brownout

    Thread Starter Well-Known Member

    Jan 10, 2012
    2,375
    998
    I have vector data that contains characters and numerals, ie

    vector<0>
    vector<1>
    ...
    vector<10>
    vector<11>
    ....
    vector<20>
    vector<21>
    ect.

    If I sort this data in Excel, I get this?

    vector<0>
    vector<1>
    vector<10>
    vector<11>
    ...
    vector<2>
    vector<20>
    vector<21>
    etc.

    Anyone ever get this to work correctly?
     
  2. LDC3

    Active Member

    Apr 27, 2013
    920
    160
    I just tried it and that is what I get.
     
  3. BillB3857

    Senior Member

    Feb 28, 2009
    2,400
    348
    Change the <1> to <01>, the <2> to <02>. etc and see what happens. (add leading zeros to fill out the maximum length of the count, ie 0001 if the max count will be 9999)
     
  4. Brownout

    Thread Starter Well-Known Member

    Jan 10, 2012
    2,375
    998
    I'll give that a try, however this data is generated, not created. I get it from software tools I use for design place and route. I would much rather have a method that doens't involve hand-editing the data each time I run the tools, if one exists.
     
  5. djsfantasi

    AAC Fanatic!

    Apr 11, 2010
    2,795
    831
    Copy the data into a new column. Use the Excel text to data feature to parse out only the number into its own column. Then, set that column to be a numerical data type and sort on it.

    When using text to data, specify the less than symbol as your delimiter and also do not use / import the first column (containing "vector"). You may have to parse a second time with the greater than symbol to remove it as well.
    After sorting, any work columns can be deleted.

    Text to data in Excel is a great tool to process raw data and convert it into a more usable form. These steps can be saved into a macro, so the conversion and sorting can be done with a keystroke.
     
    Last edited: Jun 27, 2013
  6. tshuck

    Well-Known Member

    Oct 18, 2012
    3,531
    675
    You can also make a custom sort...

    Click sort, click the dropdown box under "Order", and select "Custom List...". There you enter in the proper, sorted list once, and it will sort according to this when selected.

    It's a bit of a pain to make the list, but works...
     
  7. LDC3

    Active Member

    Apr 27, 2013
    920
    160
    I'm using OpenOffice so I'm not certain is this will work in Excel.

    In the Find and Replace dialog box:
    Code ( (Unknown Language)):
    1. Find - \<(\d)\>
    2. Replace - 0$0
    3. Click 'More Options' and select 'Regular Expressions'
    4. Click 'Replace All'
    There should be an equivalent method in Excel.
     
  8. Brownout

    Thread Starter Well-Known Member

    Jan 10, 2012
    2,375
    998
    I got this to work. Here is the equation I ended up with:

    =VALUE(MID(B1,SEARCH("<",B1)+1,SEARCH(">",B1)-SEARCH("<",B1)-1))

    this is for the first cell in the list, hence, the "B1". The other equations auto-increment as I drag them through.


    Narly, eh?
     
  9. Brownout

    Thread Starter Well-Known Member

    Jan 10, 2012
    2,375
    998
    My equivalent method (see above) is alot more complicated. I'll give this a try too.

    Edit: can't find an option for regualr expressions. I'll try this with Open Office.
     
  10. Brownout

    Thread Starter Well-Known Member

    Jan 10, 2012
    2,375
    998
    Ok, so I have something called "Text to Columns" which works exactly as you have described. Both of these methods work well.
     
Loading...