TRIM and CLEAN in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are a data assistant at a customer service company.

📋 Request: Your manager wants you to clean up a list of customer names that have extra spaces and hidden characters before using them in reports.

📊 Data: You have a list of customer names in a column. Some names have extra spaces before, after, or between words. Some names have hidden non-printable characters that cause errors in reports.

🎯 Deliverable: Create a cleaned list of customer names with no extra spaces and no hidden characters.

Sample Data

Customer Names
Alice Johnson
Bob Smith
Carol Brown
David Lee
Eve Davis
FrankMiller
Grace Wilson
HenryMoore

1

Step 1: Insert a new column next to the customer names to place the cleaned names.

No formula needed.

Expected Result

A new empty column next to the original names.

2

Step 2: In the first cell of the new column, enter a formula to remove hidden characters using CLEAN and remove extra spaces using TRIM.

=TRIM(CLEAN(A2))

Expected Result

The name 'Alice Johnson' appears without extra spaces or hidden characters.

3

Step 3: Copy the formula down the entire column to clean all customer names.

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

Expected Result

All customer names are cleaned with no extra spaces or hidden characters.

4

Step 4: Check a few cleaned names to confirm spaces and hidden characters are removed.

Manually inspect cleaned names or use LEN function to compare lengths.

Expected Result

Names like 'Bob Smith', 'Carol Brown', and 'Frank Miller' show correct spacing and no hidden characters.

Final Result

Customer Names   | Cleaned Names
-----------------|----------------
  Alice Johnson  | Alice Johnson
Bob Smith       | Bob Smith
  Carol Brown   | Carol Brown
David Lee       | David Lee
 Eve Davis      | Eve Davis
Frank Miller    | Frank Miller
  Grace Wilson  | Grace Wilson
Henry Moore     | Henry Moore

✓TRIM removes extra spaces before, after, and between words.

✓CLEAN removes hidden non-printable characters that cause errors.

✓Using both together ensures customer names are neat and ready for reports.

Bonus Challenge

Create a formula that also converts all cleaned names to proper case (first letter uppercase, rest lowercase).

Show Hint

Use the PROPER function combined with TRIM and CLEAN, like =PROPER(TRIM(CLEAN(A2))).