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?