Splitting and Cleaning Messy Data

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.

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.

By default each character in the delimiter string is treated as its own separator. So SPLIT(A2, ",;") splits on commas and semicolons.

The formula below splits on either a comma or a space, handy when separators are inconsistent across rows.

SPLIT has optional arguments. The third controls whether each delimiter character splits separately, and the fourth controls whether empty results are removed.

By default empty pieces are dropped, which can shift your columns. The formula below keeps empties by passing FALSE as the last argument, preserving column alignment for A,,C.

Text pasted from PDFs or web pages often carries invisible control characters that break formulas and lookups.

The CLEAN function removes most non-printable characters from a string. The formula below returns A2 with those gremlins stripped out, so a value that looked equal but never matched finally behaves.

CLEAN handles control characters but not extra spaces. Pair it with TRIM, which removes leading, trailing, and repeated spaces, then split.

The formula below cleans control characters, trims spaces, and splits the result on commas in one expression. Read it inside out.

Column A holds messy entries like Smith , John with stray spaces. You want clean first and last names in separate cells.

The formula below trims and splits on the comma. To also clean each piece, you can wrap individual results in TRIM after splitting, but cleaning first usually does the job.

SPLIT needs a literal delimiter. When separators vary, normalize them with REGEXREPLACE first, then split on the standard one.

The formula below replaces any run of spaces, commas, or semicolons with a single pipe, then splits on the pipe. This handles wildly inconsistent input cleanly.

Sometimes you want only part of a cell, then split that. From Tags: red, blue, green you want just the colors as separate cells.

The formula below first extracts everything after the colon, then splits on the comma. Combining the regex and split tools is where the real power shows.

For most messy imports, this order works well:

• CLEAN to drop control characters
• TRIM to fix spacing
• REGEXREPLACE to standardize delimiters or strip junk
• SPLIT to break into columns

The formula below applies all four steps in sequence to a single cell.

SPLIT spreads pieces across columns by default. Sometimes you want a vertical list instead, one value per row, ready for filtering or counting.

Wrap SPLIT in TRANSPOSE to flip the spilled row into a column. The formula below splits A2 on commas and stacks the results vertically, which is perfect for turning a comma list into a clean lookup column.

Check your understanding of splitting and cleaning.

You finished the course by combining splitting and cleaning tools in Google Sheets:

• SPLIT(text, delimiter) breaks a cell into spilled columns
• Extra arguments control multi-char delimiters and empty pieces
• CLEAN strips invisible control characters; TRIM fixes spaces
• Use REGEXREPLACE to standardize messy delimiters before splitting
• Chain CLEAN, TRIM, REGEXREPLACE, and SPLIT for a repeatable cleanup

You now have a complete regex toolkit: match, extract, replace, split, and clean.