← VCE Data Analytics U3O1

Converting text to numbers and fixing date formats

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.

Part of: VCE Data Analytics - U3O1 - Data Analytics — Module 77, ACARA Digital Technologies Years 7–8 — Acquiring and Storing Data — Module 63, ACARA Digital Technologies Years 9–10 — Acquiring and Validating Data — Module 102, ACARA Digital Technologies Years 9–10 — Analysing and Visualising Data — Module 7, QCE Digital Solutions Unit 4 — Complex Digital Data Exchange Problems and Solution Requirements — Module 72

Understanding Converting text to numbers and fixing date formats in VCE Data Analytics U3O1

What it does: Left-aligned numbers are a warning sign that the value is text, and VALUE(B2) converts it to a real number. SUM skips text values in a range. The total can be too low until the values are converted to numbers. Text to Columns can reconvert a column and set its data type, for example to a number or a date. Conditional formatting only changes how cells look. Text dates such as '12/03/2025' are sorted character by character. Converting them to real dates fixes the sort order.

Common mistakes: B2 refers to itself, which creates a circular reference. VALUE must point at the text cell, so it should be =VALUE(A2). 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).

Class: _____________________ · Name: _____________________ · Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add Converting text to numbers and fixing date formats to a class →

This module's key terms are also in a printable crossword (Modules 50–91) and word search (Modules 17–91).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning

Duo

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

The marks have been validated correctlyThe marks are stored as text, not true numbers

Matches

=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.

Applying

Meanings

Term

=VALUE(A2)

Meaning

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.

Practice

=VALUE(C2)*VALUE(D2)

Testing

Gaps

=(A2)

Check Answer

Spots

1A2 holds the text '45'

2B2: =VALUE(B2)

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy

Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games

Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print

Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

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

Numbers are normally right-aligned in a spreadsheet. Left-aligned numbers are a warning sign that they may be stored as text.

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

A. =TEXT(A2)

B. =VALUE(A2) ✓

VALUE() converts text that looks like a number into a real number so it can be calculated, averaged and sorted.

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

SUM skips text values in a range. The total can be too low until the values are converted to numbers.

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

Text to Columns can reconvert a column and set its data type, for example to a number or a date. Conditional formatting only changes how cells look.

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 ✓

Text dates such as '12/03/2025' are sorted character by character. Converting them to real dates fixes the sort order.

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.

Key Term

=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.

Fill in the Blanks

1. =VALUE(A2)

Hint: Type the function name that turns text that looks like a number into a true number.

2. =VALUE(B5)+VALUE(B6)

Hint: Type the arithmetic operator that adds the two converted numbers together.

3. =VALUE(LEFT(A2,2))

Hint: Type the text function that pulls the first characters from the cell so the digits can then be converted.

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

Hint: Type the function that checks whether a cell holds text, the warning sign for a number stored as text.

5. =DATEVALUE(A2)

Hint: Type the function that converts a date typed as text into a real date value that can be sorted.

Spot the Error

Question 1

A A2 holds the text '45'

B B2: =VALUE(B2)

C C2: =SUM(B2:B9)

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

Question 2

A B2: =VALUE(A3)

B B3: =VALUE(A3)

C B4: =VALUE(A4)

D B10: =SUM(B2:B4)

Line 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).

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

Line 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).

Question 4

A A2:A9 are dates held as text

B B2: =DATEVALUE(A2)

C Sort A2:A9 oldest to newest

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

Question 5

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

B B3: =VALUE(A3)*1.1

C B4: =VALUE(A4)*1.1

Line 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.

Student with a class code? Join your class →