Sorting in Excel

[Sammi]

Please help:
I am trying to sort a list in Excel 2002.
When it sorts, like this:

10A
10A
10B
10B
10B
10C
10C
10D
11A
11B
1A
1A
1A
1A
1A

I want 1A at the top. I know that since there is a letter
in there, then it is formatted as text, I have tried
formatting every way I can think of, to no avail
Does anybody know of a way to get around this??

Please help!
Any suggestions would be appreacitate!

Sammi

 

[Frank Kabel]

Hi
one way: Use a helper column. In B1 enter the following array formula
(with CTRL+SHIFT+ENTER)
=--LEFT(A1,MAX(IF(ISNUMBER(--MID(A1,ROW(INDIRECT("1:1024")),1)),ROW(IND
IRECT("1:1024")))))
and copy this down for all rows. Now sort with this column

 

[Sammi]

Thanks, I will try that.

-----Original Message-----
Hi
one way: Use a helper column. In B1 enter the following array formula
(with CTRL+SHIFT+ENTER)
=--LEFT(A1,MAX(IF(ISNUMBER(--MID(A1,ROW(INDIRECT ("1:1024")),1)),ROW(IND
IRECT("1:1024")))))
and copy this down for all rows. Now sort with this column




--
Regards
Frank Kabel
Frankfurt, Germany



.