Home 
Search 
Today's Posts 
#1




Sorting Numbers with letter suffixes
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. 
#2




Sorting Numbers with letter suffixes
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. 
#3




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. 
#4




Sorting Numbers with letter suffixes
well, the caveat was:
.. presuming data as posted is representative Since you have now extended the scope, hang around awhile for better solutions from others  Max Singapore http://savefile.com/projects/236895 xdemechanik  "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. 
#5




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. 
#6




Sorting Numbers with letter suffixes
Thanks for the suggestion Martin but it still sorts numbers like 12B ahead
of single digits like 2 and 3. Not a problem  I can get around it manually. I do appreciate your suggestion  many thanks. "MartinW" wrote in message ... 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. 
#7




Sorting Numbers with letter suffixes
Hello,
If I understand the problem correctly Max was almost the =IF(A1="","",IF(ISNUMBER(A1),A1,LEFT(A1)+CODE(LEFT(A1))/10^10)) If there can be more than one letter suffix I suggest to use RegExpReplace to separate numerical and text parts... Regards, Bernd 
#8




Sorting Numbers with letter suffixes
Well no it doesn't Terry,
If you change the extraction formula to this, =LEFT(A9,LEN(A9)1)*1 It will sort this column 1 3B 7C 12B 2 5 7A 6C 7B 12A 4 5A 4B 3A 6B 7 3C 124A 7D 124B Like this 1 2 3A 3B 3C 4 4B 5 5A 6B 6C 7 7A 7B 7C 7D 12A 12B 124A 124B Is that not what you are trying to do? Regards Martin "Terry Bennett" wrote in message ... Thanks for the suggestion Martin but it still sorts numbers like 12B ahead of single digits like 2 and 3. Not a problem  I can get around it manually. I do appreciate your suggestion  many thanks. "MartinW" wrote in message ... 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. 
#9




Sorting Numbers with letter suffixes
Thanks again guys for the suggestions  much appreciated.
Terry "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. 
#10




Sorting Numbers with letter suffixes
On Wednesday, February 27, 2008 at 4:42:44 AM UTC5, Terry Bennett wrote:
Thanks again guys for the suggestions  much appreciated. Terry "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. Change the data type to Plain text instead of Auto. Then when you sort it does it correctly. 
Reply 

Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Sorting a column from the last letter or 2 letters of a part numbe  Excel Discussion (Misc queries)  
Extracting names and suffixes  Excel Discussion (Misc queries)  
How can you get Excel to sort numbers with alpha suffixes? 1234A  Excel Discussion (Misc queries)  
How do I use the numbers on the letter keys?  Excel Discussion (Misc queries)  
Equations with numbers and letter values  Excel Discussion (Misc queries) 