AdvancedData Cleaning
Reconcile a list with hidden spaces
Match two lists where one side has trailing spaces you cannot see.
COUNTIF · IF
12 min35 points
Standardise messy codes with TRIM and UPPER
The situation
A supplier feed arrived with product codes in mixed case and padded with stray spaces. Lookups against the product master are failing because of it.
A1
fx
| Row number | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Raw code | Clean code | |||
| 2 | hsc-04182 | ||||
| 3 | HSC-11904 | ||||
| 4 | hsc-77213 | ||||
| 5 | |||||
| 6 |
Who this is for
Recommended next
Related exercises
- BeginnerText Functions
Check code length with LEN
Use LEN to validate that product codes are the right length.
LEN
3 min10 points - 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 - IntermediateData Cleaning
Spot duplicate order numbers with COUNTIF
Find rows whose order number appears more than once in the column.
COUNTIF · IF
9 min30 points