About this exercise
A 120-row dataset on one sheet, one answer cell, about 6 minutes. Graded in your browser: the value and the formula behind it, with feedback that names the fix.
How to solve it
- C2
=PROPER(TRIM(B2))
TRIM removes all spaces from a text except single spaces between words: leading and trailing spaces disappear and "JEAN-LUC···JACKSON" becomes "JEAN-LUC·JACKSON". PROPER then capitalises the first letter of every word and makes all other letters lower case, so lower-case, ALL-CAPS and randomly cased entries all end up as "First Last". Nesting them — PROPER(TRIM(B2)) — applies both fixes in one cell, and the check is strict about case: "jean-luc jackson" would not count.
Pitfall 1: PROPER alone leaves the doubled inner spaces, and the grader compares the text exactly — 51 of the 120 rows would fail; TRIM alone leaves the wrong case. Both are needed. Pitfall 2: PROPER capitalises after every non-letter, so "o'neil" becomes O'Neil (fine) but "mcdonald" becomes Mcdonald and "iPhone" becomes Iphone — review names with internal capitals by hand or fix them with SUBSTITUTE. Pitfall 3: TRIM only removes the normal space, CHAR(32). Web forms and copied web pages often contain the non-breaking space CHAR(160), which TRIM ignores in Excel; use TRIM(SUBSTITUTE(B2,CHAR(160)," ")) for those, and CLEAN for line breaks and other non-printing characters. Flash Fill (Ctrl+E) or Text to Columns can fix one export quickly but leave static values; the formula keeps cleaning new rows. To compare cleaned names with another list safely, also apply LOWER or use EXACT only after both sides are cleaned the same way.
Other ways that work
- =TRIM(PROPER(B2)) — same result, functions nested the other way round
- =PROPER(TRIM(SUBSTITUTE(B2,CHAR(160)," "))) — also handles non-breaking spaces from web forms
- =PROPER(TRIM(CLEAN(B2))) — additionally strips line breaks and other non-printing characters
Common mistakes
Using PROPER without TRIM: =PROPER(B2) returns "··Jean-Luc··Jackson·" (· marks a space) — capitalised, but with the padding and the doubled space still there.
How we grade
Any formula that produces the right result is accepted, unless the task names a function: then another function earns half credit. We compare values with a small tolerance and ignore case and spaces in text. A correct number typed by hand instead of a formula earns half credit. Multi-cell tasks earn full credit at 100% of cells, half credit from 50%. Where it matters, we also change an input cell on a copy of your sheet and check that your formula follows.
Answers computed with Excel 365 rules and checked in our grading engine on November 16, 2026. Author: GridBelt team.