Text › CLEAN, TRIM53 / 144

Progress is saved on this device. Sign in or sign up free to keep it in your account.

EasyFreeCLEANTRIM

CLEAN & TRIM: clean text pasted from a PDF

Gilang copied a product list from a supplier's PDF catalog into Excel. Some product names carry invisible characters such as line breaks and tabs, plus extra spaces, so his price-matching formulas keep failing. Column B shows the length of the original text.

Task

Fill C2:C6 with the cleaned product name: remove invisible characters with CLEAN, then remove extra spaces with TRIM.

Cells to fillC2:C6

Required functionsCLEANTRIM

Expected result

Loading the expected result…

Check answerCtrlShiftEnter
Fill downCtrlD
Previous / next exerciseAlt←→
Leave the workspaceEscTab

Hint 1
CLEAN removes non-printable characters (codes 0 to 31), such as line breaks and tabs. Ordinary spaces are left alone.
Hint 2
TRIM removes spaces at the start and end, and squeezes double spaces in the middle down to one.
Hint 3
Run CLEAN first, then pass its result to TRIM.

Reveal them one at a time. The last hint nearly gives the answer away.

Give it a try first. The solution unlocks automatically after your first check.

Answer

C2:C6

=TRIM(CLEAN(A2))

Write it in C2, then fill down to C6.

Explanation

=TRIM(CLEAN(A2)) removes two kinds of junk: CLEAN drops line breaks and tabs, then TRIM tidies the spaces. TRIM alone is not enough because it ignores line breaks, and CLEAN alone leaves the extra spaces. Compare the numbers in column B with the length of the cleaned text to see how many characters were removed. Note: text from web pages often carries the special space CHAR(160), which neither CLEAN nor TRIM removes; replace it first with SUBSTITUTE(A2,CHAR(160)," ").

Starting worksheet: Sheet1
Row	A	B	C
1	Name from PDF	Length	Clean name
2	Gula Aren Cair
3	Susu UHT 1 L
4	Kopi Bubuk 200 g
5	Teh Melati
6	Keju Cheddar 165 g
7
8
9
10
11
12
13
14

Fill in the requested cell, then press Check answer.

Validation result