ALPHA/NUMERICAL SORT

[MoKaLasco]

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?

 

[Teethless mama]

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

 

[Max]

One way
In B1: =SUBSTITUTE(A1,"H","")+0
Copy down to extent of source data in col A
Then select cols A & B, do Data>Sort by col B, ascending
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[Shane Devenshire]

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.

 

[James Silverton]

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 Data>Sort by col B, ascending
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

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

 

[Max]

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