Sorting Numbers that have Text

[Ginger Kohler]

I need to sort like this

1
5
90
90a
90b
100
170a
170b

instead of
1
5
90
100
90a
90b
170a
170b

I found a message from 2006 that said to do this:

"I'd convert the column to Text. (Data->Text to Columns...)

Then, sort, making sure to check "Sort number and numbers stored as
text separately."

I did what was suggested and got this:

1
100
170a
170b
5
90
90a
90b

So, how do I get it to sort the way I want? (the numbers go up into the
1000's)

 

[Ron Rosenfeld]

I need to sort like this

1
5
90
90a
90b
100
170a
170b

instead of
1
5
90
100
90a
90b
170a
170b

I found a message from 2006 that said to do this:

"I'd convert the column to Text. (Data->Text to Columns...)

Then, sort, making sure to check "Sort number and numbers stored as
text separately."

I did what was suggested and got this:

1
100
170a
170b
5
90
90a
90b

So, how do I get it to sort the way I want? (the numbers go up into the
1000's)

You need to set up TWO helper columns
The first column has just the numeric values
The second column has just the alpha values

Then you select all three columns
Sort by column 2
then by column 3

Column 1 will wind up sorted as you describe.

So, with your data A1:A8

B1: =LOOKUP(1E+307,--LEFT(A1,ROW(INDIRECT("1:10"))))
C1: =SUBSTITUTE(A1,B1,"")

Select B1:C1 and fill down to B8:C8

Select A1:C8
Data/Sort
First by column B
then by Column C

Voila!
--ron

 

[Gary''s Student]

Say starting in A2 we have:

7
4
1a
5
7a
4b
3c
4a
4b
9b
4c
10c
3c
3c
5a
4b
2a
7
5b
10c
9a
10
4a
5a
10b
3b
4a
10
6c
6a
1b
5a
2a
9a
7b
3
1b
3c
10
In B2 enter:
=IF(ISERROR(--RIGHT(A2,1)),--(LEFT(A2,LEN(A2)-1)),--A2) and copy down
In C3 enter:
=IF(ISERROR(--RIGHT(A2,1)),RIGHT(A2,1),"") and copy down
We see:

7 7
4 4
1a 1 a
5 5
7a 7 a
4b 4 b
3c 3 c
4a 4 a
4b 4 b
9b 9 b
4c 4 c
10c 10 c
3c 3 c
3c 3 c
5a 5 a
4b 4 b
2a 2 a
7 7
5b 5 b
10c 10 c
9a 9 a
10 10
4a 4 a
5a 5 a
10b 10 b
3b 3 b
4a 4 a
10 10
6c 6 c
6a 6 a
1b 1 b
5a 5 a
2a 2 a
9a 9 a
7b 7 b
3 3
1b 1 b
3c 3 c
10 10

We have basically de-coupled the numbers and letters. First sort by column
B and then by column C :


1a 1 a
1b 1 b
1b 1 b
2a 2 a
2a 2 a
3 3
3b 3 b
3c 3 c
3c 3 c
3c 3 c
3c 3 c
4 4
4a 4 a
4a 4 a
4a 4 a
4b 4 b
4b 4 b
4b 4 b
4c 4 c
5 5
5a 5 a
5a 5 a
5a 5 a
5b 5 b
6a 6 a
6c 6 c
7 7
7 7
7a 7 a
7b 7 b
9a 9 a
9a 9 a
9b 9 b
10 10
10 10
10 10
10b 10 b
10c 10 c
10c 10 c