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

[Guest]

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!!!

 

[Arvi Laanemets]

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).

 

[Guest]

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

 

[Jack Sons]

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

 

[Guest]

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!

 

[Ron Rosenfeld]

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

 

[Arvi Laanemets]

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