TRIM and CLEAN in Excel - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the TRIM function do in Excel?

TRIM removes all extra spaces from text except for single spaces between words. It helps clean up text that has extra spaces at the start, end, or between words.

beginner

What is the purpose of the CLEAN function in Excel?

CLEAN removes non-printable characters from text. These characters can cause problems when copying data from other sources like websites or PDFs.

intermediate

How would you use TRIM and CLEAN together in a formula?

You can use CLEAN first to remove non-printable characters, then TRIM to remove extra spaces. For example: =TRIM(CLEAN(A1)) cleans the text in cell A1.

beginner

True or False: TRIM removes all spaces from text.

False. TRIM removes extra spaces but keeps single spaces between words intact.

beginner

Why might you need to use CLEAN on data copied from a website?

Data copied from websites often contains hidden non-printable characters that can cause errors or display issues. CLEAN removes these characters to make the text usable.

What does the TRIM function remove from text?

A All spaces including between words

B Extra spaces except single spaces between words

C Non-printable characters

D Numbers

TRIM removes extra spaces but keeps single spaces between words.

Which function removes non-printable characters from text?

A TRIM

B LEFT

C CLEAN

D RIGHT

CLEAN removes non-printable characters that can cause issues in text.

What is the correct formula to remove non-printable characters and extra spaces from cell A1?

A =TRIM(CLEAN(A1))

B =CLEAN(A1)

C =TRIM(A1)

D =CLEAN(TRIM(A1))

Use CLEAN first to remove non-printable characters, then TRIM to remove extra spaces.

If a text has spaces only at the start and end, what will TRIM do?

A Remove spaces only at the start and end

B Remove all spaces

C Remove spaces only between words

D Do nothing

TRIM removes spaces at the start and end, and extra spaces between words.

Why is it useful to use CLEAN on data copied from PDFs?

A To add spaces

B To change font color

C To convert text to numbers

D To remove non-printable characters

PDFs often add hidden characters that CLEAN removes for better data use.

Explain how and why you would use TRIM and CLEAN together on messy text data.

Think about cleaning text copied from websites or documents.

Describe the difference between what TRIM and CLEAN functions do in Excel.

One deals with spaces, the other with hidden characters.