sorting alphabetically without first sorting numerically
Posted by Jonathan Rigby on December 19, 2000 8:48 AM
Hello,
I'm sure this is really easy, but I'm not that familiar with excel. I have a large spreadsheet of chemical names, some pre-fixed with numbers, some not. I would like to sort alphabetically only, totally ignoring the numerical pre-fixes to get a true alphabetised list. However no matter what I do excel always numerically sorts the ones with numerical pre-fixes first which therefore totally de-alphabetises the list. Can anyone help?
Jonathan
Posted by cpod on December 19, 2000 9:18 AM
Are the numbers a fixed number of digits in each entry (when there is a number), or is there a common separator (-,/) between the number and the name?
Posted by Jonathan Rigby on December 20, 2000 2:56 AM
:There are between 1 and 4 digits when there is a number. The seperators are usually - or , but sometimes items in () are involved. What I really want is to only sort alphabetically, ignoring numbers and symbols completely. If possible!!!
Posted by cpod on December 20, 2000 5:44 AM
What you have to do is recreate the list in another column with the numbers stripped off and then sort by that column. enter this formula into the second row of an empty column. If your list is in a column other than A you will have to change all instances of A2 to reflect that. Then copy down the column.
=TRIM(MID(A2,IF(ISERR(FIND("-",A2)),IF(ISERR(FIND(")",A2)),-1,FIND(")",A2)),FIND("-",A2))+1,100))
Without seeing the list it's difficult to know if this will solve all items in the list. Let me know how it works.
Posted by cpod on December 20, 2000 5:59 AM
Sorry, slight correction. Use this one instead:
=TRIM(MID(A3,IF(ISERR(FIND("-",A3)),IF(ISERR(FIND(")",A3)),0,FIND(")",A3)),FIND("-",A3))+1,100))
