2

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?

| improve this question | | | | |
  • 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 Oct 3 '16 at 23:44
  • 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 Oct 3 '16 at 23:52
  • @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 Apr 3 '18 at 19:46
  • @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 Apr 4 '18 at 8:39
  • @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 support.office.com/en-us/article/… 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 Apr 4 '18 at 18:16
1

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

| improve this answer | | | | |
  • 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 Apr 3 '18 at 19:51
  • @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 Apr 4 '18 at 8:24
2

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....

| improve this answer | | | | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.