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.
Your first month for $9.50 $19
50% off your first month when you join today.
Then $19/month. Cancel any time, and get a full refund within 14 days if it isn't for you.
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.