...
First things first though. You are going to want to add a separator in front of any data that is already in the Comments Column. To do this, we'll be using facets to act ONLY on the cells with data in them, and the Transform command to add a character in front of the comments. To do this, we create a facet on the comments section, hover over the (blank) and click on the "include" option. Then select the 'invert' option near the top of the facet title.
This 'flips' the selection, and now you should see only the cells with data in them. From the Comments drop down, select Edit Cells and from the sub menu, select Transform. A new window will appear. Adding information is very simple. Make sure the Language is set to the default value, Google Refine Expression Language (GREL). We are going to add a semi-colon and a space before the existing dataexisting data.
Now that we've done that, we can add the previous storage location without ending up with statments in the field that read "SW 269/9Young, Oversized"!
...
In practice, it looks like this:
The same 'recipe' is the foundation for how we take our old database numbering style and make it Specify compatible. But first, we have to use the recipe for padding with 0's.
The recipe is: value +"0000"0,4-value.length()
The recipe says to take the value, add as many 0's as are needed to make the field have 4 values stored in it.
This will add 4 0's to our value. If we need more, we increase the number of 0's to what we need, then change the number inside the brackets to match.
Specify expects to see 3 values for the Collection, 8 values for the specimen number, a decimal then 3 values for the suffix. Remember here that letters follow the .R00 format, and numbers .001.
Suffixes like T6 would be set to .T06
To make the Collection have 3 digits, paste this expression into the Transform window for the Collection column-value "000"0,3-value.length()To make the Specimen number have 8 digits, paste this expression into the Transform window:"0000000" value0,8-value.length()
Notice how the order has changed a little- the string of 0's is at the start, and the value is added to it. This gives us 0 padding in front of the number. For the Collection, we wanted the 0's to come at the end of the collection acronym so the expression structure was swapped around.
Changing the suffixes is a little more involved. First, facet the column. All the entries for '.' can be bulk edited to "000". Don't put a decimal in just yet. Create a text filter and type this into the box: [a-zA-Z]
Select the 'regular expression' box. We are telling the text filter to show records that have a-z in them, capitol or lower case letters.
Transform on the filtered column:value +"000"0,3-value.length()Remember, for letters we want A00 B00 and so on. Now change the a-zA-Z to 0-9, leaving the brackets in place.
Transform on the filtered column:"000"+ value0,3-value.length()You now have all suffixes transformed and properly formatted.
Now that all are formatted for Specify, combine them into a new column named "Catalog Number". The recipe looks like this: 
These recipes can be adapted, combined and broken apart to do a variety of actions. Refine is exceptional in that it allows you to undo as much as you need- straight back to the start, if you want.
| Code Block |
|---|