/
949 fields creation using columns in Excel

949 fields creation using columns in Excel

Owned by Melanie Shaw
Last updated: Oct 05, 2023
3 min read

From a batch-original project in 2017.

(This old workflow could use some refining: feel free to comment.)

 

Creating a 949 column in an Excel spreadsheet containing call number columns and barcodes:

In Excel, open the spreadsheet, and begin merging the information needed for the 949 into a new column, inserting the subfield characters for the future 949 fields along the way.

1-Begin the 949 process:

  • Create a new tab to work in.

  • Copy the barcode and call number (090) columns from the main tab, and paste them into the new tab.

 

2-Reformat the call number columns:

  1. Insert spaces within the call number stem and add a ^ symbol at the end of the call number stem to hold a space between the stem & remainder of call number:

    • Replace: M1.C422am with M 1 .C422am^

  2. Create a new column called “949callno” and merge the two call number columns into the new one, using this formula: =(B2&C2)

  3. Then copy/pull the formula in the first cell down the rest of the rows (Excel will translate the formula for each row).

  4. The resulting column will display the merged call number, but will still contain the formulas underneath.

  1. To extract just the call number values, make another new column and copy the merged call number column:

    • Paste the data, as Values into the new column and name the new column: 949a.

  2. Then erase all the old call number columns, leaving behind the new 949a.

  3. Finally, Replace ^ with nothing and save.

 

Add $i to the beginning of the barcodes in the 949i.

  • Since all our barcodes back then started with 390600, I used a simple replace function:

  • Find/Replace 390600 with $i390600

    • Spot check the barcode values to make sure they haven’t been corrupted by Excel

 

 

Create the remaining 949 subfield columns:

  • Add new columns, each with a $ subfield code and fill with the Sierra codes for each (which will be identical for each row).

    • Make sure Excel doesn’t increment any of the numbers.

 

 

Merge all the 949 columns:

  • Insert a new column (949merge) and extract each of the 949 subfield columns into it, using the formula of: =CONCATENATE(A,B2,C2,D2,E2,F2)

  • Pull down the first cell formula and Excel will translate it for each row.

  • The 949merge column should display the merged text:

M1.C533am BEETHOVEN VS no.1-10$vpiano$i39060018665892$lgbks$t0$x18$ka

But underneath the display text, the cells will contain formulas.

  • So, create a new column: “949ailtxk”and copy the 949merge column, and Paste Values alone, into 949ailtxk.

  • Once this is done, delete all other columns, leaving just the clean text in the 949ailtxk column:

 

This will be the version used in the main spreadsheet.

 

3-Copy the finished 949ailtxk column and go back to the tab for the main spreadsheet.

4-Paste the 949ailtxk column at the end of the main spreadsheet.

At this point, delete columns used to create the 949 column from the main spreadsheet, unless they are needed elsewhere (such as the call number columns)