Sorting LC Call Numbers in Excel
This is one of those situations I alluded to in my last post, where even Google couldn’t solve my problem so I had to figure it out myself. We were weeding (or, choose your preferred euphemism: pruning, discarding, deaccessioning) and shifting books. We needed to be able to sort, manipulate, print, highlight, share, and carry around with us a large set of bibliographic data. Using Excel, we could easily sort by author or title, but we wanted to also sort by call number. We use the Library of Congress classification system.
Google turned up a few options, like this simple, powerful approach by the Penn State University libraries. If you are in a library without legal materials, it will work great and I recommend it. But for me, working in a law library, this and all of the other solutions fell short, because those librarians didn’t need to account for call numbers whose first line has three characters. International Law materials and U.S. state legal materials did not sort properly by any of the available methods because they had that one extra character in the first line.
So I adapted Penn State’s solution and added a few steps to solve this problem. Here’s how I did it:
Step 1:
Put the call numbers into column F, as we’re going to need A-E to run some formulas that will make the call-numbers machine-sortable. All of the other columns of bibliographic data can go in columns after F.
Step 2:
In cell A1 (or A2 if you’re using the first row for headings), put in this formula*:
=MIN(IF(ISERROR(FIND({1;2;3;4;5;6;7;8;9;0},F1)),””,FIND({1;2;3;4;5;6;7;8;9;0},F1)))This formula searches the call number in F1 for a number. If it does not find a number, it returns a null string. If it finds a number, it returns the location of that character in the string. This tells us the number of characters in the first line of the call number.
*(It’s been brought to my attention that copying/pasting from Medium to Excel does not work if the text has quotation marks. If this isn’t working for you, try removing the quotes after copying/pasting, and then typing in your own.)
Step 3:
In cell B1, put in this formula:
=IF(A1=2,”**”,IF(A1=3,”*”,””))This formula takes the value we found in column A, and determines how many placeholder characters we’ll need to add so that the first line of every call number has a standard length. In this case, we will be adding placeholders wherever necessary so that there are always three characters.
Step 4:
In cell C1, put in this formula:
=IF(IF(ISERROR(FIND(“.”, F1, 1+FIND(“.”,F1))<FIND(“ “, F1)), IF(ISERROR(FIND(“.”,F1, 1+FIND(“.”,F1))), FALSE, TRUE), FIND(“.”,F1, 1+FIND(“.”,F1))<FIND(“ “, F1)), F1, CONCATENATE(LEFT(F1,FIND(“.”,F1)),”0.”,RIGHT(F1,LEN(F1)-FIND(“.”,F1))))This is one of Penn State’s steps. It is one of the two steps necessary to standardize the length of the second line of each call number. This formula determines whether that line has a decimal place and, if it doesn’t, it adds one (“.0”) so that all of them have it.
Step 5:
In cell D1, enter this formula:
=IF(A2=4, CONCATENATE(LEFT(C2,IF(ISERR(VALUE(MID(C2,3,1))),3,1)),REPT(“0”,5-FIND(“.”,C2)+IF(ISERR(VALUE(MID(C2,3,1))),3,1)),RIGHT(C2,LEN(C2)-IF(ISERR(VALUE(MID(C2,3,1))),3,1))), CONCATENATE(LEFT(C2,IF(ISERR(VALUE(MID(C2,2,1))),2,1)),REPT(“0”,5-FIND(“.”,C2)+IF(ISERR(VALUE(MID(C2,2,1))),2,1)),RIGHT(C2,LEN(C2)-IF(ISERR(VALUE(MID(C2,2,1))),2,1))))This is an adapted version of Penn State’s other step. It determines the length of the second line of the call number and then adds leading zeroes to it where necessary so that all call numbers will have four digits followed by a decimal point and then one more digit. The part I added makes the function account for situations where the first line has three characters.
Step 6:
We’re almost done. In cell E1, enter this formula:
=IF(A2=4,D2,REPLACE(D2,A2,0,B2))This formula takes the value we found in column A and uses it to add the appropriate number of placeholder characters to the first line of the call number.
Step 7:
Now the length of each call number is standardized, so we’re ready to sort. For each column, copy the formulas down (by clicking and dragging from the corner of the first cell) to include as many rows as you need. Then simply sort Column E in ascending alphabetical order (and select “expand the selection” when the Sort Warning window appears) and you will find that all of those call numbers you copied into column F are sorted in call number order!
Bonus Step:
If you want to go one step further for ease of use, you can create a button and assign a VBA macro to it so that pressing the button sorts the call numbers automatically. Then you can hide those columns A-E that we created so that you only have to look at the metadata you need. Here’s the VBA code for that macro:
Sub SortByCallNumber_Click()Columns(“F:F”).Select Selection.Replace What:=” .”, Replacement:=”.”, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseDim oneRange As Range
Dim aCell As RangeSet oneRange = Range(“A1:BM6000”)
Set aCell = Range(“E1”)oneRange.Sort Key1:=aCell, Order1:=xlAscending, Header:=xlYesEnd Sub
I hope that someday, in a time of need, a law librarian discovers this post and that it helps them. Happy sorting!
