Sort letters before numbers

**futamura** · 08-02-2011, 04:35 PM

How can I sort a list of text lexographically (like in a dictionary), with exception that letters (A, B, C, ..., Z) come before numbers (0, 1, ..., 9). So "AAAA" would come before "AAA1A" because "A" comes before "1" in the fourth position.

For example, sorting the following list:
AAAB
99
AAA1A
346AA
ZAAA
AAAA
5689
4AAA

should give me:
AAAA
AAAB
AAA1A
ZAAA
346AA
4AAA
5689
99

Thanks.

**sailepaty** · 08-02-2011, 05:54 PM

Add an auxiliary column with below formula. Now sort should be auxiliary column then original column.

=CODE(A2)<58

Regards

**futamura** · 08-02-2011, 06:37 PM

Add an auxiliary column with below formula. Now sort should be auxiliary column then original column.

=CODE(A2)<58

That doesn't work. That doesn't lexographically order the letters and numbers. I want:
A < B < C < ... < Z < 0 < 1 < ... < 9.

So, sorting:
AAAB
99
AAA1A
346AA
ZAAA
AAAA
5689
4AAA

should give me:
AAAA
AAAB
AAA1A
ZAAA
346AA
4AAA
5689
99

**sailepaty** · 08-03-2011, 09:48 AM

Maybe this,

=(CODE(A2)<58)&LEFT(A2)

Regards

**futamura** · 08-03-2011, 11:12 AM

Maybe this,

=(CODE(A2)<58)&LEFT(A2)

This only looks at the first character. I need a lexographic ordering (like a dictionary), but where letters come before numbers. So, AAAA needs to come before AAAB which needs to come before AAA1.

It seems like this should be easy to do, through custom lists or something, but I haven't been able to figure it out. Do you (or anyone else) have any ideas?

**sailepaty** · 08-03-2011, 11:19 AM

Ok, this should do the work.

=(CODE(A2)<58)&A2&REPT(0,50-LEN(A2))

Regards

**futamura** · 08-03-2011, 11:49 AM

Ok, this should do the work.

=(CODE(A2)<58)&A2&REPT(0,50-LEN(A2))

I appreciate your help, but this does not work either. When you use the above to sort:
AAAB
99
AAA1A
346AA
ZAAA
AAAA
5689
4AAA

you should get:
AAAA
AAAB
AAA1A
ZAAA
346AA
4AAA
5689
99

Is that what you get? (It didn't work for me)

**sailepaty** · 08-03-2011, 12:01 PM

This is what I got.

AAA1A
AAAA
AAAB
ZAAA
346AA
4AAA
5689
99

**futamura** · 08-03-2011, 01:33 PM

This is what I got.

AAA1A
AAAA
AAAB
ZAAA
346AA
4AAA
5689
99

This is not what I want. "1" comes after letters, so "AAA1A" must come after "AAAB" (The first 3 positions are the same, but in the fourth position, "1" comes after "B"). Basically, order like a dictionary would if the alphabet were: A, B, C, ..., Z, 0, 1, 2, ..., 9.

How do I do this? By default, Excel assumes the alphabet is "0, 1, ..., 9, A, B, ..., Z" (i.e., the numbers come before the letters).