Spreadsheet Practice

Lesson 14 of 29, Text cleanup

Joining text

Glue names, labels and lists together.

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.

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.

  1. Full name
  2. Directory format
  3. One-cell list
Try these functions in the Formula Lab