Introduction
Cleaning data is one of the most important Excel skills every analyst must master. When working with imported datasets, you’ll often face messy text with irregular spaces. This is where the TRIM function in Excel becomes powerful.
In this article, we’ll cover 20 Excel skill practice problems with TRIM function, show step-by-step solutions, and provide a free downloadable Excel file to practice.
What the Excel TRIM Function Does
The TRIM function in Excel removes all extra spaces from text, leaving only single spaces between words.
Formula syntax:
=TRIM(text)
text→ The cell containing the text you want to clean.
Example:
If A1 = ” Hello World ” → =TRIM(A1) → “Hello World”
Why Practice TRIM Function Skills?
- Cleans up imported data from CSV, web, or ERP systems.
- Fixes extra spaces before numbers, names, or codes.
- Helps ensure formulas like VLOOKUP, MATCH, and COUNTIF work correctly.
- Saves time by avoiding manual cleaning.
Example Dataset (Messy Data with Spaces)
| ID | Name (Messy) | Department (Messy) | City (Messy) |
|---|---|---|---|
| 1 | John Smith | Finance | New York |
| 2 | Mary Johnson | HR | Los Angeles |
| 3 | Sam Wilson | Sales | San Francisco |
| 4 | Rita Brown | IT | Chicago |
| 5 | Alex Davis | Finance | Boston |
| 6 | Nina Thomas | HR | Houston |
| 7 | Peter White | IT | Miami |
| 8 | Laura Hall | Sales | Dallas |
| 9 | Chris Green | HR | Denver |
| 10 | Julia Adams | Finance | Seattle |
20 Excel TRIM Function Practice Problems with Solutions
Here are 20 real-world problems you can practice to improve your TRIM skills:
Problem 1: Remove leading spaces
Cell A1 = " Excel"
Solution: =TRIM(A1) → Excel
Problem 2: Remove trailing spaces
Cell A1 = "Excel "
Solution: =TRIM(A1) → Excel
Problem 3: Remove multiple spaces between words
Cell A1 = "Excel Skills"
Solution: =TRIM(A1) → Excel Skills
Problem 4: Clean product codes with spaces
A1 = " P123 "
Solution: =TRIM(A1) → P123
Problem 5: Fix imported names
A1 = " John Doe "
Solution: =TRIM(A1) → John Doe
Problem 6: Clean email addresses
A1 = " test @gmail.com "
Solution: =TRIM(A1) → test @gmail.com
Problem 7: Remove spaces before numbers
A1 = " 4500 "
Solution: =TRIM(A1) → 4500
Problem 8: Combine TRIM with VALUE
A1 = " 123 " (text format)
Solution: =VALUE(TRIM(A1)) → 123 (number)
Problem 9: Clean imported CSV data
A1 = " Apple , Mango , Banana "
Solution: =TRIM(A1) → Apple , Mango , Banana
Problem 10: Use TRIM with PROPER case
A1 = " john smith "
Solution: =PROPER(TRIM(A1)) → John Smith
Problem 11: Nested TRIM inside SUBSTITUTE
A1 = " Excel Skill "
Solution: =TRIM(SUBSTITUTE(A1,CHAR(160),"")) → Excel Skill
Problem 12: Clean up data before VLOOKUP
A1 = " P001 "
B1:B10 = Product IDs
Solution: =VLOOKUP(TRIM(A1),B1:C10,2,FALSE)
Problem 13: TRIM with CONCATENATE
A1 = " Excel "
B1 = " Skill "
Solution: =CONCAT(TRIM(A1)," ",TRIM(B1)) → Excel Skill
Problem 14: TRIM with LEN check
A1 = " Data "
Solution: =LEN(A1) → 8,=LEN(TRIM(A1)) → 4
Problem 15: Clean full address
A1 = " 123 Main Street "
Solution: =TRIM(A1) → 123 Main Street
Problem 16: TRIM array formula (Excel 365)
A1:A3 contain messy names.
Solution: =TRIM(A1:A3) → Returns cleaned list.
Problem 17: Remove spaces before hyphen
A1 = " Excel - Skill "
Solution: =TRIM(A1) → Excel – Skill
Problem 18: TRIM with CLEAN for non-printable characters
A1 = " Excel " (contains CHAR(160))
Solution: =TRIM(CLEAN(A1)) → Excel
Problem 19: TRIM with SUBSTITUTE for double spaces
A1 = "Excel Skill Practice "
Solution: =TRIM(SUBSTITUTE(A1," "," ")) → Excel Skill Practice
Problem 20: TRIM + UNIQUE for cleaned lists
A1:A5 contain " Apple ", "Apple", " Banana "
Solution: =UNIQUE(TRIM(A1:A5)) → Clean unique list.
Conclusion
Mastering the Excel TRIM function is a must-have Excel skill for data cleaning. These 20 practice problems with solutions give you real-world scenarios where TRIM helps you fix messy data instantly. Download the free Excel practice file below and sharpen your Excel skills today.
Free Download: Excel TRIM Function Practice Problems
This file contains 20 problems with step-by-step solutions so you can practice cleaning messy data using the Excel TRIM function.
