Skip to Main Content

University Library, University of Illinois at Urbana-Champaign

Site Navigation Menu

OpenRefine

A free, open source, powerful tool for working with messy data.

Common Transformations

OpenRefine features several functions for performing commonly needed transformations to your data. Many of these transformations are intended for data cleaning purposes and can also be done by using regular expressions.

To locate these transformations:

  1. Go to the column you would like to make edits to and click the arrow button on the column header.
  2. Select the “Edit cells” and then “Common transforms” options.

  1. Depending upon your needs, select the relevant transformation from the options listed. For quick reference, consult the list of transformations and their function below.
Transformation Function
Trim leading and trailing whitespace Removes extra spaces before and after the value in each cell in the selected column.
Collapse consecutive whitespace Removes extra spaces between individual strings within a cell value in the selected column.
Unescape HTML entities Removes HTML strings indicating particular characters (e.g., “&” vs “&”).
Replace Smart quotes with ascii Replaces Smart quotes to their ASCII (e.g. ' and ")
To titlecase Changes the first letter of all words within a cell value to an uppercase letter.
To uppercase Changes all letters of all words within a cell value to uppercase letters.
To lowercase Changes all letters of all words within a cell value to lowercase letters.
To number Changes the cell format to number, for more detail, see Changing the Cell Format.
To date Changes the cell format to date, for more detail, see Changing the Cell Format.
To text Changes the cell format to text, for more detail, see Changing the Cell Format.
To null Changes the cell format to null.
To empty string Changes the cell format to empty string.

NOTE: All these transformations are logged in your tracked history. To undo a transformation, follow the instructions found in History and Undo/Redo.

For more on the various regular expressions that can be used to achieve these same transformations, see the resources under About Regular Expressions.

 

5/2/2018 - Brinna Michael