Skip to Main Content

Microsoft Tips and Codes

Codes, tips and tricks found over years of experience using Office.

Sorting Call Numbers

Red DiamondRed DiamondRed Diamond

If there is a list of books in Excel that needs to be sorted by call number, the list will not load properly because of Excel's rules on sorting. Here is a partial example of a list of titles "sorted" by call number in Excel:

AE25 E3 2002
AE27 B782x 1996
AE27 G76x 2003
AE5 E333 2003
AE5 E363 2003
AE5 E363 2010

The problem is obvious from the start. AE5 should be in the list before AE25 and AE27. I have been contacted by others trying to solve this as well, so I went to work looking for a solution.

Enter Power Query.

Power Query is a relatively new feature to Excel. Along with Power Pivot, some very tricky and intricate calculations can be done automatically over multiple sheets in a workbook, or even scattered about your department. I found a way to sort LC call numbers by splitting the Class number into two columns. The alpha column will sort as a general format. Changing the numeric part of the class into a "decimal" format instead of "general" in Excel, will make the list fall in line. The Cutter will automatically sort correctly because excel sorts it correctly as it is.

Once the steps of this method are completed, the resulting table can be used to find and sort call numbers by using the common ID, such as a bibliographic number or ISBN. Just be sure to use a unique number to what you are sorting for each record.

Thanks to Ken Puls' Blog post "Keep Only Numbers in Power Query" for the inspiration behind this method (the link is below).

I have included a copy of my workbook so you can see how I did this.

This method assumes a basic understanding of Power Query.

Steps - This example is in Excel 2016

Using Power Query, find the data you wish to sort. I suggest you stay linked to that table so this list will automatically update when titles are added or removed from the raw data list. For testing, I downloaded part of a larger table and placed in the workbook so you can see how this works.

Two columns are necessary for this procedure:

  • One column with a unique identifier. I used the bibliographic number for this example.
  • The column with the call number in it.

Convert the worksheet into a table and name it RawData. There will be three separate Power Queries run.

NOTE: The original list of call numbers did not include a decimal between the Class and Cutter numbers. Additional formatting to account for the decimal would be necessary for this to work correctly. Alternatively, if your call numbers are set up with the decimal, please check out the links on the right-hand column with macros that will sort with the decimal present.

The first query sheet you will make prepares the call number for sorting.

  1. From the ribbon, Click Data > From Table
  2. Select the call number column and from the Add Column tab, click Duplicate
  3. Rename the duplicated column Sort
  4. Select the Sort column
    • From the Transform ribbon, select Split Column > By Delimiter
    • Choose Space for a delimiter if it is not already selected
    • Click Insert (again, this will not work if you have a decimal between the Class and Cutter numbers). This will split all the pieces of the call number into columns where there are spaces in the call number.
  5. Select all columns Sort.2 through Sort.# where # is the number of the last column. Do not select Sort.1
    • From the Transform ribbon, select Merge Columns and use a Space delimiter
    • Rename the column Sort3
  6. Rename the Query PrepareSheet (or whatever makes sense to you. I will use this name for the following functions)
  7. From the Home tab, click Close & Load
  8. Rename the resulting sheet PrepareTable

Here is a breakdown of the Power Query formulas that run for this procedure. The ones with an asterisk by them automatically run in power query:

