AdvancedData Cleaning
Find which customers are missing from a list
Use COUNTIF to reconcile two lists and spot what is absent.
COUNTIF · IF
14 min40 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 - AdvancedData Cleaning
Find which customers are missing from a list
Use COUNTIF to reconcile two lists and spot what is absent.
COUNTIF · IF
14 min40 points