Introduction
The TRIM function in Excel removes extra spaces from text. It removes spaces at the beginning and end of text and reduces multiple spaces between words to a single space. It is useful for cleaning employee records, customer data, product names, and other information before preparing reports.
In this TRIM Function in Excel Tutorial, you will learn how to use TRIM with practical examples, combine it with relevant text functions, and handle common spacing problems in Excel.
Syntax of the TRIM Function
=TRIM(text)
text: The cell reference or text value you want to clean.
Question 1: Remove Extra Spaces from Employee Names
An HR department receives employee names with unnecessary spaces. Use TRIM to clean the names.
Data:
| Original Name | Clean Name |
|---|---|
Amit Sharma | |
Priya Verma | |
Rahul Mehta |
Excel Formula in B2:
=TRIM(A2)
Copy the formula down to B4.
Expected Result:
| Original Name | Clean Name |
|---|---|
Amit Sharma | Amit Sharma |
Priya Verma | Priya Verma |
Rahul Mehta | Rahul Mehta |
What to learn: TRIM removes leading and trailing spaces and reduces repeated spaces between words to one.
Question 2: Clean Department Names Before Preparing a Summary
Department names may contain unnecessary spaces when data is entered manually by different employees.
Data:
| Department | Clean Department |
|---|---|
Sales | |
Human Resources | |
Information Technology | |
Finance |
Excel Formula in B2:
=TRIM(A2)
Copy the formula down.
Expected Result:
| Department | Clean Department |
|---|---|
Sales | Sales |
Human Resources | Human Resources |
Information Technology | Information Technology |
Finance | Finance |
What to learn: Consistent spacing helps maintain clean department names in company reports.
Question 3: Calculate the Difference in Character Count Before and After TRIM
A data analyst wants to identify how many characters are removed when extra spaces are cleaned from product descriptions.
Data:
| Product Description | Original Length | Clean Description | Clean Length |
|---|---|---|---|
Wireless Mouse | |||
USB Keyboard |
Assume the descriptions are in A2.
Formula for Original Length in B2:
=LEN(A2)
Formula for Clean Description in C2:
=TRIM(A2)
Formula for Clean Length in D2:
=LEN(C2)
Copy the formulas down.
Expected Result:
| Product Description | Original Length | Clean Description | Clean Length |
|---|---|---|---|
Wireless Mouse | 19 | Wireless Mouse | 15 |
USB Keyboard | 16 | USB Keyboard | 12 |
What to learn: LEN counts spaces as characters. Comparing the original and cleaned lengths helps you understand how much the text has changed.
Question 4: Clean Text Containing Nonprinting Characters
Imported data can sometimes contain nonprinting characters that make text difficult to clean. CLEAN can remove many of these characters, while TRIM handles extra ordinary spaces.
Assume cell A2 contains a product description with extra spaces and a nonprinting line-break character.
Excel Formula:
=TRIM(CLEAN(A2))
Expected Result:
The formula removes supported nonprinting characters and cleans the extra ordinary spaces. For example, a description that appears on two lines may become a single clean line.
What to learn: CLEAN and TRIM solve different problems. CLEAN removes many nonprinting characters, while TRIM removes extra ordinary spaces.
Important: CLEAN does not remove every unusual Unicode character. If imported text still looks incorrect, identify the specific character and handle it separately.
Question 5: Compare Two Text Values After Cleaning Their Spaces
A company has two records for the same supplier. Before deciding whether the names match, remove extra spaces from both values.
Data:
| Supplier Record A | Supplier Record B | Do They Match? |
|---|---|---|
North Star Ltd | North Star Ltd | |
Bright Solutions | Bright Solutions | |
Green Field Co | Green Fields Co |
Assume the first record is in A2 and the second is in B2.
Excel Formula in C2:
=TRIM(A2)=TRIM(B2)
Copy the formula down.
Expected Result:
| Supplier Record A | Supplier Record B | Do They Match? |
|---|---|---|
North Star Ltd | North Star Ltd | TRUE |
Bright Solutions | Bright Solutions | TRUE |
Green Field Co | Green Fields Co | FALSE |
What to learn: TRIM can help compare text values that differ only because of extra ordinary spaces. It does not correct spelling differences.
Question 6: Clean Product Descriptions Without Removing Spaces Between Words
A product catalogue contains descriptions with inconsistent spacing. The goal is to clean the descriptions while keeping the words separate.
Data:
| Original Description | Clean Description |
|---|---|
Premium Cotton Shirt | |
Lightweight Travel Bag | |
Stainless Steel Bottle |
Excel Formula in B2:
=TRIM(A2)
Copy the formula down.
Expected Result:
| Original Description | Clean Description |
|---|---|
Premium Cotton Shirt | Premium Cotton Shirt |
Lightweight Travel Bag | Lightweight Travel Bag |
Stainless Steel Bottle | Stainless Steel Bottle |
What to learn: TRIM preserves one ordinary space between words. This makes it suitable for cleaning descriptions without joining words together.
Question 7: Apply TRIM Automatically in an Excel Table
A company maintains an employee table that grows as new employees join. Using a calculated column helps clean new records consistently.
Data:
| Employee Code | Employee Name | Clean Name |
|---|---|---|
| EMP301 | Dev Malhotra | |
| EMP302 | Sana Roy | |
| EMP303 | Kabir Jain |
Select the data and press Ctrl + T to convert it into an Excel Table. Ensure the table has headers.
In the first cell of the Clean Name column, enter:
=TRIM([@[Employee Name]])
Excel should fill the formula down the calculated column.
Expected Result:
| Employee Code | Employee Name | Clean Name |
|---|---|---|
| EMP301 | Dev Malhotra | Dev Malhotra |
| EMP302 | Sana Roy | Sana Roy |
| EMP303 | Kabir Jain | Kabir Jain |
What to learn: Structured references make formulas easier to understand, and Excel Tables can automatically extend calculated-column formulas to new rows.
Question 8: Create a Clean Employee Name from Separate Fields
An HR system stores first and last names in separate columns. Some entries contain extra spaces, so the full name needs to be created carefully.
Data:
| First Name | Last Name | Full Name |
|---|---|---|
Dev | Malhotra | |
Sana | Roy | |
Kabir | Jain |
Excel Formula in C2:
=TRIM(A2)&" "&TRIM(B2)
Copy the formula down.
Expected Result:
| First Name | Last Name | Full Name |
|---|---|---|
Dev | Malhotra | Dev Malhotra |
Sana | Roy | Sana Roy |
Kabir | Jain | Kabir Jain |
What to learn: TRIM cleans each name separately, and &" "& joins the two parts with one space.
Student Challenge: Clean a Messy Supplier List
Use the following data to practise the concepts from this tutorial.
| Supplier ID | Supplier Name | Department |
|---|---|---|
| S401 | PRIME OFFICE SUPPLIES | Purchasing |
| S402 | metro computers | IT Procurement |
| S403 | CITY PAPER CO | Office Supplies |
| S404 | northstar stationery | PURCHASING |
Tasks
- Remove extra spaces from supplier names.
- Standardize supplier names using PROPER and TRIM.
- Clean the department names using TRIM.
- Calculate the original and cleaned character counts for the supplier names.
- Compare the original department values with their cleaned versions to see how spacing changes.
Suggested Formulas
Clean supplier name:
=TRIM(B2)
Standardized supplier name:
=PROPER(TRIM(B2))
Clean department:
=TRIM(C2)
Original name length:
=LEN(B2)
Clean name length:
=LEN(TRIM(B2))
Check your results by comparing them with the original data. Your cleaned text should not contain leading or trailing ordinary spaces or repeated ordinary spaces between words.
Key Takeaways
- TRIM in excel removes leading and trailing ordinary spaces.
- It reduces repeated ordinary spaces between words to one.
- Combine TRIM with PROPER to standardize the capitalization of names.
- Combine TRIM with LEN to compare character counts before and after cleaning.
- Use TRIM in excel with SUBSTITUTE to handle nonbreaking spaces represented by
CHAR(160). - Combine TRIM with CLEAN when imported text contains supported nonprinting characters.
- TRIM in excel can help compare text values that differ only because of extra spaces.
- TRIM in excel does not correct spelling mistakes or remove every unusual Unicode whitespace character.
- TRIM in excel returns a cleaned result in a separate cell. To replace the original data, copy the results and use Paste Special → Values.
Frequently Asked Questions (FAQs)
1. What is the TRIM function in Excel?
TRIM removes extra ordinary spaces from text. It keeps a single ordinary space between words and removes unnecessary spaces at the beginning and end.
2. What is the syntax of TRIM?
=TRIM(text)
Replace text with a cell reference such as A2 or a text value.
3. Does TRIM remove spaces between words?
Yes. It reduces multiple consecutive ordinary spaces between words to a single space. It does not remove the remaining space between words.
4. What is the difference between TRIM and CLEAN?
TRIM removes extra ordinary spaces. CLEAN removes many nonprinting characters. You may combine them when cleaning imported text:
=TRIM(CLEAN(A2))
5. Can TRIM be combined with PROPER?
Yes. Use:
=PROPER(TRIM(A2))
This cleans ordinary spaces and capitalizes the first letter of each word.
6. Why does TRIM sometimes fail to remove spaces copied from a website?
The text may contain nonbreaking spaces or other Unicode whitespace characters. Try:
=TRIM(SUBSTITUTE(A2,CHAR(160)," "))
This handles the common nonbreaking space represented by character code 160.
7. Does TRIM change the original cell?
No. A formula such as =TRIM(A2) displays the cleaned text in the formula cell. The original cell remains unchanged until you replace it yourself.
8. Can TRIM clean an entire column automatically?
Yes. Copy the formula down the column, use Excel’s fill handle, or use a calculated column in an Excel Table.
9. Is TRIM available in common Excel versions?
Yes. TRIM is available in widely used Excel versions, including Excel 2016, 2019, 2021, 2024, and Microsoft 365.

