Forum Discussion

ccotter's avatar
ccotter
Copper Contributor
Jan 16, 2019

Sort numerically with - A-Z after them.

I am trying to sort a large tool room list numerically so it can be reorganized.  The problem I am running into is have the sheet has numbers only for the tools and the other half the sheet has letters or numbers after the number with a dash (51701-blt).  I have seven columns in this spreadsheet:

quantity, tool number, description, section,kit, sort order, notes, and location.

 

I appreciate anyone's help in this.  I want to sort by sort order.

BI & Data Analysis
excel
office 365

5 Replies

  • IngeborgHawighorst's avatar
    IngeborgHawighorst
    MVP
    Jan 17, 2019

    Hello,

     

    I assume that the problem is that number will be sorted after text, like in this screenshot, is that correct?

     

     

    When cells contain a mix of text and numbers, text will be sorted after the numbers. If you want to sort strictly by the numerical value, you need to pull the number into a helper column. The formula to do that is showing in cell E12

     

    =LEFT(D12,FIND("-",D12&"-")-1)+0

     

    Now you can sort the table by the helper column.

      • IngeborgHawighorst's avatar
        IngeborgHawighorst
        MVP
        Jan 17, 2019

        Would you like me to keep guessing what you want to achieve and what the problems are, or are you prepared to speed things up a bit and use your own words?

         

        So, you have text that starts with numeric characters and has leading zeros. Fine. 

         

        Now what? With real numbers ranging from ?? to ??, where would you like to see your text cells in the sort order? Please give me something to aim for.

         

        Mock it up and post the expected result, then things will be so much easier to work out.

Resources