Sorting 1, 1A, 2, 2A, 3, 4, 4A, 4B . . .
 

Help!
I am using Excel 2000. I have a spreadsheet with multiple columns and the
column that I want to sort by contains sheet numbers. I know how to sort by
that row, the problem is that when I do, instead of sorting 1, 1A, 2, 2A, 3,
4, 4A, 4B . . . Excel sorts it as: 1, 2, 3, 4, 1A, 2A, 4A, 4B. I have tried
making the numbers all text and all numbers. Neither works. I have also
tried both options that you get when you are sorting (the screen that pops up
with two sorting options). None have worked so far!!! Please help!!!

Hi

You have to format cells as text. The problem is, that when you didn't so at
start, you have to re-enter every numeric entry in column (select cell,
press F2, press Enter). The workaround is to use the helper colum with
formula like
="" & A1
and to overwrite then the original column with values from this helper
column (you can delete the helper column after that).

--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )


"agc1234" wrote in message
...
Help!
I am using Excel 2000. I have a spreadsheet with multiple columns and the
column that I want to sort by contains sheet numbers. I know how to sort
by
that row, the problem is that when I do, instead of sorting 1, 1A, 2, 2A,
3,
4, 4A, 4B . . . Excel sorts it as: 1, 2, 3, 4, 1A, 2A, 4A, 4B. I have
tried
making the numbers all text and all numbers. Neither works. I have also
tried both options that you get when you are sorting (the screen that pops
up
with two sorting options). None have worked so far!!! Please help!!!

I tried this and it is still not sorting them correctly! What is a helper
column?

"Arvi Laanemets" wrote:

Hi

You have to format cells as text. The problem is, that when you didn't so at
start, you have to re-enter every numeric entry in column (select cell,
press F2, press Enter). The workaround is to use the helper colum with
formula like
="" & A1
and to overwrite then the original column with values from this helper
column (you can delete the helper column after that).

--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )


"agc1234" wrote in message
...
Help!
I am using Excel 2000. I have a spreadsheet with multiple columns and the
column that I want to sort by contains sheet numbers. I know how to sort
by
that row, the problem is that when I do, instead of sorting 1, 1A, 2, 2A,
3,
4, 4A, 4B . . . Excel sorts it as: 1, 2, 3, 4, 1A, 2A, 4A, 4B. I have
tried
making the numbers all text and all numbers. Neither works. I have also
tried both options that you get when you are sorting (the screen that pops
up
with two sorting options). None have worked so far!!! Please help!!!

agc1234,

Also look at post "sorting alphanumeric" by Chronos 17-05-2005 09.01 and
especially Héctor Miguel's asnwer.

Jack Sons
The Netherlands

"agc1234" schreef in bericht
...
Help!
I am using Excel 2000. I have a spreadsheet with multiple columns and the
column that I want to sort by contains sheet numbers. I know how to sort
by
that row, the problem is that when I do, instead of sorting 1, 1A, 2, 2A,
3,
4, 4A, 4B . . . Excel sorts it as: 1, 2, 3, 4, 1A, 2A, 4A, 4B. I have
tried
making the numbers all text and all numbers. Neither works. I have also
tried both options that you get when you are sorting (the screen that pops
up
with two sorting options). None have worked so far!!! Please help!!!

Thanks Jack. After looking at what you recommended I did a little more
research and found an easier way:
If what you want to sort by is in column a, put
=IF(ISERROR(A1*1),LEFT(A1,LEN(A1)-1),A1&"" IN column b and drag it down to
fill all cells that are filled in A. Then sort by row B then A.
Thanks for all your help!

"Jack Sons" wrote:

agc1234,

Also look at post "sorting alphanumeric" by Chronos 17-05-2005 09.01 and
especially Héctor Miguel's asnwer.

Jack Sons
The Netherlands

"agc1234" schreef in bericht
...
Help!
I am using Excel 2000. I have a spreadsheet with multiple columns and the
column that I want to sort by contains sheet numbers. I know how to sort
by
that row, the problem is that when I do, instead of sorting 1, 1A, 2, 2A,
3,
4, 4A, 4B . . . Excel sorts it as: 1, 2, 3, 4, 1A, 2A, 4A, 4B. I have
tried
making the numbers all text and all numbers. Neither works. I have also
tried both options that you get when you are sorting (the screen that pops
up
with two sorting options). None have worked so far!!! Please help!!!

On Thu, 26 May 2005 06:20:02 -0700, "agc1234"
wrote:

Help!
I am using Excel 2000. I have a spreadsheet with multiple columns and the
column that I want to sort by contains sheet numbers. I know how to sort by
that row, the problem is that when I do, instead of sorting 1, 1A, 2, 2A, 3,
4, 4A, 4B . . . Excel sorts it as: 1, 2, 3, 4, 1A, 2A, 4A, 4B. I have tried
making the numbers all text and all numbers. Neither works. I have also
tried both options that you get when you are sorting (the screen that pops up
with two sorting options). None have worked so far!!! Please help!!!

Assumption: Your format is a single digit followed by text.

A1:An := your data

In B1 place the formula: =LEFT(A1,1) and copy/drag it down to Bn.

Select both columns, then:
Data/Sort
Sort by: Column B
then by: Column A

then you can hide or delete column B.

If the assumptions are invalid, you may need to change your formula a bit


--ron

Simply I meant an additional temporary column with it - as best placed
outside (to right) of your table. It is used to store some temporary data
when you modify the table, and usualy is deleted afterwards.


Arvi Laanemets


"agc1234" wrote in message
...
I tried this and it is still not sorting them correctly! What is a helper
column?

"Arvi Laanemets" wrote:

Hi

You have to format cells as text. The problem is, that when you didn't
so at
start, you have to re-enter every numeric entry in column (select cell,
press F2, press Enter). The workaround is to use the helper colum with
formula like
="" & A1
and to overwrite then the original column with values from this helper
column (you can delete the helper column after that).

--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )


"agc1234" wrote in message
...
Help!
I am using Excel 2000. I have a spreadsheet with multiple columns and
the
column that I want to sort by contains sheet numbers. I know how to
sort
by
that row, the problem is that when I do, instead of sorting 1, 1A, 2,
2A,
3,
4, 4A, 4B . . . Excel sorts it as: 1, 2, 3, 4, 1A, 2A, 4A, 4B. I
have
tried
making the numbers all text and all numbers. Neither works. I have
also
tried both options that you get when you are sorting (the screen that
pops
up
with two sorting options). None have worked so far!!! Please help!!!