AdvancedText Functions
Extract a lot number from a messy code
Pull a fixed-length value out of the middle of a code that varies in shape.
MID · FIND
12 min35 points
Split a city away from its state
The situation
Harbor Supply's shipping export writes destinations as one field — Cleveland, OH — and the new carrier portal wants city and state in separate columns. City names vary in length, so counting characters will not survive the second row.
A1
fx
| Row number | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Location | City | |||
| 2 | Cleveland, OH | ||||
| 3 | San Antonio, TX | ||||
| 4 | Providence, RI | ||||
| 5 | Grand Rapids, MI | ||||
| 6 | Charleston, SC | ||||
| 7 |
Functions in this exercise
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 - BeginnerText Functions
Pull a state code out of a territory with RIGHT
Take the trailing two-letter state abbreviation off the end of a territory code.
RIGHT
4 min10 points - IntermediateLogical Functions
Turn a product code into a category
Read a code's prefix and translate it into a readable category name.
IFS · LEFT
9 min30 points