# Thread: sort numbers and letters (excel 2000)

1. ## sort numbers and letters (excel 2000)

Hello all, I have a column in excel that i want to sort but it wont work, for example i have the digits 5, 21, 11aa, 1, 8, 4b. when i sort them i get 1, 5, 8, 21, 11aa, 4b.
but i wont that it is 1, 4b, 5, 8, 11aa, 21. is this possible?? i have looked by format cells but that won't work....

Gerwin

2. ## Re: sort numbers and letters (excel 2000)

Gerwin,

Others will probably come up with a more clever solution, but you could use array formulas to split the values into a numeric and an alphabetic part.
For instance, if your values start in A1, this formula in B1 extracts the numeric part:
=1*LEFT(A1,MATCH(TRUE,ISERROR(1*MID(A1,ROW(\$1:\$9), 1)),0)-1)
And this formula in C1 extracts the alphabetic part:
=MID(A1,MATCH(TRUE,ISERROR(1*MID(A1,ROW(\$1:\$9),1)) ,0),255)
Both must be entered as array formulas, i.e. confirm with Ctrl+Shift+Enter instead of Enter. You can fill down the formulas as far as needed, then sort on the second and third column. See the attached workbook.

Note: array formulas inspired by Bob Umlas.

3. ## Re: sort numbers and letters (excel 2000)

One additional column to convert a numeric to text should do the trick.

You can use = Text(A1,"0"), where A1 has a value with or without a following letter.

I have hijacked Hans's example to show what I mean. See attcahed.

Andrew

4. ## Re: sort numbers and letters (excel 2000)

Another method is a macro approach similar to the index sorting in <post#=347173>post 347173</post#>

Select the region and it will sort based on the initial "value part" and then the rest. The macro reads the selection (can be multiple columns if you want to sort an "datatable", then adds 2 columns to it: the initial numbers from the first range and the then the "rest". It sorts based on the number first and if equal the "rest"

Steve

5. ## Re: sort numbers and letters (excel 2000)

Thanks for all the codes........

Gerwin

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•