View Single Post
  #3   Report Post  
Old February 26th 08, 12: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
"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?