Clean migrated customer names for a CRM import
TRIM strips out the spacing problems a copy-pasted export leaves behind, so nest it inside PROPER and PROPER only ever has to solve capitalization, never spacing too.
Task:
You're migrating customer records out of a 15-year-old CRM into a new cloud system for Bellwood Furniture ahead of the cutover. Years of staff copy-pasting between systems have left the name field a mess — some names are in ALL CAPS, some in lowercase, and most carry extra spaces at the start, the end, or doubled up between words. The new system's name field is capped at 20 characters, so before you load the list you need a properly capitalized, single-spaced version of each name in column B, and each one's character count in column C so you can catch anything too long to fit.
Learning Objectives:
- Strip stray spacing out of imported text with TRIM before doing anything else to it
- Standardize capitalization with PROPER, and know what it does and doesn't touch
- Nest one text function inside another so each one only has to solve its own part of the problem
Solve without hints for +5 XP
Interactive Spreadsheet
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 | |
|---|---|---|---|
| 1 | Customer Name (raw) | Clean Name | Length |
| 2 | DEREK morrison | ||
| 3 | lucia VARGAS | ||
| 4 | KWAME osei | ||
| 5 | natalie BENNETT |
What this exercise teaches (contains the answer)
PROPER has to run on TRIM's result rather than on the raw text, because PROPER only ever changes letters — it has no opinion on spacing. Handed "DEREK morrison" directly, it would hand back "Derek Morrison", capitalization fixed but the doubled space between the names untouched. Nesting TRIM inside PROPER cleans the spacing first, so PROPER only ever sees text that's already down to single spaces. LEN then counts whatever ended up in column B, not column A — counting the raw column would include the very spaces the TRIM step just removed, overstating how many characters the migrated name will actually take.