VALUE Function
Numbers that arrived as text, and will not behave until you convert them.
Task:
The payment gateway export puts every amount in as text, which is why the column will not behave like money. Convert each amount to a real number in column C.
Learning Objectives:
- Convert text to a number with VALUE
- Recognise text-formatted numbers by their alignment
- Understand why an import needs cleaning before it is used
Solve without hints for +5 XP
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | |
|---|---|---|---|
| 1 | Reference | Amount (text) | Amount |
| 2 | PAY-4471 | 1240.50 | |
| 3 | PAY-4472 | 89.99 | |
| 4 | PAY-4473 | 376.00 |
What this exercise teaches (contains the answer)
Exports from payment systems, bank portals and older databases routinely deliver numbers as text, and the damage is quiet rather than loud. Try =SUM(B2:B4) on the text column: it comes back 0. Not an error — a confident zero, because a range contributes only the numbers in it and text is skipped however much it looks like one. The same silence puts 9 after 100 when you sort, and leaves a lookup matching nothing. VALUE is the one-step fix, once you know to look for it.