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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!!!
 
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).
 
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
 
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!
 
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!!!
Click to expand...

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
 
Back
Top