View Single Post
  #5   Report Post  
Old February 26th 08, 03:28 AM posted to microsoft.public.excel.worksheet.functions
MartinW[_2_] MartinW[_2_] is offline
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2008
Posts: 168
Default Sorting Numbers with letter suffixes

Hi Terry,

Maybe this.

First up sort ascending on your data in col A.
This will leave your numbers sorted in the top of the
column and your text sorted at the bottom of the column

Copy the numbers across to column B. Then with the text
values, for this example let's say that the first text entry is in A9.
Put this in B9 and drag down to the end of your data.
=LEFT(A9,1)*1
You may need something different depending
on what your actual data is. What you want is to extract
the numbers without the letters, and to convert the
text numbers to real numbers (that's the *1 bit)

Then in col. C we pinch a bit of Max's formula.
Put this in C1 and drag down to the end of your data
=B1+ROW()/10^10

Now select all three columns A,B and C
and sort on col. C ascending.
Hopefully col A should be sorted the way you want.

HTH
Martin


"Terry Bennett" wrote in message
...
Thanks Max.

I see your logic but this sorts all digits begining with a '1' first ...
hence 112 comes before 2. In the case of 2, 2A, etc it seems to sort
these randomly within the digits begining with 2s.


"Max" wrote in message
...
One way which might suffice, presuming data as posted is representative

Assuming data in A1 down
Put in B1, copy down:
=IF(A1="","",IF(ISNUMBER(A1),A1,LEFT(A1)+CODE(LEFT (A1))/10^10))
Then select both cols A & B, sort by col B, ascending
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Terry Bennett" wrote in message
...
Is there a way I can sort numbers such that if I have a letter suffix
(ie; 2A, 3B, etc), they are sorted after the original number, ie; 2A
between 2 and 3 then 3B between 3 and 4?

Thanks.