Forum Discussion
Sorting Dewey codes in Excel
How do I sort a column where the cells contain both numbers and text eg 398 FOLK, 920 ATT, 942 TUDORS as well as just numbers eg 577, 796.33. I have tried making the cell format text, general and special but none work.
Can I make a custom cell format to cover this (haven't been able to work out how)?
Well, a prior question would have to be "How do you want them sorted?" On the basis of the numeric values; alphabetically, on the basis of the letters; some hybrid of your own design?
What happens now when you sort them by all those different methods you mention? What about each of those is falling short of what you want?
We need to answer these questions--to define the sort you desire--before coming up with the satisfactory method. I realize you probably think the answer is obvious, given the reference to the Dewey (decimal?) code, but not all of us are versed in that coding system, even if we do know a lot about a lot of books.
Well, a prior question would have to be "How do you want them sorted?" On the basis of the numeric values; alphabetically, on the basis of the letters; some hybrid of your own design?
What happens now when you sort them by all those different methods you mention? What about each of those is falling short of what you want?
We need to answer these questions--to define the sort you desire--before coming up with the satisfactory method. I realize you probably think the answer is obvious, given the reference to the Dewey (decimal?) code, but not all of us are versed in that coding system, even if we do know a lot about a lot of books.The way to do it with Data | Sort would be to create a custom list of DD codes and have Excel sort on this instead of A-Z or Z-A. Custom lists are limited to 255 characters and I'll venture to say this would be a problem with your data set.
The other way to sort is using dynamic arrays. With a standard list of DD codes you could use something like this:
=LET( i, --TEXTBEFORE(DD, " "), a, TEXTAFTER(DD, " "), SORTBY(DD, i, 1, a, 1) )I tried to break it by adding dummy DD codes that shared the same number prefix as those already listed and it held up. You may want to share a sample workbook if your data contains anomalies (e.g. cells with only numbers and no text).
I'd suggest to modify a bit
=LET( i, --TEXTBEFORE(DD," ",,,1), a, TEXTAFTER(DD," ",,,,""), SORTBY(DD,i,1,a,1) & "" )to fix for the records where we have numbers only.
- Thank you for spending time helping to sort this!
- Thank you for spending time helping to sort this!
Having now done a little research on Dewey Decimal codes, seeing that the code always includes (and always begins with) the three digits, my suggestion for you would be to use a helper column to extract the numbers from the full code and let that be the basis for your sort. See the attached.
My solution is admittedly far less elegant than the one offered by Patrick2788 , but maybe more understandable. If your need is for a full major public library, go with his, by all means. If you're just struggling with sorting -- or wondering where to file -- a few new books in your private library, mine might work just fine.
One of the fun things of Excel--there are always several ways to get from need to solution.
- Thank you for the time and effort you've put into helping solve this. It's worked! I'm a school librarian whose library management system is corrupt and I'm cataloguing on Excel at the moment. So happy I can now sort properly! 🙂
- Thank you for spending time helping to sort this!
