simple sort

M

maacmaac

I have following list in Excel:
54
6A
34
5T
4X
67

When a do Data|Sort|Ascending the list looks like this:
34
54
67
4X
5T
6A

I need the list to sort like this:
34
4X
54
5T
67
6A

I have tried a half dozen ways to sort including formatting the cells
but nothing is working. Any help would be great. Thanks
 
A

Alan

With the data in column A,try in column B:
=LEFT(A1,1)*1
The '*1' will return the first number in the string as a true number as
opposed to text that the LEFT function would normally return, then sort the
two columns by column B,
Regards,
Alan.
 
M

maacmaac

Alan,

The formula did not quite work; I should have used real numbers. Th
actual numbers are as follows (I have an actual list of about 500 tha
need to be sorted)

45624X456
692000111
69200R345
23563Y403
893000222
89300G234

Thank
 
D

Dave Peterson

One way to pick out the leading numbers is to use this kind of array formula:

=--LEFT(A4,MIN(MATCH(TRUE,
ISERROR(-MID(A4&"x",ROW(INDIRECT("1:"&LEN(A4)+1)),1)),0))-1)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

If you need to extract the remaining stuff, you can use this formula:

=MID(A1,LEN(B1)+1,255)

I put the data in A1.
That array formula in B1.
and that final formula in C1.

Then sort all the columns by B and C.
 
Top