Challenge Scenario
When people sign up on websites or fill out forms, their names often look messy. Some people type in ALL CAPS ("SARAH CONNOR"), some type in all lowercase ("clark kent"), and some accidentally add extra spaces before, between, or after their names (like " jOhN dOE " in Column A).
Sending customer emails with names like "Hello jOhN dOE !" looks unprofessional. You need a simple, automatic Excel formula that cleans up extra spaces and makes the first letter of each name properly capitalized.
As a Customer Data Assistant, your task is to clean this contact list:
- Cleaned Name (Column B): Remove extra spaces and fix capitalization so names look like
"John Doe" using PROPER and TRIM.
- Clean Length (Column C): Count how many characters are in the cleaned name using
LEN.
- Audit Flag (Column D): Check if the name has more than 5 characters. Output
"OK" if valid, or "REVIEW" if it looks too short using IF.
- Total Cleaned Count (Cell B8): Count total cleaned customer rows using
COUNTA.
Your Sheet Layout
Understanding how your sheet is structured makes writing formulas easy. Here is how data moves across this worksheet:
This worksheet is split into three simple zones:
- Source Data (Column A): The messy raw input names. Leave these cells as they are so you can reference them.
- Cleaned Output & Check (Columns B, C, D): Where you apply cleaning formulas, measure name length, and verify name quality.
- Summary Total (Row 8): Where you count the total number of cleaned records at the bottom.
| Cell Range |
Column Name |
Section |
Formula / Approach |
What It Does |
A2:A5 |
Raw Name |
Source Inputs |
Original Text |
Messy names with extra spaces and irregular capital letters. |
B2:B5 |
Cleaned Name |
Text Cleaning |
=PROPER(TRIM(A2)) |
Remove all extra spaces and capitalize the first letter of each word. |
C2:C5 |
Clean Length |
Character Count |
=LEN(B2) |
Count total letters and spaces in the cleaned name. |
D2:D5 |
Audit Flag |
Quality Check |
=IF(C2>5, "OK", "REVIEW") |
Output "OK" if length is greater than 5, otherwise "REVIEW". |
B8 |
Summary: Cleaned Records |
Total Count |
=COUNTA(B2:B5) |
Count the total number of cleaned customer rows. |
Each customer name is cleaned on its own row, and the summary total at the bottom confirms all rows are finished.
Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.
1
In Column B (B2:B5), clean and standardize raw names using PROPER and TRIM.
2
In Column C (C2:C5), calculate the character length of Cleaned Name using LEN.
3
In Column D (D2:D5), output "OK" if Clean Length is greater than 5, otherwise "REVIEW".
4
In cell B8, count total cleaned records using COUNTA.
Solution & Formula Breakdown
Here is the simple, step-by-step explanation for each cleaning formula used in this challenge:
1. Cleaning Names with PROPER and TRIM
We combine two helpful Excel text functions together into one single formula:
=PROPER(TRIM(A2))
How they work together:
-
TRIM(A2) does the first step: It deletes all unwanted spaces at the beginning, at the end, and reduces double spaces between words down to a single space.
Example: " jOhN dOE " → becomes "jOhN dOE".
-
PROPER(...) does the second step: It turns the first letter of each word into UPPERCASE, and turns all other letters into lowercase (Title Case).
Example: "jOhN dOE" → becomes "John Doe"!
2. Measuring Character Length (Column C)
The LEN(text) function simply counts how many characters (including letters and single spaces) are inside the cell:
=LEN(B2)
For "John Doe" (4 letters + 1 space + 3 letters), it returns 8.
3. Quality Checking with IF (Column D)
We want to make sure the name isn't blank or too short (at least 6 characters long):
=IF(C2 > 5, "OK", "REVIEW")
If the clean length in cell C2 is greater than 5, Excel shows "OK". Otherwise, it alerts us with "REVIEW".
4. Counting Cleaned Records (Cell B8)
=COUNTA(B2:B5)
This counts all cells in the range that are not empty, giving us a total of 4 cleaned records.
Quick Excel Casing Reference:
PROPER("john doe") → "John Doe" (Capitalizes First Letter of Each Word)UPPER("john doe") → "JOHN DOE" (ALL CAPS)LOWER("JOHN DOE") → "john doe" (all lowercase)