Results 1 to 8 of 8

Thread: Sort Mixed Info in Excel

  1. #1

    Sort Mixed Info in Excel



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi All: I'm trying to sort mixed info in excel. Does anyone know a formula for this? Below is a sample of what I'm trying to sort. Thanks!

    100030
    117A
    2304A
    2607AA
    5037A
    8104A
    8163A
    8400A
    8458A
    8873B
    8947A
    9242A
    9578A
    A
    B
    C

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,231
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    What are you trying to sort it by? I.e. what order would you expect the above to be sorted into?
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Hi Ken: I'm trying to sort by number. So, my column should look like this:

    117A
    2304A
    2607AA
    5037A
    8104A
    8163A
    8400A
    100030
    A
    B
    C

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,231
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Curious... why a formula? It seems to be sorting fine using the built in sort commands from the ribbon...
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    I guess the prob is that I don't want the numbers with letters attached sorted at the bottom. So, if there is a 1655 I would like 1655-A to follow. These are exhibits for a trial so I also can't pad them with 0000's.
    4013
    4014
    5050
    8536
    1655-A
    1657-A
    1657-B
    1659-C
    1704-A
    1712-A
    1715-A
    1720-A
    1726-A
    1732-A
    1733-A
    1851-A
    1917-A
    1958-A
    1958-A
    1979-A
    2214-A
    238-A
    238-C
    243-A
    243-B
    2501-DA
    2501-DB
    2501-LA
    2501-NA
    2502-2
    2605-A
    2607-B
    2607-C
    2607-D
    2607-E
    2607-F
    2607-G
    2607-H
    2607-J
    3000-S
    3001-S
    3001-S
    3002-S
    3003-S
    3004-S
    3005-S
    3006-S
    3007-S
    3008-S
    3009-S
    3010-S
    3011-S
    3012-S
    3013-S
    314-A
    3510-3
    3516-3
    461-A
    586-A
    586-B
    600-A
    719-B
    720-A
    775-A
    780-A
    781-A
    8529-A
    8529-B
    8529-C
    905-A
    919-A

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,231
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Ah, okay, I get what you mean.

    I'm assuming that there are no mathematical calculations that are ever done off this column, correct? If you convert them to text, then they will sort exactly as you're after.

    To do that, you're going to need to select all the cells, then go to Format Cells-->Number-->Text

    The rub is that it still won't work properly until you re-commit any "numbers" to the cell so that Excel can reformat them as text properly. To do that, you pretty much just need to go to the cell, press F2, then press Enter.

    If you have a lot of them, then you can do it with a macro:

    Code:
    Sub RePaste()
    Dim cl as Range
    
    For each cl in Selection
    cl.value = cl.text
    Next cl
    
    End Sub
    You still need to set the range as text first, then copy that into a standard module (see the steps in my signature), select the cells in question, and run it.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  7. #7
    Correct no mathematical calculations performed. So, I was reformatting the cells using F2 and enter and I noticed the green dash does not appear in the cells that have a number and letter (ie 1655-A). Any thoughts on why?

  8. #8
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,231
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Yep.

    The green mark is Excel's way of telling you that the cell format in inconsistent with the others. It's because you've forced it to text rather than it being implicitly converted. It's nothing to worry about.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

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