LEN function in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Calculate length of trimmed text

Given cell A1 contains the text " Hello World ", which formula correctly returns the length of the text without leading and trailing spaces?

A =LEN(TRIM(A1))

B =LEN(A1)

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

D =LEN(A1)-2

💡 Hint

Think about how to remove spaces before counting characters.

The TRIM function removes extra spaces from the start and end of the text. LEN(TRIM(A1)) counts characters after trimming.

📊 Formula Result

intermediate

Counting characters including spaces

If cell B2 contains the text "Data Science", what does the formula =LEN(B2) return?

A 11

B 10

C 12

D 9

💡 Hint

Count all letters and spaces in the text.

"Data Science" has 12 characters including the space between words.

❓ Function Choice

advanced

Choose formula to count characters excluding spaces

Which formula correctly counts the number of characters in cell C3 excluding all spaces?

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

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

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

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

💡 Hint

Think about removing spaces first, then counting characters.

SUBSTITUTE(C3," ","") removes spaces. LEN counts characters after removal.

📊 Formula Result

advanced

Length of a formula-generated string

Cell D4 contains the formula =CONCATENATE("Hi", " ", "There"). What does =LEN(D4) return?

A 7

B 8

C 6

D 5

💡 Hint

Count all letters and spaces in the combined text.

"Hi There" has 8 characters including the space.

🎯 Scenario

expert

Find the number of characters in a cell ignoring trailing spaces only

You want to count the number of characters in cell E5 but ignore only trailing spaces (spaces at the end). Which formula will give the correct count?

A =LEN(E5)-LEN(RIGHT(E5,LEN(E5)-LEN(RTRIM(E5))))

B =LEN(E5)-LEN(LEFT(E5,LEN(E5)-LEN(RTRIM(E5))))

C =LEN(E5)-LEN(TRIM(E5))

D =LEN(RTRIM(E5))

💡 Hint

Use a function that removes spaces only from the right side.

RTRIM removes trailing spaces. LEN(RTRIM(E5)) counts characters ignoring trailing spaces.