What You Will Learn
Your address data is split across three columns: building/floor (A), street name (B), and city (C). You need one combined mailing label in column D that reads like "Suite 100, Market St, San Francisco".
Three address pieces, one output, same separator every time. TEXTJOIN is the cleanest tool for this.
=TEXTJOIN(", ", TRUE(), A2:C2)
Fill this down from D2 to D6. Here is what each argument does:
- ", " — the delimiter. A comma followed by a space goes between each piece.
- TRUE() — tells TEXTJOIN to skip empty cells. If one address part is missing, you do not get a double comma.
- A2:C2 — the range to join. TEXTJOIN reads left to right and inserts the delimiter between each cell.
The result for row 2 is "Suite 100, Market St, San Francisco". Same pattern for every row.
TEXTJOIN combines three columns into one mailing label with consistent delimiters.
The audit formulas:
- B9:
=COUNTA(A2:A6) — counts source records.
- B10:
=COUNTA(D2:D6) — counts merged labels. Should match B9 if every row was processed.
Alternative: Ampersand Operator
If you do not have TEXTJOIN, the ampersand operator can build the same result manually:
=A2 & ", " & B2 & ", " & C2
This gives full control over each separator, but it gets long when joining many columns. TEXTJOIN is shorter and easier to read. One downside of the ampersand approach: if a cell is empty, you still get ", , " in the output. TEXTJOIN with TRUE() skips blanks automatically, which is why it is the better choice for address data where some rows might have missing parts.
If your address pieces contain extra spaces (like "Market St " with a trailing space), wrap each column in TRIM before joining: =TEXTJOIN(", ", TRUE(), TRIM(A2), TRIM(B2), TRIM(C2)). The extra spaces will not show up in the final mailing label.
Ampersand works but gets repetitive with many columns.
The delimiter matters. If you use ", " consistently, every label reads the same way. A missing space or comma makes the output look sloppy even when the data is correct.
Use TEXTJOIN in D2:D6 to combine building, street, and city into one mailing label per row. Then use COUNTA in B9 for source records and B10 for merged labels.