Joining text with TEXTJOIN

1 / 5

Join each address into one line, with a comma and a space between the parts.

keyboard Typing formulas is easiest on a computer with a keyboard.

A1
ABCDEF
1Line 1Line 2TownCountyFull address
235 Station RoadExeterDevon
369 High StreetUnit 19ChesterCheshire
441 Station RoadFlat 11YorkNorth Yorkshire
583 Green LaneFlat 5BathSomerset
693 Park AvenueTruroCornwall
7107 Station RoadLincolnLincolnshire
8
9
10
11
12
parts
one line
Type to enter a value or a formula starting with =. F2 edits, F4 adds $ to a reference.
Study notes

How TEXTJOIN and CONCAT works

  • What does TEXTJOIN do?

    TEXTJOIN joins a range of cells into one piece of text, with a separator between each part:

    =TEXTJOIN(", ",TRUE,A2:D2)

  • What are its three parts?

    • The separator, in double quotes: ", " puts a comma and a space between the parts.
    • TRUE or FALSE: TRUE skips empty cells, FALSE keeps them.
    • The cells to join: a range such as A2:D2, or several cells and ranges separated by commas.

  • Why not just use &?

    =A2&", "&B2&", "&C2 works when every cell is filled in. When one is empty, you get two commas in a row: "12 Mill Road, , York". TEXTJOIN with TRUE leaves the gap out. It is also much shorter when there are many cells to join.

  • What about CONCAT?

    CONCAT(A2:D2) joins a range too, but with no separator and no way to skip empty cells. It replaces the older CONCATENATE.

Know someone fighting with a spreadsheet? Send them this page.