Clean and Combine Names with TRIM and PROPER
Your task: In C2, write a formula that gives each customer as Last, First with tidy capitals, then fill it down.
Full instructions, scenario and goal
Scenario
A CRM export arrived with the first and last names in separate columns, typed by different people. Some have extra spaces and the capital letters are all over the place. For the printed mailing list you need each customer as Last, First, correctly spelt in capital and small letters, and with no extra spaces.
Your task
- Look at the raw first names in column A and last names in column B.
- In C2, write a formula that gives the customer as Last, First: the last name, a comma and one space, and the first name, each cleaned up.
- Copy C2 and paste it over C3:C7.
- Select Check Answer.
You are done when
- C2:C7 contain formulas that read the names in columns A and B.
- Each name has a capital first letter, small letters after it, and no extra spaces.
- The result looks like Lopez, Maria for every row.
What you will practise
Clean imported text with TRIM and PROPER and combine pieces of text into one result.
The idea in 30 seconds
Data exported from other systems is often untidy: spaces at the start or end, doubled spaces in the middle, and capital letters in the wrong places. TRIM removes leading and trailing spaces and reduces runs of spaces inside the text to a single space. PROPER capitalises the first letter of each word and lowercases the rest. UPPER and LOWER change every letter.
Spreadsheet
Loading spreadsheet…
Keyboard shortcuts
- Arrow keys
- Move between cells
- Shift + Arrow keys, or drag with the mouse
- Select a range
- Ctrl + Arrow keys
- Jump to the edge of a block of data
- Type, or F2, or double-click
- Edit a cell (F2 switches arrow keys between moving and editing)
- Click or arrow keys inside a formula
- Insert a cell reference after =, +, (, comma and similar
- F4
- Cycle a reference through A1, $A$1, A$1, $A1
- Enter / Tab while editing
- Confirm and move down / right (Shift reverses)
- Esc
- Cancel the edit
- Ctrl + C / X / V
- Copy, cut and paste (formulas adjust like in Excel)
- Delete
- Clear the selected cells
- Ctrl + Z / Ctrl + Y
- Undo and redo
- Ctrl + Enter
- Check your answer
- Tab when not editing
- Leave the spreadsheet
Type your answer in cell C2. Click a cell and type. Enter confirms, Esc cancels, Ctrl+Enter checks your answer.Tap a highlighted cell to type in it, then tap the green tick to confirm. Scroll the sheet sideways if it is wider than your screen.
New here? How an exercise works
- Read the task. Highlighted cells are yours to fill in; the rest are locked.
- Select (or tap) a highlighted cell and type a formula. It starts with =, like =A1+B1.
- Press Check answer. Stuck? Reveal hints one at a time.
The full explanation: the idea behind this exercise
Data exported from other systems is often untidy: spaces at the start or end, doubled spaces in the middle, and capital letters in the wrong places. TRIM removes leading and trailing spaces and reduces runs of spaces inside the text to a single space. PROPER capitalises the first letter of each word and lowercases the rest. UPPER and LOWER change every letter.
To join pieces of text, put the & operator between them: it glues the text on its left to the text on its right. Any text you add yourself, such as a comma and a space, goes in double quotation marks. Functions can be nested, so one formula can clean each piece and then join the results. The cleaned value is a new text: the original cell is not changed.
Skills you will practise
- TRIM
- PROPER
- UPPER
- LOWER
- CONCATENATE
- &
Topic guide: Excel Text Functions Practice: TRIM, LEFT, TEXT and More
Common mistakes to avoid
- Only fixing the capital letters
- PROPER does not remove spaces, so a name that starts with a space keeps it. Clean the spaces with TRIM as well.
- Forgetting the quotation marks
- A comma and a space typed outside quotation marks are not text, so Excel rejects the formula.