MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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"?