Length checking with LEN

Tip: Choose Save as PDF in the print dialog to download. Use A4 paper size.

Length checking with LEN

Name:
Date:
Class:
A

Quick Questions

Shade in the A or B circle for each question.

1. Which formula checks that the postcode in A2 has exactly 4 characters?
A =LEN(A2)=4
B =LEN(A2)+4
2. A2 contains 3000. What does =LEN(A2)=4 return?
A FALSE
B TRUE
3. A2 contains 300. What does =LEN(A2)=4 return?
A FALSE
B TRUE
4. A2 contains the text 'Hi there'. What does =LEN(A2) return?
A 8
B 7
5. A2 contains 3 0 0 (with a space between each digit, so 5 characters). What does =LEN(A2)=4 return?
A TRUE
B FALSE
B

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left columnRight column
1=LEN(A2)=4 AThe 4 is in quotes, so it is text, not a number. LEN gives a number, so the comparison will not match. It should be =LEN(A2)=4.
2=LEN(B2)>=8 B=LEN(A2)=4
3Check that a postcode in cell A2 has exactly 4 characters C=LEN(TRIM(A2))=4
4LEN counts spaces, so test a trimmed value to ignore extra spaces DGives TRUE if the text in B2 meets a minimum length of 8 characters
5=LEN(A2=4) EThe =4 comparison is inside the brackets, so LEN is given a TRUE or FALSE value instead of the cell. The comparison must go after the closing bracket: =LEN(A2)=4.
6=LEN(A2)="4" FGives TRUE only if the postcode in A2 has exactly 4 characters
C

Definitions

=LEN(A2)=4
D

Fill in the Blanks

Complete each line of code by filling in the missing parts.

1.
=___(A2)=4
2.
=LEN(A2)_4
3.
=LEN(A2)__3
4.
=LEN("AB CD")=_
5.
=IF(LEN(A2)=_,"OK","Check")
E

Spot the Error

Shade in the letter of the line that contains an error.

Question 1
A =LENGTH(A2)=4
B =LEN(A3)=4
C =LEN(A4)=4
Question 2
A =IF(LEN(A2)=4,"OK","Check")
B =IF(LEN(A3)=4,"OK","Check")
C =IF(LEN(A4)=4,"OK" "Check")
Question 3
A =LEN(A2)>=8
B =LEN(A3)>=8
C =LEN(A4)<=8
Question 4
A =LEN(A2)=4
B =LEN(A3)=4
C =LEN(A4)=4
D =LEN(A5)="4"
Question 5
A =IF(COUNT(A2)=4,"OK","Check")
B =IF(LEN(A3)=4,"OK","Check")
C =IF(LEN(A4)=4,"OK","Check")

Answer Key

Length checking with LEN
Section A: Quick Questions
Q1
A — =LEN(A2)=4
Q2
B — TRUE
Q3
A — FALSE
Q4
A — 8
Q5
B — FALSE
Section B: Match the Pairs
Q1
=LEN(A2)=4 → Gives TRUE only if the postcode in A2 has exactly 4 characters
Q2
=LEN(B2)>=8 → Gives TRUE if the text in B2 meets a minimum length of 8 characters
Q3
Check that a postcode in cell A2 has exactly 4 characters → =LEN(A2)=4
Q4
LEN counts spaces, so test a trimmed value to ignore extra spaces → =LEN(TRIM(A2))=4
Q5
=LEN(A2=4) → The =4 comparison is inside the brackets, so LEN is given a TRUE or FALSE value instead of the cell. The comparison must go after the closing bracket: =LEN(A2)=4.
Q6
=LEN(A2)="4" → The 4 is in quotes, so it is text, not a number. LEN gives a number, so the comparison will not match. It should be =LEN(A2)=4.
Section C: Definitions
Q1
=LEN(A2)=4 — LEN counts the characters in a cell, spaces included. Comparing it to a number, like =LEN(A2)=4 for a 4-digit postcode, gives TRUE if the length is right and FALSE if not.
Section D: Fill in the Blanks
Q1
=LEN(A2)=4
Q2
=LEN(A2)=4
Q3
=LEN(A2)>=3
Q4
=LEN("AB CD")=5
Q5
=IF(LEN(A2)=4,"OK","Check")
Section E: Spot the Error
Q1
A — Spreadsheets have no LENGTH function. The function that counts characters is LEN, so the line should be =LEN(A2)=4.
Q2
C — The comma between the TRUE result and the FALSE result is missing. It should be =IF(LEN(A4)=4,"OK","Check").
Q3
C — A minimum length check needs >= so that 8 or more characters pass. <= would set a maximum instead, so it should be =LEN(A4)>=8.
Q4
D — Putting 4 in quotes makes it text, and LEN returns a number, so the comparison is always FALSE. It should be =LEN(A5)=4, without quotes.
Q5
A — COUNT counts how many cells hold numbers, not how many characters a cell has. Use LEN: =IF(LEN(A2)=4,"OK","Check").