Format checking text and dates

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. What does a format check do?

A Tests that a value is true in real life

B Tests that a value follows an expected pattern

2. Student IDs look like KT482 (two letters then three digits). Which formula pulls out the letters part to test?

A =RIGHT(A2,2)

B =LEFT(A2,2)

3. Student IDs look like KT482. Which formula pulls out the three digits at the end?

A =RIGHT(A2,3)

B =LEFT(A2,3)

4. Which formula correctly tests that the last three characters of A2 are digits?

A =ISNUMBER(VALUE(RIGHT(A2,3)))

B =ISNUMBER(RIGHT(A2,3))

5. A2 holds 31/02/2025, which the spreadsheet could not read as a real date, so it stays as text. What does =ISNUMBER(A2) return?

A FALSE

B TRUE

Match the Pairs

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

Left column		Right column
1=ISNUMBER(LEFT(A2,2)*1)		AChecks that a date was entered as a real date, because spreadsheets store real dates as numbers
2=ISNUMBER(B2)		BLEFT returns text, even when it looks like digits, so ISNUMBER gives FALSE. Multiply by 1 first, as in ISNUMBER(LEFT(A2,2)*1).
3A format check on a code of two letters then three digits can test the last three characters with RIGHT		C=ISNUMBER(RIGHT(A2,3)*1)
4A value can pass a format check and still be wrong, so the check does not prove the data is correct		DTests whether the first two characters of a code are numbers, which would be wrong for a code that should start with letters
5=ISNUMBER(LEFT(A2,2)) to check a code starts with two digits		EAB123 passes the pattern but is not a real student ID
6Date entered as 31/02/2025 is accepted because it looks like dd/mm/yyyy		FThe pattern looks right but 31 February is not a real date. A format check needs to test for a real date, not just the look of one.

Definitions

format check

Fill in the Blanks

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

=ISNUMBER(VALUE(_____(A2,3)))

=IF(____(A2,2)="AB","Prefix OK","Bad")

=IF(________(B2),"Real date","Not a date")

=RIGHT(A2,_)

=AND(ISNUMBER(B2),B2__TODAY())

Spot the Error

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

Question 1

A code ← A2

B letters ← LEFT(code, 2)

C digits ← LEFT(code, 3)

D ok ← ISNUMBER(VALUE(digits))

Question 2

A code ← A2

B ok ← LEN(code) = 3

C IF ok THEN accept code

Question 3

A date ← A2

B isDate ← ISNUMBER(date)

C IF isDate = TRUE THEN

D SHOW 'Not a real date'

E ELSE

F SHOW 'Not a real date'

Question 4

A letters ← RIGHT(code, 2)

B digits ← RIGHT(code, 3)

C ok ← ISNUMBER(VALUE(digits))

Question 5

A d ← '31/02/2026'

B ok ← ISNUMBER(DATEVALUE(d))

C IF ok = TRUE THEN reject d

Answer Key

Format checking text and dates

Section A: Quick Questions

B — Tests that a value follows an expected pattern

B — =LEFT(A2,2)

A — =RIGHT(A2,3)

A — =ISNUMBER(VALUE(RIGHT(A2,3)))

A — FALSE

Section B: Match the Pairs

=ISNUMBER(LEFT(A2,2)*1) → Tests whether the first two characters of a code are numbers, which would be wrong for a code that should start with letters

=ISNUMBER(B2) → Checks that a date was entered as a real date, because spreadsheets store real dates as numbers

A format check on a code of two letters then three digits can test the last three characters with RIGHT → =ISNUMBER(RIGHT(A2,3)*1)

A value can pass a format check and still be wrong, so the check does not prove the data is correct → AB123 passes the pattern but is not a real student ID

=ISNUMBER(LEFT(A2,2)) to check a code starts with two digits → LEFT returns text, even when it looks like digits, so ISNUMBER gives FALSE. Multiply by 1 first, as in ISNUMBER(LEFT(A2,2)*1).

Date entered as 31/02/2025 is accepted because it looks like dd/mm/yyyy → The pattern looks right but 31 February is not a real date. A format check needs to test for a real date, not just the look of one.

Section C: Definitions

format check — A validation check that tests a value follows an expected pattern, like two letters then three digits or a real date. A correct pattern does not prove the data is right.

Section D: Fill in the Blanks

=ISNUMBER(VALUE(RIGHT(A2,3)))

=IF(LEFT(A2,2)="AB","Prefix OK","Bad")

=IF(ISNUMBER(B2),"Real date","Not a date")

=RIGHT(A2,3)

=AND(ISNUMBER(B2),B2<=TODAY())

Section E: Spot the Error

C — A code like AB123 has its three digits at the END. LEFT takes characters from the start, so it would grab letters. It should be RIGHT(code, 3).

B — A code of two letters then three digits is 5 characters long, not 3. The length test should be LEN(code) = 5.

D — This is the branch for when ISNUMBER returns TRUE, so the message should say 'Real date'. As written, both branches give the same message and the check tells the user nothing.

A — The two letters come at the start of the code, so you need LEFT(code, 2). RIGHT would take the last two digits instead.

C — 31/02/2026 is not a real date, so ok is FALSE and the entry should be rejected. The broken line rejects dates that pass the check. It should test ok = FALSE.