Home 
Search 
Today's Posts 
#1




Sorting when some numbers have a text suffix
I have about 1350 5 and 6 digit numbers starting in column B2 that I would
like to be sorted. About 200 of the numbers have "A" at the end along with a coulpe that have "B", "C", "D", and "E" . All the numbers with a letter are six digits long if that helps i.e. 123456A. Now when I sort the numbers sort fine but all the numbers with a letter attached are at the bottom of the list, presumably as text. How can get the numbers and text"numbers" to sort together? 
#2




One way would be to break off the letters using data Texttocolumns
Fixed, and put them in the next column, then sort using your regular column as the first sortkey and this new column as your second sortkey............then you could Concatenate them back together if you wish...... Vaya con Dios, Chuck, CABGx3 "confused on the tundra" wrote in message ... I have about 1350 5 and 6 digit numbers starting in column B2 that I would like to be sorted. About 200 of the numbers have "A" at the end along with a coulpe that have "B", "C", "D", and "E" . All the numbers with a letter are six digits long if that helps i.e. 123456A. Now when I sort the numbers sort fine but all the numbers with a letter attached are at the bottom of the list, presumably as text. How can get the numbers and text"numbers" to sort together? 
#3




Try the following...
C2, copied down: =IF(D2="",B2,SUBSTITUTE(B2,D2,"")) D2, copied down: =IF(ISNUMBER(B2),"",RIGHT(B2)) Then select Columns B, C and D, and sort by Column C and D... Data Sort Sort by "Column C" Then by "Column D" Hope this helps! In article , "CLR" wrote: One way would be to break off the letters using data Texttocolumns Fixed, and put them in the next column, then sort using your regular column as the first sortkey and this new column as your second sortkey............then you could Concatenate them back together if you wish...... Vaya con Dios, Chuck, CABGx3 "confused on the tundra" wrote in message ... I have about 1350 5 and 6 digit numbers starting in column B2 that I would like to be sorted. About 200 of the numbers have "A" at the end along with a coulpe that have "B", "C", "D", and "E" . All the numbers with a letter are six digits long if that helps i.e. 123456A. Now when I sort the numbers sort fine but all the numbers with a letter attached are at the bottom of the list, presumably as text. How can get the numbers and text"numbers" to sort together? 
#4




Hi
Create an helper column with formula in it (an example for row a2 below): ="" & B2 and sort your data on this helper column. Arvi Laanemets "confused on the tundra" wrote in message ... I have about 1350 5 and 6 digit numbers starting in column B2 that I would like to be sorted. About 200 of the numbers have "A" at the end along with a coulpe that have "B", "C", "D", and "E" . All the numbers with a letter are six digits long if that helps i.e. 123456A. Now when I sort the numbers sort fine but all the numbers with a letter attached are at the bottom of the list, presumably as text. How can get the numbers and text"numbers" to sort together? 
#5




"Domenic" wrote: Try the following... C2, copied down: =IF(D2="",B2,SUBSTITUTE(B2,D2,"")) This populated column C with values from B until it got to the numbers with the letter suffix and put #value with the little triangle in the upperleft corner. D2, copied down: =IF(ISNUMBER(B2),"",RIGHT(B2)) When I do this no numbers show up in the any of the cells but the formula is in the Fx box on the tool bar for all the cells. Then select Columns B, C and D, and sort by Column C and D... Data Sort Sort by "Column C" Then by "Column D" When I sort it stays the same as before with the number with suffixes on the bottom. What am I doing wrong? One way would be to break off the letters using data Texttocolumns Fixed, and put them in the next column, then sort using your regular column as the first sortkey and this new column as your second sortkey............then you could Concatenate them back together if you wish...... BINGO! that did the trick. Thanks Vaya con Dios, Chuck, CABGx3 
#6




That got it! Thanks. I just have to remenber to keep the two pre concatenate
columns there so I can resort after changes and sort by other columns. "CLR" wrote: One way would be to break off the letters using data Texttocolumns Fixed, and put them in the next column, then sort using your regular column as the first sortkey and this new column as your second sortkey............then you could Concatenate them back together if you wish...... Vaya con Dios, Chuck, CABGx3 "confused on the tundra" wrote in message ... I have about 1350 5 and 6 digit numbers starting in column B2 that I would like to be sorted. About 200 of the numbers have "A" at the end along with a coulpe that have "B", "C", "D", and "E" . All the numbers with a letter are six digits long if that helps i.e. 123456A. Now when I sort the numbers sort fine but all the numbers with a letter attached are at the bottom of the list, presumably as text. How can get the numbers and text"numbers" to sort together? 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Sorting imported "numbers"  Excel Discussion (Misc queries)  
numbers and text in Excel to read as text keeping the leading zer.  Excel Discussion (Misc queries)  
Seed numbers for random number generation, uniform distribution  Excel Discussion (Misc queries)  
I enter numbers and they are stored as text  Excel Discussion (Misc queries)  
Converting text to numbers  Excel Discussion (Misc queries) 