Strip symbols, then VALUE
Text with a currency sign and thousands separators has to be cleaned before it converts.
Task:
The finance export writes amounts like £1,250.00 as text. In C2:C6 turn each into a real number by removing the £ sign and the commas and converting what is left.
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 | Ref | Amount (text) | Amount |
| 2 | P-01 | £1,250.00 | |
| 3 | P-02 | £86.40 | |
| 4 | P-03 | £12,999.99 | |
| 5 | P-04 | £4,000 | |
| 6 | P-05 | £0.75 |
What this exercise teachesMay contain the answer
Reading inside out: remove the symbol, remove the separators, convert. Each step is simple and the order does not matter much, but leaving any non-numeric character in place makes VALUE fail. Once converted, the column sums and sorts like money should.