WYLD TECHNOTES: Director's Station - Removing initial articles to sort title field in exported reports
Version 4.9 of Director's Station introduced a change in the way that data is extracted from Symphony. One result of this change is that initial articles are included in the output of the 'drilldown' reports. Previous versions of Director's Station removed the initial articles in the display, thus making it easier to sort on the title column. This technote provides instructions for adding a formula in Excel to remove the initial articles from the title column and then sort in alphabetical order on the edited column.
Note that although the examples in this technote refer to the Office 2007 version of Excel, the basic instructions for formulas and the sort functions are the same for other versions
Part 1 - Extract the data
Once you have designed your report in Director's Station, select the cells you wish to output by clicking on one corner of the cell or cells in question and dragging the mouse to the end of the cell or group of cells. A popup dialog will appear with options contextual to that data set. For this example, we are using 'List copies by number of total checkouts'.
Click on the menu button to select the export option. Select to open or save to Excel
Part 2 - Using the formula to remove A, AN, THE
Below is an example of the default report format when opening an extract from Director's Station in Excel.
For the purposes of this example, we will remove the top row (the report title text 'List Copies by Number of Total Checkouts ') so that we have only column headings starting with cell A1. Right click on the number (1) and select 'Delete' from the menu.
After removing the report title line, right click on the cell immediately to the right of the title column and select 'insert' from the menu. In this example, that column is the 'Author' column. The insert function will create a blank column next to the title column and move the 'Author' column over to the right. Give the new, blank column a name, such as titlesort.
Assuming that the first cell of the text you wish to edit is B2, copy this exact formula, with no line breaks, into the first blank cell in the column you just inserted (C2). You can use the keyboard functions to copy this quickly by highlighting the text in your browser with your mouse and then clicking ctrl-c. Open or tab to Excel, click in the appropriate cell and type ctrl-v.
=IF(LEFT(B2,2)="A ",REPLACE(B2,1,2,),IF(LEFT(B2,3)="An ",REPLACE(B2,1,3,),IF(LEFT(B2,4)="The ",REPLACE(B2,1,4,),B2)))
If you do not replace the title line or show the column you wish to sort in a column other than B, replace all the B2 values in the formula above with the first cell value where the column starts. E.g. you have removed the checkouts column and have moved the title column to column A. Your title values start in cell A2 (A1 is the column header name), so your formula will look like this:
=IF(LEFT(A2,2)="A ",REPLACE(A2,1,2,),IF(LEFT(A2,3)="An ",REPLACE(A2,1,3,),IF(LEFT(A2,4)="The ",REPLACE(A2,1,4,),A2)))
After you have inserted the formula and then clicked somewhere else in the spreadsheet, you will have the text of the first title cell displayed in the first cell of your new column. To copy this formula to all the cells below, you can do either of the following.
- Click in the cell with the formula and type 'ctrl-c'. The cell will have a highlighted border appear. Click in the cell immediately below and then use your mouse or hold down the shift key while hitting page down to select as many cells in the column as you need. With the cells highlighted, type 'ctrl-v'.
- Another way to copy the formula is to click in the cell with the formula and click on the plus sign that will appear in the lower right corner. Hold down the mouse and drag the plus sign down the column. When you let go, all the cells you have highlighted will have copied the formula.
Part 3 - Sorting the Data
Excel provides a quick function to sort data. Now that the initial articles are removed, you can sort on your newly created column. If the 'Home' tab is active on the top menu in Excel, the sort function appears on the far right. If the 'Data' tab is active, then the sort options will appear in the middle of the menu.
Click on the column header. Now click on the Sort & Filter button (home menu) or the Sort button (data menu).
Select to sort from A to Z.
If this popup menu appears
- Choose to expand the selection
- Click 'Sort'
- Select the name of the edited column
- Click 'OK' to perform the sort function.
Part 3 - Cleaning up
You should now have two columns of titles. One is the original data, and the other is your edited column which has removed A, AN, and THE from the beginning of the text string. The edited column is a 'view' of the data. If you delete the original column that is the base for that view, then your edited column will have errors, because the referenced cells no longer exist. If you want to save a copy of the spreadsheet without two columns (the original title values and the edited values), you can use the 'Paste Special' feature in Excel to create a new column with the edited values and then remove any columns you no longer need.
- Right click on the column header to select the entire column with the edited data (cell contents are actually formulas).
- Select COPY from the popup menu.
- Right click on the column where you would like to copy the edited values (this can be the original title column, if you prefer, or a blank column)
- Select PASTE SPECIAL from the popup menu
- Select VALUES from the menu
- The results (values) of the formulas will be pasted instead of the formulas.
- Now you can remove the column with the formulas and the pasted values will remain the same
Part 4 - Editing Uppercase values to mixed case
A further edit you can make on textual data exported from Director's Station is to change the case from all uppercase to mixed case. The following assumes that the text to be edited begins in cell C2. Copy this formula into a new, blank column:
Using the techniques outlined above, copy the formula into all the following cells in the column and then copy and paste these values into a new column.
This function could be used on any text field exported from Director's Station, such as titles or patron names.
Example: This formula will convert TWO FOR THE DOUGH to Two For The Dough or SMITH JAMES to Smith James.