IntermediateText Functions
Pull the year out of an order code with MID
Extract characters from the middle of a text value.
MID
7 min25 points
Skip blanks when joining an address
The situation
Harbor Supply prints shipping labels from four address columns, but most customers have no suite number. The current labels come out with a stray comma floating where the blank field was, and the carrier's system rejects them.
A1
fx
| Row number | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Street | Suite | City | State | Label | |
| 2 | 1420 Harbor Rd | Cleveland | OH | |||
| 3 | 88 Fenwick Ave | Suite 300 | San Antonio | TX | ||
| 4 | 7 Alder Ct | Providence | RI | |||
| 5 | 215 Riverbend Way | Unit 12 | Grand Rapids | MI | ||
| 6 |
Functions in this exercise
- TEXTJOIN — Joins text values with a separator, and can skip the empty ones.
Who this is for
Recommended next
Related exercises
- BeginnerText Functions
Build a mailing address with TEXTJOIN
Combine separate address fields into one line, skipping blanks.
TEXTJOIN
6 min15 points - BeginnerText Functions
Build a product code with CONCAT
Join a category prefix and an item number into a single SKU.
CONCAT
4 min10 points - 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