Text Functions
Intermediate
Capstone: clean up an exported contact list
Trim, re-case and slice, all in one pass.
Task:
This came out of an old HR system with stray spaces and shouting capitals. In column C give the name tidied up and properly capitalised, in column D just the five-digit part of the staff code, and in column E how many characters the tidied name has.
Learning Objectives:
- Nest TRIM inside PROPER
- Slice a fixed-width code with RIGHT
- Use LEN to prove the clean-up worked
Hints
Solve without hints for +5 XP
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 | E | |
|---|---|---|---|---|---|
| 1 | Raw name | Staff code | Clean name | Number | Length |
| 2 | ADA LOVELACE | EMP-00412 | |||
| 3 | bo nilsson | EMP-00875 | |||
| 4 | Cy WATANABE | EMP-01330 |
What this exercise teaches (contains the answer)
Nesting is the point: TRIM hands its result straight to PROPER without a helper column. The LEN column is the check people skip — if it still counts the padding, the trim never happened, and you will find out much later when a join fails to match.