Skip to Main Content

Cleaning Data with OpenRefine: Google Refine Expression Language (GREL)

A guide to using the OpenRefine program to organize messy datasets

Google Refine Expression Language (GREL)

Google Refine Expression Language (GREL) is similar to formulas in Excel. GREL is OpenRefine's way to accomplish complex transformations, queries, and arrangement of data. It can be used in four ways:

  • Creating a custom text or numeric facet
  • Adding a column based on another column
  • Transforming cells in a column using the transformation function
  • Creating a new column by fetching a URL

GREL input windows share a similar layout:

  1. The expression box where you can type in your expression.
  2. The error box to the right of the expression box will display a syntax error message in the case of an error.
  3. The preview box will show a preview the transformation of your data so that you can check the quality before applying the transformation.
  4. The history tab will allow you to select and reuse GREL expressions you have used in previous projects.

 

 

Common GREL Expressions

Google Refine Expression Language

Function
value.replace('string1','string2') replaces 'string1' with 'string2' in the current column
value.replace(' ','') replaces blank spaces with no space
value.replace("''"," ") replaces quotations with blank space (NOTE: double quotations must be used on the outside.)
cells['Column1'].value + cells['Column2'].value concatenates the values in the specified columns
cells['Column1'].value + 'string' adds 'string' to the values in the specified column
value + ' ' + cells['Column2'].value concatenates the values in the current column with the values in the specified column with a space in between
value.splitByCharType[0] returns the first element of a string based on the character type
value. == cells['Column2'].value compares two columns against one another
value.contains('string') checks to see if values in a column contain 'string'
value.startsWith('string') checks to see if values in a column start with 'string'
value.endsWith('string') checks to see if values in a column end with 'string'
diff(date 1, date 2, 'results format') returns the difference between 'date 1' and 'date 2' in the format (months, days, etc.) specified

Further Resources

  • Last Updated: Jul 15, 2025 8:34 AM
  • URL: https://guides.lib.lsu.edu/openrefine
  • Print Page
Subjects: Research Guides