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 lette...
BI & Data Analysis
excel
office 365
ccotter's avatar
ccotter
Copper Contributor
Jan 17, 2019

More like this.

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.

  • ccotter's avatar
    ccotter
    Copper Contributor
    Jan 17, 2019

    Here is what I am working with.  The current is the top section and the expected is the bottom.  I have roughly 4k tools to sort.

    • IngeborgHawighorst's avatar
      IngeborgHawighorst
      MVP
      Jan 17, 2019

      That helps, thanks.

       

      This now shows me that there is no pattern that we can use in a Left or Mid function by just looking at the dash. Instead, the approach needs to be to use a helper column that extracts all numeric characters from the cell.

       

      Here is the formula that does that to the number in F5 (change accordingly)

       

      =SUMPRODUCT(MID(0&F5,LARGE(INDEX(ISNUMBER(--MID(F5,ROW($1:$25),1))*
      ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

       

      And here is a screenshot to see this in action.

       

       

      Does that get you any further?

Resources