Need help with getting things to sort correctly in Excel

April 29, 2014 12:08 PM

I have a spreadsheet with items that I am trying to organize by part numbers made up of both letters and numbers. When I sort them, though, "HMDL32000" comes ahead of "HMDL3700", even though 32000 is a bigger number. In the warehouse, HMDL32000 is behind HMDL3700 and it seems unintuitive to have it appear first on the spreadsheet. How can I get Excel to organize first by the alphabetical portion of the part number, and then in the correct numeric order?
posted by BuddhaInABucket to Computers & Internet (3 answers total)

Here's a formula that should take care of it for you.
posted by Ruthless Bunny at 12:11 PM on April 29, 2014 [3 favorites]

The reason it is sorting that way is that 37 is bigger than 32. If it was HMDL32000
and HMDL03700, then Excel would sort it correctly. You could have two hidden columns, one that contains the alpha part and one that contains the numeric part. Then you could sort by the first column and then the second.
posted by soelo at 12:27 PM on April 29, 2014 [5 favorites]

Piggybacking on soelo's idea, this will be easier if every code starts with the same number of letters because you can grab the letter portion of the code with the left formula and then grab the numeric portion with the mid formula (just set the length in the mid formula to a value that is long enough to grab the longest numeric code you have).
posted by agog at 1:26 PM on April 29, 2014