Splitting and Cleaning Messy Data
Use SPLIT and CLEAN to break apart and tidy imported text.
Breaking One Cell Into Many
Imported data is rarely tidy. A single cell might hold John,Smith,Sales when you really want three separate columns.
The SPLIT function in Google Sheets breaks a string apart at a delimiter and spills the pieces across adjacent cells.
In this final lesson you will pair SPLIT and CLEAN with the regex skills you just learned to fully tidy messy text.
The SPLIT Syntax
SPLIT(text, delimiter) divides text wherever it finds the delimiter and outputs each piece into its own cell to the right.
The formula below splits A2 on commas. John,Smith,Sales spills into three cells: John, Smith, and Sales.
=SPLIT(A2, ",")All lessons in this course
- Testing Patterns With REGEXMATCH
- Pulling Out Patterns With REGEXEXTRACT
- Replacing Patterns With REGEXREPLACE
- Splitting and Cleaning Messy Data