Why text manipulation cleans data in Excel - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to remove extra spaces from the text in cell A1.

Excel

=TRIM([1])

Drag options to blanks, or click blank then click option'

AB1

B"A1"

CA1

D1

Attempts:

3 left

💡 Hint

Common Mistakes

Using quotes around the cell reference like "A1" treats it as text, not a cell reference.

Referencing the wrong cell like B1.

Using a number instead of a cell reference.

The TRIM function removes extra spaces from the text in cell A1, cleaning the data.

2fill in blank

medium

Complete the formula to convert the text in cell B2 to uppercase.

Excel

=UPPER([1])

Drag options to blanks, or click blank then click option'

AB2

B"b2"

Cb2

D2

Attempts:

3 left

💡 Hint

Common Mistakes

Using quotes around the cell reference, which treats it as text.

Using lowercase cell reference without quotes, which Excel does not recognize.

Using a number instead of a cell reference.

The UPPER function changes all letters in cell B2 to uppercase, which helps standardize text data.

3fill in blank

hard

Fix the error in the formula to extract the first 5 characters from cell C3.

Excel

=LEFT([1], 5)

Drag options to blanks, or click blank then click option'

ALEFT

B"C3"

C3

DC3

Attempts:

3 left

💡 Hint

Common Mistakes

Putting quotes around the cell reference.

Using a number instead of a cell reference.

Using the function name as an argument.

The LEFT function extracts characters from a cell reference. Using C3 without quotes correctly references the cell.

4fill in blank

hard

Fill both blanks to create a formula that replaces all commas with semicolons in cell D4.

Excel

=SUBSTITUTE([1], [2], ";")

Drag options to blanks, or click blank then click option'

AD4

B","

Attempts:

3 left

💡 Hint

Common Mistakes

Using incorrect cell references.

Not using quotes around the comma character.

Using wrong characters for replacement.

The SUBSTITUTE function replaces all commas (",") in cell D4 with semicolons (";").

5fill in blank

hard

Fill all three blanks to create a formula that trims spaces, converts text in E5 to lowercase, and replaces spaces with underscores.

Excel

=SUBSTITUTE(LOWER(TRIM([1])), [2], [3])

Drag options to blanks, or click blank then click option'

AE5

B" "

C"_"

D"-"

Attempts:

3 left

💡 Hint

Common Mistakes

Not nesting functions correctly.

Using wrong characters for replacement.

Forgetting quotes around space or underscore.

This formula cleans text in E5 by removing extra spaces (TRIM), changing all letters to lowercase (LOWER), and replacing spaces with underscores (SUBSTITUTE).