Confident Cells

Numbers and dates stored as text: practice exercises

VALUE, DATEVALUE, DATE with LEFT, MID, RIGHT. 4 exercises on real-looking data, each marked the moment you press Check. The first exercise is below to try now; the full lesson is part of Pro.

Your turn

The finance system exported amounts like GBP 12.50, which a spreadsheet treats as text. In C2, turn each one into a number, so the total in C10 works.

ABC
1SupplierExported amountAmount
2Harbour CateringGBP 555.03
3Ashby StationersGBP 572.78
4Northgate SuppliesGBP 556.82
5Redbrook CouriersGBP 407.69
6Brightway PrintGBP 910.67
7Summit SoftwareGBP 871.98
8Oakfield ITGBP 167.49
9Willow TrainingGBP 233.68
10Total£0.00

How it works

Exports from other systems often bring numbers and dates in as text. They look right, but SUM ignores them and date sums go wrong. Text usually sits on the left of the cell, and numbers on the right.

VALUE turns text that looks like a number into a number. DATEVALUE does the same for a date written the UK way. When the date is in another layout, cut it into pieces and rebuild it: DATE(year, month, day), with LEFT, MID and RIGHT to take out each piece.

=VALUE(SUBSTITUTE(A3,"GBP","")) =DATEVALUE("14/03/2026") =DATE(LEFT(C3,4),MID(C3,5,2),RIGHT(C3,2)) =DATE(RIGHT(E3,4),LEFT(E3,2),MID(E3,4,2))

Spotting text that looks like numbers

Exports often bring numbers and dates in as text. Signs to look for: they sit on the left of the cell, SUM gives 0 or leaves them out, and dates won't sort properly.

VALUE turns number-like text into a number, once anything that isn't part of the number is removed, for example with SUBSTITUTE. DATEVALUE does the same for a date written the UK way.

Worked example

ABCDEF
1ExportedAmountCode dateDateUS dateDate
2GBP 12.502026031403/14/2026
3GBP 7.252026110511/05/2026
4GBP 30.002026070107/01/2026
  1. =VALUE(SUBSTITUTE(A2,"GBP","")) in B2. SUBSTITUTE removes "GBP", and VALUE turns " 12.50" into the number 12.5, which SUM can add.
  2. =DATE(LEFT(C2,4),MID(C2,5,2),RIGHT(C2,2)) in D2. 20260314: the year is the first 4 characters, the month the next 2, the day the last 2. DATE puts them together: 14/03/2026.
  3. =DATE(RIGHT(E2,4),LEFT(E2,2),MID(E2,4,2)) in F2. A US date puts the month first. Pick out each part and give DATE the year, month and day in its own order.

Why not just DATEVALUE?

DATEVALUE reads a date the way your computer's settings say. Here that's the UK way, day first. A US date such as 11/05/2026 means 5 November, but DATEVALUE would read it as 11 May, with no error to warn you. And 03/14/2026 has no 14th month, so it gives #VALUE!.

When the layout isn't the UK one, take the date apart with LEFT, MID and RIGHT and rebuild it with DATE.

What you'll practise

  1. The finance system exported amounts like GBP 12.50, which a spreadsheet treats as text. In C2, turn each one into a number, so the total in C10 works.
  2. These order dates came in as text. In C2, turn each one into a real date that the spreadsheet can work with.
  3. This system writes dates as year, month, day with no gaps: 20260314 is 14 March 2026. In C2, turn each one into a real date.
  4. These dates came from a US system, which puts the month first: 03/14/2026 is 14 March 2026. In C2, turn each one into the right date.