TEXTJOIN: join a row and skip the gaps
One address line from several columns, with no doubled commas where a part is missing.
Task:
The CRM stores addresses in four columns, and not every customer has a second line or a county. Build one comma-separated address in F2:F5, with no empty gaps where a part is missing.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
Variations of CONCAT and &
0/4The same formula in the other shapes it takes at work.
- Text Concatenation
Combine text from multiple cells using concatenation.
CoreBeginner - Join text and numbers into a label
Build a readable line out of a name and a figure.
Beginner - TEXTJOIN: join a row and skip the gaps
One address line from several columns, with no doubled commas where a part is missing.
Intermediate - Initials from two names
Join the first letter of each part rather than the whole thing.
Intermediate
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Customer | Line 1 | Line 2 | Town | County | Address |
| 2 | Bayliss | 4 Mill Lane | Ledbury | Herefordshire | ||
| 3 | Carrow | Unit 9 | Riverside Park | Norwich | ||
| 4 | Dalby | 12 High St | Flat 2 | Thirsk | North Yorkshire | |
| 5 | Elwes | The Old Forge | Ripon |
What this exercise teachesMay contain the answer
Joining with & means writing every piece and every separator yourself, and a missing Line 2 leaves a stray ", ," in the middle. TEXTJOIN puts the separator only between parts that exist, which is the rule you would follow writing the address by hand.