Why text functions clean messy data in Google Sheets - Challenge Your Understanding

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the output of TRIM function?

Given cell A1 contains the text: " Hello World ", what will be the result of the formula =TRIM(A1)?

Google Sheets

=TRIM(A1)

A "Hello World"

B "Hello World"

C " Hello World "

D "HelloWorld"

💡 Hint

TRIM removes extra spaces except single spaces between words.

TRIM removes all extra spaces before, after, and between words, leaving only single spaces between words.

📊 Formula Result

intermediate

How does CLEAN function affect text?

If cell A2 contains the text with hidden characters: "HelloWorld", what will =CLEAN(A2) output?

Google Sheets

=CLEAN(A2)

A "HelloWorld"

B "HelloWorld"

C "Hello World"

D "Hello\u0007World"

💡 Hint

CLEAN removes non-printable characters.

CLEAN removes all non-printable characters like the bell character \u0007, so the output is "HelloWorld" without that character.

❓ Function Choice

advanced

Which function removes all spaces including between words?

You want to remove every space from the text in cell A3, including spaces between words. Which formula should you use?

A =SUBSTITUTE(A3, " ", "")

B =TRIM(A3)

C =LEFT(A3, LEN(A3)-1)

D =CLEAN(A3)

💡 Hint

Think about replacing spaces with nothing.

SUBSTITUTE replaces all spaces with empty text, removing all spaces including those between words. TRIM only removes extra spaces but keeps single spaces between words.

🎯 Scenario

advanced

Cleaning imported data with mixed spaces and hidden characters

You imported a list of names into cell A4 with extra spaces and hidden characters. Which combination of functions will best clean the text to have single spaces only and no hidden characters?

A =SUBSTITUTE(TRIM(A4), " ", "")

B =CLEAN(TRIM(A4))

C =TRIM(CLEAN(A4))

D =LEFT(CLEAN(A4), LEN(A4)-1)

💡 Hint

Think about the order: remove hidden characters first or trim spaces first?

Using CLEAN first removes hidden characters, then TRIM removes extra spaces leaving single spaces between words. This order ensures clean and readable text.

❓ data_analysis

expert

Count how many cells have messy data after cleaning

You have a column A with 10 cells containing text with extra spaces and hidden characters. Which formula counts how many cells still have extra spaces after applying TRIM and CLEAN?

A =COUNTIF(A1:A10, "<>" & TRIM(CLEAN(A1:A10)))

B =SUM(IF(A1:A10=TRIM(CLEAN(A1:A10)),1,0))

C =COUNTIF(A1:A10, TRIM(CLEAN(A1:A10)))

D =SUMPRODUCT(--(A1:A10<>TRIM(CLEAN(A1:A10))))

💡 Hint

You need to compare original and cleaned text for each cell and count differences.

SUMPRODUCT with a logical test counts how many cells differ from their cleaned version. COUNTIF with arrays does not work as expected here.