Numbers stored as text
A column of quantities contains symbols, separators, or words that stop software reading it as numbers.
What it looks like
Before
| amount |
|---|
| $1,200.00 |
| 1200 |
| 1.200,00 |
| twelve hundred |
| (350) |
After
| amount |
|---|
| 1200.00 |
| 1200.00 |
| 1200.00 |
| 1200.00 |
| -350.00 |
How it happens
- Currency symbols and thousands separators were typed or exported along with the number.
- Countries disagree on whether the comma or the period marks the decimal.
- A single non-numeric cell made a spreadsheet treat the whole column as text.
What it breaks
- Sums return zero or an error, or quietly ignore the text cells.
- Sorting is alphabetical, so 1000 comes before 200.
- A misread decimal mark changes a value by a factor of a thousand with no warning.
How to spot it
- Try to total the column. A total that is too small means some cells were skipped.
- Check alignment in a spreadsheet: numbers sit right, text sits left.
- Search for characters that should not be in a number: letters, currency symbols, spaces, and parentheses.
How to fix it
- Work out which decimal convention each value uses before removing any separators.
- Strip symbols and separators, convert accounting negatives, and store a plain number.
- Keep the currency or unit in its own column if rows differ. See mixed units.
- Mixed unitsValues in one column are measured in different units, sometimes with the unit written in the cell and sometimes not.
- Disguised missing valuesA cell is not empty, but what it holds means there is no value: N/A, a dash, 999, or the word none.
- Mixed date formatsOne column holds dates written in several different styles.
- Case example: Academic researchA survey whose result depended on how blanks were coded
TermsData type, Type coercion, Parsing