TRIM and CLEAN in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are a data analyst at an e-commerce company.

📋 Request: Your manager wants you to clean up the customer feedback data to remove extra spaces and hidden characters before analysis.

📊 Data: You have a list of customer feedback comments in a column. Some comments have extra spaces at the start or end, and some contain hidden non-printable characters that cause issues in reports.

🎯 Deliverable: Produce a cleaned version of the feedback comments with no extra spaces or hidden characters.

Sample Data

Customer ID	Feedback
101	Great product!
102	Good valuefor money
103	Fast delivery and service
104	Excellent quality
105	Will buy again
106	Not satisfied with packaging
107	Friendly support
108	Average experience

1

Step 1: Insert a new column next to the Feedback column to hold cleaned feedback.

No formula needed for this step.

Expected Result

A new empty column appears next to Feedback.

2

Step 2: In the first cell of the new column, enter a formula to remove non-printable characters using CLEAN.

=CLEAN(B2)

Expected Result

The formula removes hidden characters from the feedback in B2.

3

Step 3: Wrap the CLEAN formula with TRIM to also remove extra spaces before and after the text.

=TRIM(CLEAN(B2))

Expected Result

The formula removes hidden characters and extra spaces from the feedback in B2.

4

Step 4: Copy the formula down the entire new column to clean all feedback entries.

Drag the fill handle from the first formula cell down to the last feedback row.

Expected Result

All feedback comments are cleaned with no extra spaces or hidden characters.

5

Step 5: Optionally, copy the cleaned feedback column and paste as values to replace original data or use for analysis.

Select cleaned column, Copy, then Paste Special > Values over original feedback or another location.

Expected Result

Cleaned feedback text is now static and ready for reporting.

Final Result

Customer ID | Cleaned Feedback
------------|-----------------
101         | Great product!
102         | Good valuefor money
103         | Fast delivery and service
104         | Excellent quality
105         | Will buy again
106         | Not satisfied with packaging
107         | Friendly support
108         | Average experience

✓Extra spaces before and after feedback are removed.

✓Hidden non-printable characters causing issues are cleaned.

✓Feedback text is now consistent and ready for accurate analysis.

Bonus Challenge

Create a formula that counts how many cleaned feedback comments contain the word 'good' (case-insensitive).

Show Hint

Use COUNTIF with SEARCH inside an array formula or use COUNTIFS with wildcards and LOWER function.