Converting text to numbers and fixing date formats

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. A column of marks is left-aligned in the cells. What does this most likely warn you about?

A The marks are stored as text, not true numbers

B The marks have been validated correctly

2. Cell A2 holds the text '45'. Which formula converts it into a true number?

A =TEXT(A2)

B =VALUE(A2)

3. What happens if SUM is used on numbers that are stored as text?

A The text values are ignored, so the total can be wrong

B They are converted automatically and added correctly

4. Which tool can convert a whole column of text numbers into true numbers without writing a formula?

A Text to Columns

B Conditional formatting

5. A column of dates is stored as text and is sorted A to Z. What is the likely problem?

A The dates sort in correct calendar order

B The dates sort as text, so the order is not by true date

Match the Pairs

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

Left column		Right column
1=VALUE(A2)		AData > Text to Columns > Date: DMY
2=VALUE(TRIM(A2))		BThe values are stored as text, so SUM ignores them. Convert them to numbers with VALUE() or Text to Columns first.
3Numbers stored as text sit on the left of a cell, which warns you they are not true numbers, so convert them with a function		CRemoves stray spaces from the text in A2, then converts what is left into a true number
4Text to Columns can reformat a whole column of dates stored as text into a real date format in one step		DText dates sort alphabetically, not by date. Convert them to real dates by fixing the date format first.
5=SUM(B2:B6) returns 0 when B2:B6 hold left-aligned '45', '60', '52' stored as text		EConverts the text '45' in A2 into the true number 45 so it can be added, averaged and sorted correctly
6Sorting dates stored as text such as '12/03/2025' and '05/11/2024' and expecting a date order		F=VALUE(B2)

Definitions

=VALUE(A2)

Fill in the Blanks

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

=_____(A2)

=VALUE(B5)_VALUE(B6)

=VALUE(____(A2,2))

=IF(______(A2),VALUE(A2),A2)

=_________(A2)

Spot the Error

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

Question 1

A A2 holds the text '45'

B B2: =VALUE(B2)

C C2: =SUM(B2:B9)

Question 2

A B2: =VALUE(A3)

B B3: =VALUE(A3)

C B4: =VALUE(A4)

D B10: =SUM(B2:B4)

Question 3

A Select the date column A

B Data > Text to Columns

C Choose Text: DMY, then Finish

D Dates are now true dates

Question 4

A A2:A9 are dates held as text

B B2: =DATEVALUE(A2)

C Sort A2:A9 oldest to newest

Question 5

A B2: =VALUE("A2")*1.1

B B3: =VALUE(A3)*1.1

C B4: =VALUE(A4)*1.1

Answer Key

Converting text to numbers and fixing date formats

Section A: Quick Questions

A — The marks are stored as text, not true numbers

B — =VALUE(A2)

A — The text values are ignored, so the total can be wrong

A — Text to Columns

B — The dates sort as text, so the order is not by true date

Section B: Match the Pairs

=VALUE(A2) → Converts the text '45' in A2 into the true number 45 so it can be added, averaged and sorted correctly

=VALUE(TRIM(A2)) → Removes stray spaces from the text in A2, then converts what is left into a true number

Numbers stored as text sit on the left of a cell, which warns you they are not true numbers, so convert them with a function → =VALUE(B2)

Text to Columns can reformat a whole column of dates stored as text into a real date format in one step → Data > Text to Columns > Date: DMY

=SUM(B2:B6) returns 0 when B2:B6 hold left-aligned '45', '60', '52' stored as text → The values are stored as text, so SUM ignores them. Convert them to numbers with VALUE() or Text to Columns first.

Sorting dates stored as text such as '12/03/2025' and '05/11/2024' and expecting a date order → Text dates sort alphabetically, not by date. Convert them to real dates by fixing the date format first.

Section C: Definitions

=VALUE(A2) — Converts text that looks like a number, such as '45' stored as text, into a true number so it can be added, averaged and sorted correctly. Left-aligned numbers are a warning sign.

Section D: Fill in the Blanks

=VALUE(A2)

=VALUE(B5)+VALUE(B6)

=VALUE(LEFT(A2,2))

=IF(ISTEXT(A2),VALUE(A2),A2)

=DATEVALUE(A2)

Section E: Spot the Error

B — B2 refers to itself, which creates a circular reference. VALUE must point at the text cell, so it should be =VALUE(A2).

A — B2 should convert the text in its own row, A2. Pointing at A3 converts the wrong value and duplicates row 3, so the total is wrong. It should be =VALUE(A2).

C — Choosing Text keeps the values as text, so they still cannot be sorted or calculated as dates. The column data format must be Date (DMY).

C — Text dates sort alphabetically, not by time. The sort must use the converted true dates in B2:B9.

A — Quotes turn A2 into the plain text "A2", which cannot be converted and gives #VALUE!. The cell reference must have no quotes: =VALUE(A2)*1.1.