Any Excel experts out there?

I'm trying to sort a spreadsheet based on part number. These are seven-digit strings that contain a mix of numbers and letters, but the first two characters are always numbers. I want to sort on those two numbers. When I used the regular ascending/descending options, it rearranges the rows, but not in any logical order that I can detect, probably because of the letters. I probably need to be using wildcards, but I tried a few things (for example, 1*, 2*, 3*, etc.) but no luck. Anybody have any ideas?

Add a sort column where the contents of that column are set to the first two position of the part number.  If the part number is in column A, you would set up a formula such as =LEFT(A1,2).

Is it possible that the strings have leading spaces in them?

A normal sort on the data you describe would naturally sort on the leading characters, the two digits.

tjohn's suggestion would also tell you if there were leading spaces, since the value of the left function would be blank in that case.

tjohn said:
Add a sort column where the contents of that column are set to the first two position of the part number.  If the part number is in column A, you would set up a formula such as =LEFT(A1,2).

Wow, this is great! I need to spend some time exploring those functions. 

Is there a way to copy and paste the formula into the sort column for each row that causes it to pick up the current row number? Or do I need to enter that manually for each row?

drummerboy said:
tjohn's suggestion would also tell you if there were leading spaces, since the value of the left function would be blank in that case.

 Not seeing any leading spaces. This is what I get when I sort on the part numbers (now column B). I have no idea how it's calculating the sort. 

Based on your example, the program is sorting all numerical entries first followed by alpha/numeric entries.  The alpha figure in the third position in some of your entries is what is throwing the sort off.

There is something weird going on here. Under normal sort conditions, the column should sort exactly the way you want.

I think the problem is that it's considering some values as integers, and other values as strings. And I think integers always sort ahead of strings. This exactly explains your results.

When you run the sort, does it ask a question like this?

kthnry said:
Is there a way to copy and paste the formula into the sort column for each row that causes it to pick up the current row number? Or do I need to enter that manually for each row?

Never mind, I figured this out. I munged up my first pass because I typed in B1 for the first row (when it was actually B150) and so they were all off. On a new table, I put the correct row number in the formula, then when I copied and pasted the formula it incremented correctly for each row.

drummerboy said:

When you run the sort, does it ask a question like this?

No, I haven't seen a message like that. 

I tried changed the cell format for the column from General to Number and to Text but still got incorrect results. Is that what you're referring to? Is there something else I should try? Want me to send it to you so you can play with it?

FYI, the Description column, where the first word is all text, sorts just fine.

I think I found it. It's explained here, but the basic idea is to use the TEXT function in your sort column A. e.g. =TEXT(B2,"###")

That should do it.

kthnry said:

kthnry said:
Is there a way to copy and paste the formula into the sort column for each row that causes it to pick up the current row number? Or do I need to enter that manually for each row?

Never mind, I figured this out. I munged up my first pass because I typed in B1 for the first row (when it was actually B150) and so they were all off. On a new table, I put the correct row number in the formula, then when I copied and pasted the formula it incremented correctly for each row.

 In addition to the copy-paste strategy, there is also a "fill down" method to make formulas (and numbers) increment by row.

https://support.office.com/en-us/article/fill-a-formula-down-into-adjacent-cells-041edfe2-05bc-40e6-b933-ef48c3f308c6

You can find Youtube videos that show this more clearly.