View Single Post
  #3   Report Post  
Old February 26th 08, 01:38 AM posted to microsoft.public.excel.worksheet.functions
Terry Bennett Terry Bennett is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 67
Default Sorting Numbers with letter suffixes

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.