CSU Library Website Customization

Problem 

CSUL Special Collections maintains an Excel file, in which contains books titles, call numbers, locations, estimated values, and etc. The file has thousands of entries. At the beginning we tried to find book value for each book and recorded them. If we can't find a real value for the book, we will use a placeholder value. Over time, the task become too time consuming. We decide to use Library Congress estimated value for LC subclasses. 

Goal

Our file comes with call numbers but not LC subclasses. We don't want manually change each call number and input congress' estimate value, because we have thousands of records. We need an semi automated process to do the job without bugging programmers. 

1. We want strip extra numbers and letters and leave only LC subclasses. We can not simply apply Excel's build in function such as LEFT(text, [num_chars])/ Extract substring of a specified length because LC subclass can be 1 to 3 letters.

2. We need automatically match LC subclass with the congress' estimated values.

Solution

Step 1. Remove everything after the first dot

P120.S48 H54 1986  change to  P120

0. Original file 

1. Create a new column and copy & paste the column with call numbers to the new column. Highlight all cells -> ctrl + F -> Click the replace tab -> type .* in the find what field and leave replace with empty/do nothing there -> click replace all.

 

Result

2. Remove numbers. 

• Alt + F11, to bring up Visual Basic window -> Insert -> Module

 

• Copy and paste the following code to the window.  The original code is on this page. 

Function RemoveNumbers(Txt As String) As String

With CreateObject("VBScript.RegExp")

.Global = True

.Pattern = "[0-9]"

RemoveNumbers = .Replace(Txt, "")

End With

End Function

 

• Save the code 

 

• Create a new column -> Go to the first cell you want apply the function -> type =RemoveNumbers(B2)  (B2 is the cell, Colunm B and Row 2, you want apply the function). You will see 93 is removed and only P left. 

 

• Click this cell and drag the corner to the end of the column to apply the same function to all other cells. (We have not found an easy to select all cells but do the dragging)

Puzzle encountered

The original Excel has many sheets. The RemoveNumbers function works for all sheets except one sheet even though we did the exact same steps to all sheets. 

The cell recognize the function but the cell display formula rather than result. It turns out for some reason that column was formatted as Text. To fix this, select the first cell with function already applied -> Right click your mouse -> select format cells -> select General -> OK

You will see correct result. Now you can do select and drag to apply the function to rest of cells. 

 

Abnormal Call Numbers

After running the normal find/replace .* and remove number processes, you might still have LC subclasses that have problems. We need either run TRIM function or manually fix some errors. These errors caused by abnormal call numbers. 

1. Dot is behind year 

Example G7960 1942 .U55  & G8060 1942 .U55  cause extract empty space behind G.  We can run TRIM(CellNmber) to remove the empty space. 

2. Space before the first dot

GA405 .W5 v.1 and GA405 .W5 v.5, pt.2 cause extract empty space behind GA.  We can run TRIM(CellNmber) to remove the empty space. 

Other examples: KF644 .C65 v.1-4  LC197 .F478 1998  

3. Call number has not dot 

LD6355 1853  generates LDspace. We can run TRIM function to fix this. 

 

4. Manual Fix

PT 2625 A44V63  The Coming victory of democracy. The find .* function did not work on this record, so after the remove number function, we have two set of letters left. For this case, we need manually remove extra letters and spaces. 

Other Old records

Human Error

This record was a human error. Accession number was place in the call number field.  This one has to be manually corrected.