LEN function in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the output of LEN with spaces?

Given cell A1 contains the text " Hello World " (with spaces at start and end), what will =LEN(A1) return?

A 13

B 10

C 11

D 12

💡 Hint

Remember LEN counts all characters including spaces.

The text " Hello World " has 13 characters: 1 space + 5 letters + 1 space + 5 letters + 1 space.

📊 Formula Result

intermediate

LEN function with numbers stored as text

If cell B2 contains the text "12345" (a number stored as text), what does =LEN(B2) return?

A Error

B 12345

C 1

D 5

💡 Hint

LEN counts characters, not numeric value.

LEN counts the number of characters in the cell. The text "12345" has 5 characters.

❓ Function Choice

advanced

Which formula counts characters excluding spaces?

You want to count the number of characters in cell C3 but exclude all spaces. Which formula will do this correctly?

A =LEN(SUBSTITUTE(C3," ",""))

B =LEN(C3)-LEN(SUBSTITUTE(C3," ",""))

C =LEN(C3)+LEN(SUBSTITUTE(C3," ",""))

D =LEN(C3)*LEN(SUBSTITUTE(C3," ",""))

💡 Hint

SUBSTITUTE removes spaces, then LEN counts remaining characters.

SUBSTITUTE(C3," ","") removes all spaces. LEN then counts characters without spaces.

🎯 Scenario

advanced

Find the length of trimmed text

Cell D4 contains text with extra spaces at start and end. You want to find the length of the text without these extra spaces. Which formula gives the correct length?

A =LEN(D4)

B =LEN(TRIM(D4))

C =LEN(SUBSTITUTE(D4," ",""))

D =LEN(D4)-2

💡 Hint

TRIM removes extra spaces at start and end.

TRIM removes leading and trailing spaces. LEN then counts the trimmed text length.

❓ data_analysis

expert

Count how many cells have text length greater than 5

You have a list of names in cells A1:A10. Which formula correctly counts how many names have more than 5 characters?

A =COUNT(LEN(A1:A10)>5)

B =COUNTIF(LEN(A1:A10),">5")

C =SUMPRODUCT(--(LEN(A1:A10)>5))

D =COUNTIF(A1:A10,">5")

💡 Hint

LEN does not work directly inside COUNTIF for ranges.

LEN(A1:A10) creates an array of lengths. SUMPRODUCT counts how many are >5.