# SPLIT, JOIN and text cleanup (Google Sheets lesson 10 of 17)

> Tidy messy imported text, break one column into several, and join values back into one cell.

Source: https://spreadsheetpractice.com/google-sheets/learn/split-join-text · Updated October 29, 2026

- **Course:** [Google Sheets course](https://spreadsheetpractice.com/google-sheets), track "Sheets power tools"
- **Time:** about 11 minutes, 3 graded exercises
- **Functions:** `SPLIT`, `JOIN`, `TEXTJOIN`, `TRIM`, `PROPER`, `LEFT`, `RIGHT`, `LEN`
- **Access:** Pro (the lesson text is free; the graded exercises need Pro)
- **Key formula:** `=PROPER(TRIM(A2))`: Fix spacing and capitals

## How it works

Imported names often arrive with stray spaces and odd capitals. `TRIM` removes spaces at the start and end and shrinks runs of spaces to one. `PROPER` capitalizes the first letter of each word. Together, `=PROPER(TRIM(A2))` turns "  maria   LOPEZ" into "Maria Lopez".

`SPLIT` breaks text at a delimiter and puts each piece in its own cell to the right: `=SPLIT(B2, ",")`. Watch the third argument, split_by_each. It is TRUE unless you say otherwise, which means every character in the delimiter counts on its own. `SPLIT("San Antonio, TX", ", ")` splits at the comma and at every space, giving three pieces. Pass FALSE to split only where the whole ", " appears: `=SPLIT(B2, ", ", FALSE)`.

`JOIN` goes the other way and puts a delimiter between values: `=JOIN(", ", E2:E6)`. `TEXTJOIN` does the same, with a second argument that skips empty cells when it is TRUE. JOIN has no such switch. Both accept the result of another function, so `=TEXTJOIN(", ", TRUE, UNIQUE(E2:E6))` lists each value once.

For fixed-width pieces, `LEFT` and `RIGHT` take characters from either end, and `LEN` counts them. `=LEFT(A2, 3)` returns the first three characters.

## Syntax

| Formula | What it does |
|---|---|
| `=PROPER(TRIM(A2))` | Fix spacing and capitals |
| `=SPLIT(B2,",")` | One piece per cell, split at each comma |
| `=SPLIT(B2,", ",FALSE)` | Split only where the whole ", " appears |
| `=JOIN(", ",E2:E6)` | Values in one cell, with ", " between them |
| `=TEXTJOIN(", ",TRUE,E2:E6)` | The same, skipping empty cells |
| `=LEFT(A2,3)` | The first three characters |

## Watch out for

- SPLIT's results spill to the right. Leave those cells empty, or the formula shows an error.
- With the default split_by_each, a delimiter of ", " also splits at every space. Use FALSE when the delimiter is more than one character.
- SPLIT works on one cell at a time here. Write it in the first row and fill down.
- Data, then Split text to columns does a one-time split. SPLIT keeps working when the source text changes.

## The exercises

Solve them in the live grid at [SPLIT, JOIN and text cleanup](https://spreadsheetpractice.com/google-sheets/learn/split-join-text). Each answer is graded, then re-checked on changed data so typed-in numbers don't pass.

1. **Clean the names.** In C2, clean the name in A2: remove the extra spaces and give each word a capital first letter. Fill down to C6.
2. **City and state in separate columns.** In D2, split the text in B2 into the city and the state, so the city lands in D2 and the state in E2. Split only where the whole ", " appears, so city names with spaces stay in one piece. Fill down to D6.
3. **Join them back up.** The states are in E2:E6. In H2, list all five states in one cell, separated by a comma and a space. In H3, list each state only once, in the same format.

Previous: [ARRAYFORMULA: one formula for the whole column](https://spreadsheetpractice.com/google-sheets/learn/arrayformula) · Next: [REGEXMATCH, REGEXEXTRACT and REGEXREPLACE](https://spreadsheetpractice.com/google-sheets/learn/regexmatch-regexextract-regexreplace)

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
