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.
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.
| A | B | C | |
|---|---|---|---|
| 1 | Supplier | Exported amount | Amount |
| 2 | Harbour Catering | GBP 555.03 | |
| 3 | Ashby Stationers | GBP 572.78 | |
| 4 | Northgate Supplies | GBP 556.82 | |
| 5 | Redbrook Couriers | GBP 407.69 | |
| 6 | Brightway Print | GBP 910.67 | |
| 7 | Summit Software | GBP 871.98 | |
| 8 | Oakfield IT | GBP 167.49 | |
| 9 | Willow Training | GBP 233.68 | |
| 10 | Total | £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
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Exported | Amount | Code date | Date | US date | Date |
| 2 | GBP 12.50 | 20260314 | 03/14/2026 | |||
| 3 | GBP 7.25 | 20261105 | 11/05/2026 | |||
| 4 | GBP 30.00 | 20260701 | 07/01/2026 |
=VALUE(SUBSTITUTE(A2,"GBP",""))in B2. SUBSTITUTE removes "GBP", and VALUE turns " 12.50" into the number 12.5, which SUM can add.=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.=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
- 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.
- These order dates came in as text. In C2, turn each one into a real date that the spreadsheet can work with.
- 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.
- 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.