Lesson 19 of 45, Text cleanup
Joining text
Quick answer
Glue names, labels and lists together.
- Key formula
=A2&" "&B2(First Last)
Watch it done
3 stepsWatch the technique worked out on sample data, then try it yourself in the exercises.
How it works
The ampersand & joins text: =A2&" "&B2. Put spaces and punctuation inside quotes.
CONCAT joins a whole range with nothing in between. TEXTJOIN(delimiter, ignore_empty, range) adds a separator between items and can skip blank cells.
Syntax
=A2&" "&B2First Last=B2&", "&A2Last, First=TEXTJOIN(", ",TRUE,C2:C6)A comma-separated listWatch out for
- Forget the quoted space and you get "MayaChen".
- TEXTJOIN with FALSE keeps blanks, giving doubled commas.
The exercises
- Full name. In D2, join first and last name with a space. Fill down to D6.
- Directory format. In E2, build "Last, First" (for example, Chen, Maya). Fill down to E6.
- One-cell list. In G2, list every city from C2:C6 in one cell, separated by a comma and a space, skipping blanks.
Questions people ask about this
Updated Checked by running: every exercise answer on this page was graded by our formula engine on .
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 43 Pro lessons, Excel and Google Sheets, 125 exercises in all.
- Full name
- Directory format
- One-cell list