Skip to main content

University Library, University of Illinois at Urbana-Champaign

OpenRefine

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

Splitting Cell Values

Sometimes you will have columns with lots of pieces of data all bunched together. There are several ways OpenRefine allows you to separate these pieces of data into more useful divisions, either into new columns or into a multi-row record.

Creating a New Row Based on a Separator

NOTE: It is important to reset all facets on the column you wish to work with before beginning this process.

  1. Go to the column you would like to split and click the arrow button on the column header.
  2. Select the “Edit cells” option and choose “Split multi-valued cells.”
  3. In the pop-up window, type in the separator (often a semi-colon, comma, or some other special character), and select “OK.”
  4. Cells with the values indicated as separators will be split into different rows based on those separators.

NOTE: The new rows will still be part of the same record. For more information on rows and records see the Layout section.

Process for splitting multi-valued cells

In OpenRefine 2.8, the pop-up window referred to in step 3 above appears differently (see below) and allows for more options and control when splitting cells. The options are:

  1. “By separator” allows you to input the separator value (often a semi-colon, comma, or other special character).
  2. “By field lengths” allows you to input a string of integers to indicate where in the original column value you would like OpenRefine to split the values. For example:
    1. A column contains dates in the YYYY/MM/DD format. You wish to divide this into three columns (Year, Month, Day). Under “by field lengths,” use the list “4, 1, 2, 1, 2” indicating “year, slash, month, slash, day.” The result will be five columns, two with only slashes which you can remove, and three with the Year, Month, and Day respectively.

Helpful Tips:

  • This method of splitting cell values is most useful for data you wish to remain as one column, such as multiple authors, for future faceting or transformation purposes.
  • There are many characters and strings that can be used as "separators," including: spaces, semi-colons, pipes, slashes, commas, dashes, and full words.

Creating a New Column Based on a Separator or Field Length

  1. Go to the column you would like to split and click the arrow button on the column header.
  2. Select the “Edit column” option and choose “Split into several columns.”
  3. In the pop-up window, select the method by which you would like to split the column:
    1. “By separator” allows you to input the separator value (often a semi-colon, comma, or other special character) as well as to limit the number of additional columns are created.
    2. “By field lengths” allows you to input a string of integers to indicate where in the original column value you would like OpenRefine to split the values. For example:
      1. A column contains dates in the YYYY/MM/DD format. You wish to divide this into three columns (Year, Month, Day). Under “by field lengths,” use the list “4, 1, 2, 1, 2” indicating “year, slash, month, slash, day.” The result will be five columns, two with only slashes which you can remove, and three with the Year, Month, and Day respectively.
  4. When you have selected and identified all your criteria, select “OK.”

Process for making new columns based on other columns
 

Helpful Tips:

  • The new columns will automatically be named after the column they were created from. You can change the column titles by going to the column header, clicking the arrow button, selecting “Edit column,” and then choosing “Rename this column.”
  • This transformation is useful for dividing out data you wish to analyze is separate sections. This may include address information, dates, and names.

8/17/2018 - Brinna Michael