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.


--
maacmaac
------------------------------------------------------------------------
maacmaac's Profile: http://www.excelforum.com/member.php...fo&userid=2959
View this thread: http://www.excelforum.com/showthread...hreadid=506446

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.
"maacmaac" wrote in
message ...

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.


--
maacmaac
------------------------------------------------------------------------
maacmaac's Profile:
http://www.excelforum.com/member.php...fo&userid=2959
View this thread: http://www.excelforum.com/showthread...hreadid=506446

Alan,

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

45624X456
692000111
69200R345
23563Y403
893000222
89300G234

Thanks


--
maacmaac
------------------------------------------------------------------------
maacmaac's Profile: http://www.excelforum.com/member.php...fo&userid=2959
View this thread: http://www.excelforum.com/showthread...hreadid=506446

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.

maacmaac wrote:

Alan,

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

45624X456
692000111
69200R345
23563Y403
893000222
89300G234

Thanks

--
maacmaac
------------------------------------------------------------------------
maacmaac's Profile: http://www.excelforum.com/member.php...fo&userid=2959
View this thread: http://www.excelforum.com/showthread...hreadid=506446

--

Dave Peterson