Practice by topic / Excel / Text Functions
Imported data is messy: extra spaces, inconsistent capitals, codes glued together. Text functions clean and reshape it without retyping anything. These 10 exercises build up step by step, starting with clean up a messy name entry, combine trim and proper for clean formatting, trim whitespace from a pasted value.
3 of 10 free · no sign-up needed · graded on the formula you write
A1 holds a name with extra leading/trailing spaces and doubled internal spaces. Write a formula in A2 that removes the extra spacing using TRIM.
| A | |
|---|---|
| 1 | John Smith |
| 2 | ? |
Use =TRIM(A1).
Right — TRIM removes leading/trailing spaces and collapses internal double spaces to single spaces.
Expected result in A2: John Smith
A1 holds a messy name with extra spaces and inconsistent case. Write a formula in A2 that trims and proper-cases it in one formula.
| A | |
|---|---|
| 1 | jane DOE |
| 2 | ? |
Use =PROPER(TRIM(A1)).
Good — wrapping TRIM inside PROPER fixes both the spacing and the capitalization in one step.
Expected result in A2: Jane Doe
A1 holds a numeric-looking value pasted with extra spaces around it, stored as text. Write a formula in A2 that trims it using TRIM.
| A | |
|---|---|
| 1 | 42 |
| 2 | ? |
Use =TRIM(A1).
Right — TRIM is the standard first step when cleaning pasted data with stray whitespace.
Expected result in A2: 42
A1 holds an address with extra spaces throughout. Write a formula in A2 that removes the extra spacing using TRIM.
| A | |
|---|---|
| 1 | 123 Main St |
| 2 | ? |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A1 holds text imported from another system that contains a stray control character. Write a formula in A2 that returns the length after cleaning it with CLEAN, to confirm the control character was removed.
| A | |
|---|---|
| 1 | Report\x01Draft |
| 2 | ? |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A1 holds a company name with leading and trailing spaces. Write a formula in A2 that trims it using TRIM.
| A | |
|---|---|
| 1 | Acme Corp |
| 2 | ? |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A1 holds a product code with extra spaces and lowercase letters. Write a formula in A2 that trims and uppercases it in one formula.
| A | |
|---|---|
| 1 | sku001 |
| 2 | ? |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A1 holds a survey text response with extra leading and trailing spaces. Write a formula in A2 that trims it using TRIM.
| A | |
|---|---|
| 1 | Great service! |
| 2 | ? |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A1 holds a city name with extra spaces and inconsistent case. Write a formula in A2 that trims and proper-cases it in one formula.
| A | |
|---|---|
| 1 | NEW york |
| 2 | ? |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A1 holds a tag entered with trailing spaces. Write a formula in A2 that trims it using TRIM before it gets stored elsewhere.
| A | |
|---|---|
| 1 | urgent |
| 2 | ? |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →