Split shipping addresses into city and state
TEXTSPLIT breaks text apart wherever a delimiter you name appears, so a multi-word city passes straight through untouched.
Task:
You handle order fulfillment for an online retailer. This week's shipping addresses came in from a legacy import as a single "City, State" string, but the label printer needs city and state in separate columns. In C2, split the address in B2 into City and State using TEXTSPLIT, then copy down through row 5.
Learning Objectives:
- Split text into an unknown number of pieces with a chosen delimiter
- Choose a delimiter that matches the data exactly rather than guessing a fixed position
- Read a formula's spill into a neighboring cell without typing anything there
Solve without hints for +5 XP
Interactive Spreadsheet
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 | |
|---|---|---|---|---|
| 1 | Order ID | Ship-To Address | City | State |
| 2 | ORD-5001 | Cedar Rapids, IA | ||
| 3 | ORD-5002 | Round Rock, TX | ||
| 4 | ORD-5003 | Boise, ID | ||
| 5 | ORD-5004 | Fort Collins, CO |
What this exercise teaches (contains the answer)
TEXTSPLIT(B2,", ") looks for the two characters comma-space, which occur exactly once in each address, and breaks the text there — so a multi-word city like "Round Rock" or "Cedar Rapids" passes straight through untouched, because there is no comma-space inside it to trip the split. Splitting on a bare comma would have left " TX" with a leading space stuck to the state; splitting on a space instead of a comma would have cut "Round Rock" into two pieces it should never lose. The result spills across into D on its own — there is no formula to type there.