Other Helpful Resources for OpenRefine

• Regular Expressions Tutorial
• RegExr is an interactive reference and testing tool for regular expressions
• Basic tips put together by Michelle to introduce staff to OpenRefine in December 2018

OpenRefine Functions not included in JSON Scripts

Dates

Transforming dates (1932-1999)

value.replace("/32","/1932").replace("/33","/1933").replace("/34","/1934").replace("/35","/1935").replace("/36","/1936").replace("/37","/1937").replace("/38","/1938").replace("/39","/1939").replace("/40","/1940").replace("/41","/1941").replace("/42","/1942").replace("/43","/1943").replace("/44","/1944").replace("/45","/1945").replace("/46","/1946").replace("/47","/1947").replace("/48","/1948").replace("/49","/1949").replace("/50","/1950").replace("/51","/1951").replace("/52","/1952").replace("/53","/1953").replace("/54","/1954").replace("/55","/1955").replace("/56","/1956").replace("/57","/1957").replace("/58","/1958").replace("/59","/1959").replace("/60","/1960").replace("/61","/1961").replace("/62","/1962").replace("/63","/1963").replace("/64","/1964").replace("/65","/1965").replace("/66","/1966").replace("/67","/1967").replace("/68","/1968").replace("/69","/1969").replace("/70","/1970").replace("/71","/1971").replace("/72","/1972").replace("/73","/1973").replace("/74","/1974").replace("/75","/1975").replace("/76","/1976").replace("/77","/1977").replace("/78","/1978").replace("/79","/1979").replace("/80","/1980").replace("/81","/1981").replace("/82","/1982").replace("/83","/1983").replace("/84","/1984").replace("/85","/1985").replace("/86","/1986").replace("/87","/1987").replace("/88","/1988").replace("/89","/1989").replace("/90","/1990").replace("/91","/1991").replace("/92","/1992").replace("/93","/1993").replace("/94","/1994").replace("/95","/1995").replace("/96","/1996").replace("/97","/1997").replace("/98","/1998").replace("/99","/1999")

Extract dates from mixed title/date field

Separator (regular expression and only split into 2 columns): [,][ ](?=[\[\(c]*[a]*[.]*[ ]*[0-9]{2})|[,][ ](?=[u][n][d][a][t][e][d])|[,][ ](?=[A-Za-z]{3}[.]*[ ][0-9]{2})

If text remains at end of dates:

• Separator (regular expression, split into 2 columns, and do not delete column): \d{2}[.]
• Delete first new column
• Rename second new column
• Separate out remaining text
• Separator (regular expression, split into two columns): [.][ ][A-Za-z]
• Delete second new column

General

Inventory flattening

1. Add column based on second to last column with expression value (column name Temp [name of column])
2. Fill down new column
3. Facet out blanks on last column
4. Transform on second to last column (now third to last) with expression cells['Temp Column'].value + " -- " + cells['Last column'].value
5. Remove last two columns
6. Repeat as necessary

Transformations

Add text to beginning of cell: "Text" + value

Add text to end of cell: value + "Text"

Add columns to end of cells:

1. Filter out null values
2. value + cells['COLUMN NAME'].value
3. Remove column

Move data to different column:

1. Facet for appropriate rows
2. Select column to move to
3. cells['COLUMN TO MOVE FROM'].value

Other

Create null column (using add column based on column): null

Create blank column (using add column based on column): value.replace(value,"")

Create blank row(s):

1. Download GOKb Utilities extension
2. Install (see instructions)
3. All > Edit rows > Prepend rows

Extract (item) extents from mixed title/extent field

Separator (regular expression and only split into 2 columns): [ ](?=[0-9]{1,}+[ ][i][t][e][m])