Splitting and joining text
8 minutes · 5 steps · free, no sign-up
Data rarely arrives in the shape you need. A full name in one cell has to become a first name and a last name, and sometimes you need to do the opposite and glue pieces together. You will cut text apart with LEFT, MID and FIND, then join it with & and TEXTJOIN.
- Find where a space sits with FIND
- Take the text before and after it with LEFT and MID
- Join text with the & operator
- Join a whole range with TEXTJOIN
Click a cell and start typing. Enter edits, Tab moves right, arrows move, Delete clears.
Quick referenceQuick reference
=FIND(" ", A2) position of the first space
=LEFT(A2, n) the first n characters
=MID(A2, start, count) count characters from start
=B2 & ", " & C2 join with &
=TEXTJOIN(", ", TRUE, B2:B4) join a rangeThe key ideasSummary: Splitting and joining text
The trick for splitting text is to find the divider. FIND(" ", A2) says where the space is. Everything before it is LEFT(A2, FIND(" ",A2)-1), and everything after it is MID(A2, FIND(" ",A2)+1, 50), where the 50 simply means "plenty of characters".
Joining is easier. The & operator glues text together, so add any extra text you like in quotes. TEXTJOIN does the same for a whole range, with a separator you choose, and its second argument (TRUE) skips empty cells.
Watch out forCommon mistakes
- Forgetting the minus one.
LEFT(A2, FIND(" ",A2))includes the space. Take one off to stop just before it. - Names with two spaces. A middle name or a double space breaks this pattern. Clean with TRIM first, and expect to adjust the formula for middle names.
- Typing the join text without quotes. A comma or space between pieces has to sit inside quotes:
", ". - Pasting results as values too early. If you replace the formulas with their values and later change the original names, the split names will not update.
Next stepTry it in the real Google Sheets
Google Sheets also has SPLIT, which cuts text into several cells in one go, and JOIN. They fill several cells from one formula, which is covered at the Adept level. LEFT, MID and FIND work in every spreadsheet.