Excel – Alphanumeric sorting in Excel

microsoft excelsorting

In Excel, is it possible to sort alphanumerics from a-1, a-2, a-3…a-123 instead of a-1, a-10, a-100, a-11?

Sorting via oldest to newest or via A-Z, definitely won't give me the result I want. I've tried formatting the cells as number, but it didn't help.

I'm stuck.

Best Answer

The Result

User @fixer1234 is right, you probably want to use string functions. Here is one way to do that.

Step 1

[Updated]

In the "Numbers" column, highlight the range, then split the text at the hyphen: do...

Data > Text to Columns > Delimited > Next > Other: - > Finish

Notice you need a hyphen (-) in the Other: textbox. And make sure that the adjacent column (to the right) is empty before you do this, so that you don't overwrite important data.

You could also use this function to do extract the number from column A:

=RIGHT(A2,LEN(A2)-SEARCH("-",A2))

Step 2

Now, what we're going to do in the next step, as you can tell from the screenshot above, is append zeroes to the beginning of each of the numbers which have fewer digits than the largest number. This will allow you to sort these numbers in the way you desire.

But first, we need to do a bit of fact-finding. If you can tell easily what the largest number is, then just count the digits in that largest number — this will be the number of zeroes you want to use in our next function. There is another way to determine the longest number, without having to count the digits of the largest number manually.

[UPDATE] You can use the following function (though so far we don't quite know why) to determine the longest number:

=MAX(INDEX(LEN(C2:C14),,1))

Alternatively, you could simply type the following formula into a cell (you see this in the image above as the cell highlighted orange), but instead of simply hitting your ENTER key to set the cell, hit the hotkey CTRL-SHIFT-ENTER. This will change the nature of the function, turning it into an array formula, without having to toy with functions such as INDEX().

=MAX(LEN(C2:C14))

(Make sure that the range is accurate for the specific spreadsheet you are using.)

After you hit CTRL-SHIFT-ENTER, the content of the cell will change to this, but typing this manually will do nothing:

{=MAX(LEN(C2:C14))}

However you want to do it is fine. Just determine how many digits make up the largest number in your list: "1" of course has 1 digit, "10" has 2 digits, "100" has 3 digits, and so on.

Step 3

Finally, in the "Expand" column, this function will convert the numbers from the "Numbers" column into text with the number of preceding zeroes you determined you should use in Step 2.

=TEXT(C2,"000")

Make certain that you put quotations marks around the zeroes.

If the largest number has 8 digits, then your function will look like this:

=TEXT(C2,"00000000")
Related Question