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.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
Variations of VALUE
0/3The same formula in the other shapes it takes at work.
- VALUE Function
Numbers that arrived as text, and will not behave until you convert them.
CoreIntermediate - Strip symbols, then VALUE
Text with a currency sign and thousands separators has to be cleaned before it converts.
Advanced - VALUE of the number at the start of a cell
"12 boxes" is text; the 12 inside it can be a number.
Intermediate
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 |
Functions used here
What this exercise teachesMay contain 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.