Alpha/numberic sorting
Posted by Judy on June 19, 2001 12:36 PM
I work at a library. Sorting books by Library of Congress call number may well be impossible, though if you have any ideas, I'm listening...
More immediately, though, I have a rather simple sorting problem. We have some books designated by three letters, then a whole number (ORC 1, ORC 1000, ORC 453). I want to ignore the ORC and sort by whole numbers. Because there is text in the cell, Excel wants to sort them like this: ORC 1, ORC 1000, ORC 2, etc.
Someone posted a formula to extract the number from the cells, paste it down another column, sort by the new column, and then delete it. The formula looked like this:
=MID(E1,5,100) -- my data is in column E, and I wanted to eliminate the ORC and space before the number. I will be the first to admit I do not actually understand this formula, but it got me a nice column of numbers.
However, sorting them doesn't work. Excel refuses to believe they are numbers. I formatted all the cells to 'number' and nothing happened -- I couldn't give them any decimal places, etc. Sorting results in the exact same order the items would have been in if I were sorting by the column with the ORC preceding the numbers.
In my infinite naivete, I even tried copying and re-pasting just the values into yet another column, so I've just got numbers rather than formulae. No dice. If I hand-type in identical numbers, sorting works, but nothing I do can make these numbers numbers. Help?
Posted by Barrie Davidson on June 19, 2001 12:39 PM
Try changing your formula to:
=VALUE(MID(E1,5,100))
That should do the trick.
Barrie
Posted by Aladin Akyurek on June 19, 2001 12:44 PM
Use
=MID(E1,5,100)+0
If the alpha part is separated by a space from the numeric part, use instead
=RIGHT(E1,LEN(E1)-SEARCH(" ",E1))+0
Aladin
Posted by Judy on June 19, 2001 1:46 PM
Thank you! It worked. Am I alone in wishing there were a way to tell Excel, "pay no attention to any non-numerical characters in these cells, just put the numbers in order"?
