Join an address with TEXTJOIN
PremiumIn E2, join A2:D2 into one address with a comma and a space between the parts. Use TEXTJOIN and skip the empty Suite cell.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Street | Suite | City | ZIP code | Address | |
| 2 | 12 Harbor Road | Portland | OR 97204 | |||
| 3 | ||||||
| 4 | ||||||
| 5 | ||||||
| 6 | ||||||
| 7 | ||||||
| 8 |
Premium lesson
Unlock Joining text with & and TEXTJOIN and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
Why this works
TEXTJOIN puts the same separator between every part of a range. The second argument decides what happens to empty cells: TRUE skips them, so there is no ", ," where the Suite is missing. With & you would have to write each comma yourself and handle the gap by hand.
Common mistake: Using FALSE as the second argument. Empty cells then still get a separator, and the address has two commas in a row.
The answer
- E2
=TEXTJOIN(1", ",2TRUE,3A2:D2)
In plain words: Join 3the address parts in A2:D2 with 1a comma and a space between them, 2skipping the empty ones.