How to clean up imported data
Data from a bank or another system arrives untidy. Fix stray spaces, capitals, numbers stored as text and repeated rows.
Task 1Spaces and capitals4 questions
Data copied from a bank, a till or another system often arrives untidy. Three functions tidy most text.
- TRIM(text)
- removes the spaces before and after, and leaves one space between words
- PROPER(text)
- gives every word a capital first letter and makes the rest small
- UPPER(text) and LOWER(text)
- all capitals, or all small letters
| A | B | |
|---|---|---|
| 1 | Customer | LEN |
| 2 | HOLLIN SCHOOL | 13 |
| 3 | moss lane vets | 14 |
| 4 | Quay Dental | 13 |
LEN counts characters, spaces included. Quay Dental has 11 characters but LEN says 13, so 2 spaces are hiding after the name.
Task 2Numbers stored as text3 questions
Task 3Swapping characters and removing repeats2 questions