TRIM and CLEAN in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the output of this formula?

Given cell A1 contains the text: " Hello World " (with extra spaces, a non-breaking space, and a bell character), what will be the result of =TRIM(CLEAN(A1))?

A "Hello World"

B "Hello World"

C " Hello World "

D "Hello World"

💡 Hint

Remember CLEAN removes non-printable characters and TRIM removes extra spaces.

The CLEAN function removes non-printable characters like the bell character. TRIM removes extra spaces including non-breaking spaces (CHAR(160)) in Google Sheets. So the result is "Hello World" with a single space.

❓ Function Choice

intermediate

Which formula removes only extra spaces but keeps non-printable characters?

You want to remove extra spaces from text in cell B2 but keep any non-printable characters intact. Which formula should you use?

A =TRIM(B2)

B =CLEAN(B2)

C =TRIM(CLEAN(B2))

D =SUBSTITUTE(B2, CHAR(160), "")

💡 Hint

TRIM removes extra spaces, CLEAN removes non-printable characters.

TRIM removes extra spaces but does not remove non-printable characters. CLEAN removes non-printable characters. Since you want to keep non-printable characters, use TRIM only.

🎯 Scenario

advanced

Cleaning imported data with hidden characters

You imported data into Google Sheets and notice some cells have invisible characters causing errors in formulas. Which formula combination best cleans the text by removing both extra spaces and hidden non-printable characters?

A =CLEAN(TRIM(A2))

B =TRIM(CLEAN(A2))

C =SUBSTITUTE(TRIM(A2), CHAR(10), "")

D =TRIM(SUBSTITUTE(A2, CHAR(160), " "))

💡 Hint

Think about the order: CLEAN first or TRIM first?

CLEAN removes non-printable characters first, then TRIM removes extra spaces. Doing TRIM first may leave non-printable characters that affect spacing.

📊 Formula Result

advanced

What is the output of this formula with CHAR(160)?

Cell C3 contains the text: "Data with non-breaking spaces". What is the output of =TRIM(CLEAN(C3))?

A "Data with non-breaking spaces"

B "Datawithnon-breakingspaces"

C "Data with non-breaking spaces"

D "Data with non-breaking spaces"

💡 Hint

TRIM removes non-breaking spaces (CHAR(160)) as spaces. CLEAN removes non-printable characters but not CHAR(160).

In Google Sheets, TRIM removes all extra spaces including non-breaking spaces (CHAR(160)). CLEAN removes non-printable characters but not CHAR(160). So the output has normal spaces only.

❓ data_analysis

expert

How many characters remain after cleaning?

Cell D4 contains the text: " ExampleText   " (with spaces, a non-printable character CHAR(3)). After applying =LEN(TRIM(CLEAN(D4))), how many characters will the result have?

A 15

B 13

C 17

D 11

💡 Hint

Count characters after removing non-printable and extra spaces.

CLEAN removes CHAR(3). TRIM removes leading/trailing and extra spaces. The cleaned text is "ExampleText" which has 11 characters.