Spreadsheet Practice

Lesson 19 of 45, Text cleanup

Joining text

Quick answer

Glue names, labels and lists together.

Key formula
=A2&" "&B2 (First Last)
Pro7 minutes, 3 exercises&CONCATTEXTJOIN

Watch it done

3 steps

Watch 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 list

Watch out for

  • Forget the quoted space and you get "MayaChen".
  • TEXTJOIN with FALSE keeps blanks, giving doubled commas.

The exercises

  1. Full name. In D2, join first and last name with a space. Fill down to D6.
  2. Directory format. In E2, build "Last, First" (for example, Chen, Maya). Fill down to E6.
  3. 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.

  1. Full name
  2. Directory format
  3. One-cell list