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

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to remove extra spaces from cell A1.

Google Sheets

=TRIM([1])

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

A1

BB1

C"A1"

DA1

Attempts:

3 left

💡 Hint

Common Mistakes

Using quotes around the cell reference

Referencing the wrong cell

Using a number instead of a cell

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

2fill in blank

medium

Complete the formula to convert the text in B2 to all uppercase letters.

Google Sheets

=UPPER([1])

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

AB2

Bb2

C"B2"

D2

Attempts:

3 left

💡 Hint

Common Mistakes

Using quotes around the cell reference

Using lowercase cell reference

Using a number instead of a cell

The UPPER function converts all letters in cell B2 to uppercase. Use the cell reference without quotes.

3fill in blank

hard

Fix the error in the formula to replace all commas with semicolons in cell C3.

Google Sheets

=SUBSTITUTE(C3, [1], [2])

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

A",", ";"

B",", ","

D",", ":"

Attempts:

3 left

💡 Hint

Common Mistakes

Missing quotes around characters

Using wrong replacement character

Using same character for old and new text

The SUBSTITUTE function replaces all commas "," with semicolons ";" in cell C3. Both blanks need the correct string values with quotes.

4fill in blank

hard

Fill both blanks to create a formula that extracts the first 5 characters from cell D4 and converts them to lowercase.

Google Sheets

=LOWER(LEFT([1], [2]))

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

AD4

B5

C4

DD5

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong cell reference

Using wrong number of characters

Swapping the blanks

The LEFT function extracts the first 5 characters from cell D4, and LOWER converts them to lowercase.

5fill in blank

hard

Fill all three blanks to create a formula that finds the position of the first space in cell E5 and extracts the word before it.

Google Sheets

=LEFT([1], [2]([1], " ") - [3])

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

AE5

BFIND

C1

DSEARCH

Attempts:

3 left

💡 Hint

Common Mistakes

Using SEARCH instead of FIND

Not subtracting 1

Using wrong cell reference

The FIND function locates the first space " " in cell E5. LEFT extracts characters from the start up to one less than that position to get the first word.