sort numbers and numbers+text together in Excel

I am trying to sort an Excel sheet based on a column that has a mix of:

3 digit numbers (such as 404)
3 digit numbers with non-numeric characters appended (such as 802b)
non-numeric text (such as none or ?)

Excel sorts the plain numbers first, then the rest are sorted together, with the numbers+text in between punctuation marks and alpha characters.

I want the numbers and the numbers+text interleaved. But applying Format > Text to the column does nothing. How do I force it to sort numbers as text?

Foo Bar

Posted 2016-10-03T21:01:40.503

Reputation: 390

It seems that you don't understand alphabetical order. Indeed in alphabetical order, as in Secretarial folder filing system, numbers come first, then alpha numeric, and lastly alpha only. So, yes, Excel is doing this properly. – ejbytes – 2016-10-03T23:44:52.803

NOTE: However, if you treat Number+Text as First+Last name you will get what you desire. It follows: 2, 2 a, 3, 3 a. You will need to add a space between first group(numberic) and second group(alpha). – ejbytes – 2016-10-03T23:52:05.867

@ejbytes: that's the first I've ever heard of a particular "secretarial folder filing system" for alphabetical. Is there a way of sorting based on the CODE() result character-by-character without sorting on a derived column created by concatenating a bunch of 0-padded 3-digit ASCII values as text? I've got some hexadecimal numbers where 71AA should come before 7259, but due to the "Secretarial folder filing system" this does not occur. (yes, I know in my circumstance I could use HEX2DEC() and then use a numeric sort, but that still requires an additional column & wouldn't work for non-hex) – mpag – 2018-04-03T19:46:30.197

@mpag I'll not come back to this, but column as text 71AA will indeed sort above 7259. If you are looking for some other explanation I think this is the wrong place to look for it. Either create your own question if you are seeking solutions or move on to something more constructive. Anyhow, when would someone ever sort a hexadecimal filing system? One wouldn't. The question asked was nothing more than a 3-character sort problem. Answered. It worked out. Nothing more to see, these aren't the droids you're looking for. – ejbytes – 2018-04-04T08:39:33.150

@ejbytes "when would someone ever sort a hexadecimal filing system?" I dunno. If you had my job, you would have to sort hex numbers. Or rather, you wouldn't have to, but it would sure make the display of data in tables prettier. MS Docs at https://support.office.com/en-us/article/Sort-data-in-a-range-or-table-62d0b95d-2a90-4610-a6ae-2e545c4a4654 seem to suggest that formatting the cells as text is sufficient to have them sort as pure ASCII text, but 71AA was sorting below 7259. In seeking out an explanation, I came across this thread. Have a good day. Sorry for dragging you out of exile.

– mpag – 2018-04-04T18:16:33.627

Answers

Alphabetical order: Numeric, Alphanumeric, Alpha.

1, 2, 3, 4, 5, 10, 2a, 3Regy, 4a, 6Alpha, Alpha,

If you prefer 2a to follow 2, then you need to add a space. Adding a space signifies a second grouping. Hyphenated names (words) are last.

1, 10, 2, 2 a, 2-a, 3, 3Regy, 4, 4a, 5, 6Alpha, Alpha

Further: 5 Alpha, 5Alpha, 5-Alpha, 6 Alpha, 6Alpha, Alpha, Alpha 6, Alpha5, Alpha6, Alpha-6,

Or use a Zero for place holder: 01, 02, 02a, 10

Or keep the length uniform: 001, 002, 02a, 030

ejbytes

Posted 2016-10-03T21:01:40.503

Reputation: 1 819

for uniform length why would 02a come before 010? Is that an error in your answer? Or am I not understanding something? Also, uniform length does not result in it being treated as all-text, as uniform-length hexadecimal values do not sort "properly". – mpag – 2018-04-03T19:51:16.107

@mpag Funny, I don't even remember answering this question. It's a couple years old. I don't know if you're actually seeking solutions or just looking for points. I don't really participate much here any longer because of too many 1-point reputations asking a question and never returning. However a brownie point for you, it seems I "fat fingered" the last grouping and insert a 1 instead of a three. Thanks? – ejbytes – 2018-04-04T08:24:41.070

You could force the numbers to text using the formula =text("A1","@"). You could then copy/paste values and after that they can be sorted as text.

Just note that since it is text sorting it wouldn't sort 1,2,11,3,22... correct you would get 1,11,2,22,3....

gtwebb

Posted 2016-10-03T21:01:40.503

Reputation: 2 852