Text FunctionsBeginner

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.

Interactive Spreadsheet

Hints

+5 XPno-hints bonus

Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.

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.

5 rows × 3 columns8 cells you fill in
ABC
1Customer Name (raw)Clean NameLength
2 DEREK morrison
3lucia VARGAS
4 KWAME osei
5natalie BENNETT
What this exercise teachesMay contain 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.