IntermediateText Functions
Skip blanks when joining an address
Join address parts into one line without leaving gaps where a field is empty.
TEXTJOIN
7 min25 points
Build a mailing address with TEXTJOIN
The situation
Harbor Supply's shipping labels need a single address line, but the database stores street, suite, city and state separately — and not every customer has a suite number.
A1
fx
| Row number | A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|---|
| 1 | Customer | Street | Suite | City | State | Mailing line | |
| 2 | Ridgeway Hardware | 418 Mill Street | Akron | OH | |||
| 3 | Coastal Plumbing | 77 Harbor Way | Suite 210 | Tampa | FL | ||
| 4 | Fairmont Builders | 1290 Cedar Ave | Boise | ID | |||
| 5 | |||||||
| 6 |
Functions in this exercise
- TEXTJOIN — Joins text values with a separator, and can skip the empty ones. Syntax and example
Who this is for
Recommended next
Related exercises
- BeginnerText Functions
Split a full name with LEFT and FIND
Pull the first name out of a single full-name column.
LEFT · FIND
7 min15 points - IntermediateData Cleaning
Standardise messy codes with TRIM and UPPER
Clean inconsistent spacing and casing so values can be matched.
7 min25 points - IntermediateText Functions
Skip blanks when joining an address
Join address parts into one line without leaving gaps where a field is empty.
TEXTJOIN
7 min25 points