TRIM and CLEAN in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the output of TRIM on a text with extra spaces?

Given cell A1 contains the text " Hello World " (with extra spaces before, between, and after words), what is the result of the formula =TRIM(A1)?

A "Hello World"

B " Hello World "

C "Hello World"

D "HelloWorld"

💡 Hint

TRIM removes extra spaces but keeps single spaces between words.

The TRIM function removes all extra spaces from text except for single spaces between words. So it removes leading, trailing, and multiple spaces between words.

📊 Formula Result

intermediate

What does CLEAN do to text with non-printable characters?

Cell A1 contains the text "Hello World" where is a line break (non-printable character). What is the result of =CLEAN(A1)?

A "Hello
World"

B "Hello World" (with line break)

C "Hello World"

D "HelloWorld"

💡 Hint

CLEAN removes non-printable characters like line breaks.

CLEAN removes all non-printable characters such as line breaks, so the text becomes one continuous string without breaks.

❓ Function Choice

advanced

Which formula removes both extra spaces and non-printable characters?

You have text in cell A1 that contains extra spaces and hidden line breaks. Which formula will clean the text by removing both extra spaces and non-printable characters?

A =CLEAN(TRIM(A1))

B =TRIM(A1)

C =CLEAN(A1)

D =TRIM(CLEAN(A1))

💡 Hint

Think about which function should run first to clean non-printable characters before trimming spaces.

CLEAN removes non-printable characters first, then TRIM removes extra spaces. So =TRIM(CLEAN(A1)) is the correct order.

📊 Formula Result

advanced

What is the output of TRIM on a text with non-breaking spaces?

Cell A1 contains the text "Hello World" where   is a non-breaking space (different from normal space). What does =TRIM(A1) return?

A "Hello World"

B "Hello World"

C "HelloWorld"

D "Hello World"

💡 Hint

TRIM only removes ASCII space (code 32), not non-breaking spaces.

TRIM removes only normal spaces (ASCII 32). Non-breaking spaces (ASCII 160) remain unchanged, so the text stays the same.

🎯 Scenario

expert

Cleaning imported data with hidden characters and spaces

You imported data into Excel and notice some cells have extra spaces and hidden characters causing errors in formulas. You want to clean cell A1 so it has no extra spaces and no hidden non-printable characters. Which formula should you use?

A =TRIM(CLEAN(A1))

B =TRIM(SUBSTITUTE(CLEAN(A1), CHAR(160), " "))

C =SUBSTITUTE(TRIM(A1), CHAR(160), " ")

D =CLEAN(TRIM(A1))

💡 Hint

Non-breaking spaces (CHAR(160)) are not removed by TRIM or CLEAN, so you need to replace them.

First CLEAN removes non-printable characters, then SUBSTITUTE replaces non-breaking spaces with normal spaces, and finally TRIM removes extra spaces. This sequence cleans the text fully.