A column of amounts exported from the business software looks perfect, but the total at the bottom is zero, or lower than it should be. A VLOOKUP cannot find codes that are clearly there. A sort puts 100 before 20. The symptom differs, the cause is almost always the same: some values that look like numbers are actually text.
How to spot them
There are several clues, best used together:
- alignment: without alignment set by hand, numbers sit on the right of the cell and text on the left;
- error indicator: Excel shows a small green triangle in the corner of the cell with the warning "Number stored as text";
- status bar: when you select the column, the bar at the bottom shows the count but not the sum, or a sum lower than expected;
- formula check:
=ISNUMBER(A2)returns FALSE for a number stored as text;=ISTEXT(A2)returns TRUE.
To check a whole column, comparing =COUNT(A2:A5000) with =COUNTA(A2:A5000) immediately tells you how many cells are not numbers.
Where they come from
The most frequent causes are:
- exports from other systems that wrap values in quotes or format them as text;
- copying and pasting from web pages or PDFs, which often brings along spaces, non-breaking spaces or invisible characters;
- cells formatted as Text before input: anything you type becomes text, even after you change the format;
- a leading apostrophe, which in Excel forces the content to be treated as text;
- decimal or thousands separators different from those expected by the regional settings.
The last point deserves a closer look.
The decimal separator problem
In Italy the decimal separator is the comma and the thousands separator is the full stop: 1.234,56. In English-speaking countries it is the other way round: 1,234.56. The spreadsheet interprets what you type, or what you import, according to the operating system's regional settings (or the program's own settings, if changed).
If on a computer with Italian settings you import a file containing "1234.56", the program may:
- treat it as text, because the full stop is not a valid decimal separator;
- or, in some cases, interpret it as a date or as a completely different number, if the full stop is read as a thousands separator.
The second case is the most insidious, because you cannot see it: 1.234 becomes one thousand two hundred and thirty-four instead of one point two three four.
Converting text to numbers
Quick tools
- Excel's error warning: when you select the cells with the triangle, the menu offers "Convert to Number". It works well if the text is already in the correct format.
- Paste Special, Multiply: type 1 in an empty cell, copy it, select the column to convert and use Paste Special choosing the Multiply operation. The program is forced to interpret every value as a number.
- Text to Columns: by selecting the column and confirming the procedure straight away, Excel reinterprets the values. In the advanced options you can state which decimal and thousands separators the source text uses, and it is the cleanest way to fix an import in a foreign format.
With formulas
=VALUE(A2) converts text to a number according to the current regional settings. If the text uses a different format, NUMBERVALUE (NUMERO.VALORE) lets you declare the separators:
=NUMBERVALUE(A2, ",", ".")
The second argument is the decimal separator of the source text, the third the thousands separator. This formula turns "1.234,56" into 1234.56 on an English-language computer, regardless of the settings. It is the most reliable solution when you regularly receive files from abroad.
If the text contains currency symbols or spaces, clean it first:
=NUMBERVALUE(TRIM(SUBSTITUTE(A2, "€", "")), ",", ".")
Negative numbers with a trailing sign
Some systems export negatives as "125,00-". To convert them:
=IF(RIGHT(A2,1)="-", -NUMBERVALUE(LEFT(A2, LEN(A2)-1), ",", "."), NUMBERVALUE(A2, ",", "."))
When text is the right choice
Not everything made of digits should be a number. These should be kept as text:
- codes with leading zeros, such as Italian postcodes ("00184") or product codes ("000457");
- phone numbers, which may start with zero or with the international prefix;
- codes longer than 15 digits, which as numbers would lose their final digits;
- identifiers on which you will never do arithmetic.
In these cases set the column to Text before entering or importing the data. The test is simple: if it makes sense to add it up or average it, it is a number; otherwise it is a code.
In my work, when a sheet receives data from external sources, I add a small check row with COUNT and COUNTA for each numeric column: if the two counts differ, I know at once that something came in as text.
In short
- Left-aligned numbers, green triangles and low totals point to values stored as text.
- COUNT compared with COUNTA measures the problem across a whole column.
- For files with foreign separators use NUMBERVALUE, declaring the separators.
- Paste Special with Multiply by 1 and Text to Columns are quick conversions.
- Postcodes, phone numbers and long codes should be kept as text.
