Alpha sorting (with a twist...?)

[Jason F.]

Hi, I've followed MS instructions for alpha sorting (e.g. helper column w/
=text(e5,"###") ) to no help. I think that works only when the value in the
column begins with a number. My data can begin w/ a number or alpha character.

Here's an example of my data:
1
2
3
4
A1
A10
5
6
7
8
A11
A12
A2
A3
A4
A5
9
10
11
12
A6
A7
A8
A9
B1
B10
B11
B12
B2
B3
B4
B5
B6
B7
B8
B9


And here's how I want it sorted...

1
2
3
4
5
6
7
8
9
10
11
12
A1
A2
A3
A4
A5
A6
A7
A8
A9
A10
A11
A12
B1
B2
B3
B4
B5
B6
B7
B8
B9
B10
B11
B12

But it keeps coming out like this:
1
2
3
4
5
6
7
8
9
10
11
12
A1
A10
A11
A12
A2
A3
A4
A5
A6
A7
A8
A9
B1
B10
B11
B12
B2
B3
B4
B5
B6
B7
B8
B9

This is the second column of a three-column sort.

Suggestions?

Thanks,

 

[Bernard Liengme]

How do you want it to come out on sorting?

Do you want : A1, A2...... 1,2,3.....
Let's assume the first item is in cell A1
In B1 enter =IF(ISNUMBER(A1),"ZZZ"&A1,A1) and copy down the column
Select all the B values; use Copy ; with them still selected use Paste
Special with Values specified: Now B has values not formulas and you can
sort using it

best wishes

 

[Jason F.]

your suggestion resulted in this:
ZZZ1
ZZZ10
ZZZ11
ZZZ12
ZZZ13
ZZZ14
ZZZ15
ZZZ16
ZZZ17
ZZZ18
ZZZ19
ZZZ2
ZZZ20
ZZZ21
......
ZZZ8
ZZZ9
A1
A10
A11
A12
A2
A3
A4
A5
A6
A7
A8
A9
B1
B10
B11
B12
B2
B3
B4
.......

The problem is that the A10, A11 and A12 are being sorted after A1, instead
of after A9.

BTW, this is the result if the column is formatted as text or general.

thanks,

 

[MyVeryOwnSelf]

... My data can begin w/ a number or alpha character.

Here's an example of my data:
1
2
3
4
A1
A10
5
6
7
8
A11
A12
A2
A3
A4
A5
9
10
11
12
A6
A7
A8
A9
B1
B10
B11
B12
B2
B3
B4
B5
B6
B7
B8
B9


And here's how I want it sorted...

1
2
3
4
5
6
7
8
9
10
11
12
A1
A2
A3
A4
A5
A6
A7
A8
A9
A10
A11
A12
B1
B2
B3
B4
B5
B6
B7
B8
B9
B10
B11
B12

The following works for me with Excel 2003.

In B1, put this formula and copy it down as far as needed:
=IF(ISNUMBER(A1),TEXT(A1,"0000"),
LEFT(A1,1)&TEXT(MID(A1,2,99),"0000"))

Select columns A and B together and sort by column B, ascending. Then,
column A should be in the desired order.

Column B can be hidden when not in use to avoid clutter.

If the list can change in length, you may not want blank entries to sort
at the top. To avoid that, wrap the above formula like this:
=IF(ISBLANK(A1),"ZZZZZ", <above formula>)
and copy down longer than the longest possible list.

Modify to suit.