How do we sort Alpha Numerical in the same manner as we count?
EX: H1, H2, H9, H10, H100, H500, H1004, etc...
Excell will sort as H1, H10, H100, H1004, H2, H500, H9
Our project requires the different sorting as stated.
Any Help?

Assume your data in A1:A7

create a helper column
In B1: =LEFT(A1)&TEXT(MID(A1,2,99),"0000")
copy down to B7

Select both column and sort by column B. When you done, delete the helper
column



"MoKaLasco" wrote:

How do we sort Alpha Numerical in the same manner as we count?
EX: H1, H2, H9, H10, H100, H500, H1004, etc...
Excell will sort as H1, H10, H100, H1004, H2, H500, H9
Our project requires the different sorting as stated.
Any Help?

One way
In B1: =SUBSTITUTE(A1,"H","")+0
Copy down to extent of source data in col A
Then select cols A & B, do DataSort by col B, ascending
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"MoKaLasco" wrote:
How do we sort Alpha Numerical in the same manner as we count?
EX: H1, H2, H9, H10, H100, H500, H1004, etc...
Excell will sort as H1, H10, H100, H1004, H2, H500, H9
Our project requires the different sorting as stated.
Any Help?

Hi,

Here are two ways, assuming the data is in column A starting on row 2

1. In B2 enter
=--MID(A2,2,9)
Copy this down and sort your data based on this column
2. Select the data in column A and choose Data, Text to columns, Fixed
width, Next, Next, select the first column in the preview area and choose Do
not import (Skip) and make B2 the destination cell. Use this column to sort
your data.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"MoKaLasco" wrote:

How do we sort Alpha Numerical in the same manner as we count?
EX: H1, H2, H9, H10, H100, H500, H1004, etc...
Excell will sort as H1, H10, H100, H1004, H2, H500, H9
Our project requires the different sorting as stated.
Any Help?

Max wrote on Thu, 2 Jul 2009 16:18:01 -0700:

One way
In B1: =SUBSTITUTE(A1,"H","")+0
Copy down to extent of source data in col A
Then select cols A & B, do DataSort by col B, ascending
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"MoKaLasco" wrote:
How do we sort Alpha Numerical in the same manner as we
count? EX: H1, H2, H9, H10, H100, H500, H1004, etc...
Excell will sort as H1, H10, H100, H1004, H2, H500, H9 Our
project requires the different sorting as stated. Any Help?

It seems that translation via a helper column is necessary. I wonder if
the Romans ever sorted numerically and how would you do it with Excel?
--

James Silverton
Potomac, Maryland

Email, with obvious alterations: not.jim.silverton.at.verizon.not

Maybe this googled thread contains some answers:
http://tinyurl.com/neyp8n

Or we could ask the roman-tics amongst us, I'd think, no?
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"James Silverton" wrote:
It seems that translation via a helper column is necessary. I wonder if
the Romans ever sorted numerically and how would you do it with Excel?
--

James Silverton
Potomac, Maryland

Email, with obvious alterations: not.jim.silverton.at.verizon.not