Home |
Search |
Today's Posts |
#1
|
|||
|
|||
sort alphanumeric
I want to sort data that contains numbers and letters. I have tried
formatting the row as text and it still does not work. Everytime I sort it doesn't list the entires in order but rather the numbers first then the numbers with letters next. Here is what im trying to sort: 1227 1244 1257 1277 1289 1421 1422 1423 1431 1432 1438 1494 1572 1258-A 1258-B 1258-C 1258-D 1258-F 1258-G 1273-A 1273-B 1273-C 1273-D 1273-E 1273-F 1403-E 1403-G Can anyone Help! |
#2
|
|||
|
|||
sort alphanumeric
If you input a number and then change the format to text, the change does not
"set in" right away. I copied and pasted your list into excel and sorted to get the result you got. I then changed the format to text and got the same result. I then hit F2 and then enter for each cell in the list. At this point it sorted correctly. On way to avoid this, is to use an ' (apostrophe) before numbers that should be text. This formats it as text to begin with. 1227 1244 1257 1258-A 1258-B 1258-C 1258-D 1258-F 1258-G 1273-A 1273-B 1273-C 1273-D 1273-E 1273-F 1277 1289 1403-E 1403-G 1421 1422 1423 1431 1432 1438 1494 1572 "William" wrote: I want to sort data that contains numbers and letters. I have tried formatting the row as text and it still does not work. Everytime I sort it doesn't list the entires in order but rather the numbers first then the numbers with letters next. Here is what im trying to sort: 1227 1244 1257 1277 1289 1421 1422 1423 1431 1432 1438 1494 1572 1258-A 1258-B 1258-C 1258-D 1258-F 1258-G 1273-A 1273-B 1273-C 1273-D 1273-E 1273-F 1403-E 1403-G Can anyone Help! |
#3
|
|||
|
|||
sort alphanumeric
William, Try a helper column (a spare column for sorting purposes only, hidden if required) with something like: =IF(ISERROR(A1+0),1,0) and formula copy to the end of your data. Then sort over helper (ascending) and column A (ascending) William Wrote: I want to sort data that contains numbers and letters. I have tried formatting the row as text and it still does not work. Everytime I sort it doesn't list the entires in order but rather the numbers first then the numbers with letters next. Here is what im trying to sort: 1227 1244 ~~ 1494 1572 1258-A 1258-B ~~ 1403-E 1403-G Can anyone Help! -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=482979 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
sort alphanumeric
I have the same problem as William, but your solution does not work on my
data. What other suggestions might you have? "Sloth" wrote: If you input a number and then change the format to text, the change does not "set in" right away. I copied and pasted your list into excel and sorted to get the result you got. I then changed the format to text and got the same result. I then hit F2 and then enter for each cell in the list. At this point it sorted correctly. On way to avoid this, is to use an ' (apostrophe) before numbers that should be text. This formats it as text to begin with. 1227 1244 1257 1258-A 1258-B 1258-C 1258-D 1258-F 1258-G 1273-A 1273-B 1273-C 1273-D 1273-E 1273-F 1277 1289 1403-E 1403-G 1421 1422 1423 1431 1432 1438 1494 1572 "William" wrote: I want to sort data that contains numbers and letters. I have tried formatting the row as text and it still does not work. Everytime I sort it doesn't list the entires in order but rather the numbers first then the numbers with letters next. Here is what im trying to sort: 1227 1244 1257 1277 1289 1421 1422 1423 1431 1432 1438 1494 1572 1258-A 1258-B 1258-C 1258-D 1258-F 1258-G 1273-A 1273-B 1273-C 1273-D 1273-E 1273-F 1403-E 1403-G Can anyone Help! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
sort alphanumeric
This works for me except for the numbers that are multiple digits. Any
suggestions? example: 1 1a 2 3 4 10 10a 10b 11 200 200a 200b "Sloth" wrote: If you input a number and then change the format to text, the change does not "set in" right away. I copied and pasted your list into excel and sorted to get the result you got. I then changed the format to text and got the same result. I then hit F2 and then enter for each cell in the list. At this point it sorted correctly. On way to avoid this, is to use an ' (apostrophe) before numbers that should be text. This formats it as text to begin with. 1227 1244 1257 1258-A 1258-B 1258-C 1258-D 1258-F 1258-G 1273-A 1273-B 1273-C 1273-D 1273-E 1273-F 1277 1289 1403-E 1403-G 1421 1422 1423 1431 1432 1438 1494 1572 "William" wrote: I want to sort data that contains numbers and letters. I have tried formatting the row as text and it still does not work. Everytime I sort it doesn't list the entires in order but rather the numbers first then the numbers with letters next. Here is what im trying to sort: 1227 1244 1257 1277 1289 1421 1422 1423 1431 1432 1438 1494 1572 1258-A 1258-B 1258-C 1258-D 1258-F 1258-G 1273-A 1273-B 1273-C 1273-D 1273-E 1273-F 1403-E 1403-G Can anyone Help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort - alphanumeric. | New Users to Excel | |||
how can I sort alphanumeric entries by number in excel | Excel Worksheet Functions | |||
Select rows and sort based on type | Excel Discussion (Misc queries) | |||
Alphanumeric Sort | Excel Discussion (Misc queries) | |||
Alphanumeric Sort | Excel Discussion (Misc queries) |