*= Excel.CurrentWorkbook(){[Name="Table1"]}[Content]
*= Table.TransformColumnTypes(Source,{{"bibNo", Int64.Type}, {"call_no", type text}})
= Table.DuplicateColumn(#"Changed Type", "call_no", "call_no - Copy")
= Table.RenameColumns(#"Duplicated Column",{{"call_no - Copy", "Sort"}})
= Table.SplitColumn(#"Renamed Columns", "Sort", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv),   {"Sort.1", "Sort.2", "Sort.3", "Sort.4", "Sort.5", "Sort.6"})
*= Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Sort.1", type text}, {"Sort.2", type text}, {"Sort.3", type text}, {"Sort.4", type text}, {"Sort.5", type text}, {"Sort.6", type text}})
= Table.CombineColumns(#"Changed Type1",{"Sort.2", "Sort.3", "Sort.4", "Sort.5", "Sort.6"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged")
= Table.RenameColumns(#"Merged Columns",{{"Merged", "Sort3"}, {"Sort.1", "Sort"}})

Here is what my worksheet looks like after this step:

Now to do some DAX Codes to divide the Sort column into two separate ones: one of the alpha characters, and one with the Class Number including the decimals.

  1. From the Data Ribbon: Click on New Query > From Other Sources > Blank Query
  2. When The Query window opens, click on Advanced Editor
  3. If you have named the tables and columns the same as mine, paste the following code in the editor window:

let
CharsToRemove1 = List.Transform({48..57}, each Character.FromNumber(_)),
Source = Excel.CurrentWorkbook(){[Name="PrepareSheet"]}[Content],
#"Changed Type1" = Table.TransformColumnTypes(Source,{{"Sort", type text}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "Sort1", each Text.Remove([Sort],CharsToRemove1))
in
#"Added Custom1"

4. Click Done and the results should show in the Power Query window.
5. Rename the query Sort1
6. Close & Load the sheet. It should now look like this:

 

Step 3 is like step 2 except for the code

  1. From the Data Ribbon: Click on New Query > From Other Sources > Blank Query
  2. When The Query window opens, click on Advanced Editor
  3. If you have named the tables and columns the same as mine, paste the following code in the editor window:

let
CharsToRemove2 = List.Transform({33..45,47,58..126}, each Character.FromNumber(_)),
Source = Excel.CurrentWorkbook(){[Name="Sort1"]}[Content],
#"Changed Type2" = Table.TransformColumnTypes(Source,{{"Sort", type text}}),
#"Added Custom2" = Table.AddColumn(#"Changed Type2", "Sort2", each Text.Remove([Sort],CharsToRemove2))
in
#"Added Custom2"

4. Remove the Sort column
5. Reorder the columns as Sort1, Sort2, Sort3
6. Change Sort2 to decimal format
7. Sort each row.

Here is steps 4 through 7 in DAX code:

= Table.ReorderColumns(#"Added Custom2",{"bibNo", "call_no", "Sort", "Sort1", "Sort2", "Sort3"})
= Table.RemoveColumns(#"Reordered Columns",{"Sort"})
= Table.TransformColumnTypes(#"Removed Columns",{{"Sort2", type number}})
= Table.Sort(#"Changed Type",{{"Sort1", Order.Ascending}, {"Sort2", Order.Ascending}, {"Sort3", Order.Ascending}})

Note that by making Sort2 a decimal, it will not default to Excel's "General" format that treats numbers differently than in a numeric format. One can get numbers to behave:

Here is the scenario:

My boss wants a list of titles by call number that are in the Reference Collection at the library. They are all LC Call numbers, so all I need to do is get the list and sort it.

  1. Open a new Excel workbook and link it to the list of titles through Power Query, adding the list to the data model
  2. Also link this method to the new workbook, adding it to the data model
  3. Using an INDEX() / MATCH() formula, I can add the three sort columns to the correct records by the common ID
  4. Once the three columns are added
    1. Go to the Home Ribbon
    2. Click the pulldown arrow under Sort & Filer and choose "Custom Sort..."
    3. Click "Add Level" twice to add two sorting columns
    4. Populate the "Sort By" fields in this order: Sort1, Sort2, Sort3
    5. Click OK.
  5. Your list should be in call number order.

Again, this assumes your call numbers are formatted without at decimal between the Class and Cutter, so keep that in mind as you work through this. I will be adding a procedure for formatting call numbers so they are uniform soon!

Links to Excel Call Number Sort codes and macros

NOTE: For both of these methods, there must be a decimal between the Class Number and the Cutter Number.

A few notes

To ignore SuDOC or uncataloged items in your list, use a filter in Power Query to remove them from your list before you start. Here are some of the call numbers in our library that will not display properly using this method:

  • SuDOC: C 3.277:CD-AG 92-1 B
  • CDROM: CDRM 000 090
  • Uncataloged, but still with a call number: Uncat HE203 1992 TC92-CF-14

Please share your thoughts! I would love to know if you found an easier way!