Google Sheets lesson 7 of 11, Sheets power tools
SPLIT, JOIN and text cleanup
Tidy messy imported text, break one column into several, and join values back into one cell.
Watch it done
5 stepsWatch the technique worked out on sample data, then try it yourself in the exercises.
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
=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 charactersWatch 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
- 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.
- 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.
- 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.
The practice grid runs in Sheets mode: FILTER, SORT, QUERY, ARRAYFORMULA, SPLIT and the REGEX functions behave as they do in Google Sheets, and empty results stay blank.
Graded practice for this lesson is part of Pro
The walkthrough and notes are open to everyone. Pro unlocks the exercises here and in the other 27 Pro lessons, Excel and Google Sheets, 79 exercises in all.
- Clean the names
- City and state in separate columns
- Join them back up