|  | Sort numbers alphabetically | | |
| | | Pungh0Li0 |  |
|  Posted: Tue Aug 26, 2008 9:19 pm Post subject: Sort numbers alphabetically |  |
Let's say I have a list:
1
2
5
20
35
I want it alphabetical, ie:
1
2
20
35
5
Even if I make them text, they still sort numerically.
Is there some trick?
Thanks, Joe |
| |
| | | Gord Dibben | |
| | Posted: Tue Aug 26, 2008 9:19 pm Post subject: Re: Sort numbers alphabetically | |
One method................
In B1 enter =LEFT(A1)
Copy down.
Copy and paste special>values.
Select column A and B and sort on column B
Gord Dibben MS Excel MVP
On Tue, 26 Aug 2008 14:19:10 -0700 (PDT), Pungh0Li0 <joe.dellwo@gmail.com>
wrote:
| Quote: | Let's say I have a list:
1
2
5
20
35
I want it alphabetical, ie:
1
2
20
35
5
Even if I make them text, they still sort numerically.
Is there some trick?
Thanks, Joe |
|
| |
| | | Pungh0Li0 | |
| | Posted: Wed Aug 27, 2008 12:21 am Post subject: Re: Sort numbers alphabetically | |
Well, maybe I made my example too simple.
More realistic is a long list such as:
1219760
5001057
5002280
17031590
17031591
170039110
35S0409
7631-0001-0002
etc
I want to sort alphabetically, as if this were all text.
Some may even include letters and dashes.
Thanks, Joe
On Aug 26, 2:53 pm, Gord Dibben <gorddibbATshawDOTca> wrote:
| Quote: | One method................
In B1 enter =LEFT(A1)
Copy down.
Copy and paste special>values.
Select column A and B and sort on column B
Gord Dibben MS Excel MVP
On Tue, 26 Aug 2008 14:19:10 -0700 (PDT), Pungh0Li0 <joe.del...@gmail.com
wrote:
Let's say I have a list:
1
2
5
20
35
I want it alphabetical, ie:
1
2
20
35
5
Even if I make them text, they still sort numerically.
Is there some trick?
Thanks, Joe
|
|
| |
| | | Ken Johnson | |
| | Posted: Wed Aug 27, 2008 2:39 pm Post subject: Re: Sort numbers alphabetically | |
On Aug 27, 10:21 am, Pungh0Li0 <joe.del...@gmail.com> wrote:
| Quote: | Well, maybe I made my example too simple.
More realistic is a long list such as:
1219760
5001057
5002280
17031590
17031591
170039110
35S0409
7631-0001-0002
etc
I want to sort alphabetically, as if this were all text.
Some may even include letters and dashes.
Thanks, Joe
On Aug 26, 2:53 pm, Gord Dibben <gorddibbATshawDOTca> wrote:
One method................
In B1 enter =LEFT(A1)
Copy down.
Copy and paste special>values.
Select column A and B and sort on column B
Gord Dibben MS Excel MVP
On Tue, 26 Aug 2008 14:19:10 -0700 (PDT), Pungh0Li0 <joe.del...@gmail.com
wrote:
Let's say I have a list:
1
2
5
20
35
I want it alphabetical, ie:
1
2
20
35
5
Even if I make them text, they still sort numerically.
Is there some trick?
Thanks, Joe
|
Similar to Gord's except use ="A"&A1 in B1.
Nothing special about "A". Prefacing with any character seems to work.
Ken Johnson |
| |
| | | Pungh0Li0 | |
| | Posted: Wed Aug 27, 2008 3:40 pm Post subject: Re: Sort numbers alphabetically | |
| |  | |
On Aug 27, 7:39 am, Ken Johnson <KenCJohn...@gmail.com> wrote:
| Quote: | On Aug 27, 10:21 am, Pungh0Li0 <joe.del...@gmail.com> wrote:
Well, maybe I made my example too simple.
More realistic is a long list such as:
1219760
5001057
5002280
17031590
17031591
170039110
35S0409
7631-0001-0002
etc
I want to sort alphabetically, as if this were all text.
Some may even include letters and dashes.
Thanks, Joe
On Aug 26, 2:53 pm, Gord Dibben <gorddibbATshawDOTca> wrote:
One method................
In B1 enter =LEFT(A1)
Copy down.
Copy and paste special>values.
Select column A and B and sort on column B
Gord Dibben MS Excel MVP
On Tue, 26 Aug 2008 14:19:10 -0700 (PDT), Pungh0Li0 <joe.del...@gmail..com
wrote:
Let's say I have a list:
1
2
5
20
35
I want it alphabetical, ie:
1
2
20
35
5
Even if I make them text, they still sort numerically.
Is there some trick?
Thanks, Joe
Similar to Gord's except use ="A"&A1 in B1.
Nothing special about "A". Prefacing with any character seems to work.
Ken Johnson
|
Yep, that works!
Strange that it doesn't give you the option to sort alphabetically
when you format the cells as Text.
Thanks a lot, Joe |
| |
|
|
