Cleaning extracted tables for Excel

A spreadsheet decides whether a cell is a number when the value arrives, and it is strict. One leading space, one trailing space, one non-breaking space between the thousands group and the rest, and the cell is text forever after. Typographers love the narrow no-break space (U+202F) and the figure space (U+2007) in printed tables, and both survive extraction invisibly.

The same applies to the minus sign. A well-set document uses U+2212, a proper typographic minus, which no spreadsheet recognises as arithmetic; the hyphen-minus on your keyboard is a different character. Percent signs, footnote markers glued to a value, and a stray currency symbol all have the same effect: the column right-aligns as text and SUM returns zero.

A fast diagnostic: select the column and look at the status bar. If Excel or Google Sheets shows a count but no sum, the values are text. Formatting the cells as Number afterwards will not help — formatting changes how a value is displayed, not what it is.

English-language documents write 1,284.50; most of Europe and Russia write 1 284,50 or 1.284,50. A spreadsheet interprets an incoming file according to its own locale, so a German Excel reading an American CSV turns 1,284.50 into 1.28450 or into text, depending on the column. Converting the separators before the file reaches the spreadsheet removes the ambiguity from the equation.

One case cannot be resolved by any software, including this one. The string 1,234 is one thousand two hundred thirty-four in London and one point two three four in Berlin, and nothing in the cell says which. The convention every tool uses — a group of exactly three digits after the last separator is a thousands group — is right far more often than it is wrong, but it is a convention, not a deduction.

So verify. Almost every real table has a total row, a subtotal or a page sum. Convert the numbers, sum the column yourself and compare with the printed total. If they match to the cent, the separator interpretation was right for every row, not just the ones you looked at.

Currency symbols belong to presentation, not to data. Strip them at extraction time and apply a currency format in the spreadsheet instead; that way the column sorts and sums correctly and still prints with a euro sign. Watch for three-letter codes as well — EUR and USD written after the amount cause exactly the same problem as the symbol.

Accounting documents print negative amounts in brackets: (320.00) means minus three hundred and twenty. No spreadsheet reads that on import, and a column where a handful of values are bracketed will sum to a number that is wrong by twice the bracketed total — a mistake that is easy to miss precisely because the result still looks plausible.

Percentages need a decision. In a spreadsheet, 22% is stored as 0.22 with a percent format; the text 22% imported as a number usually becomes 22. Decide whether the column is a rate or a label, and if it is a rate, divide by a hundred once, deliberately, rather than discovering the factor later in a formula.

Dates are the most dangerous column in any extracted table, because 03/04/2026 is the third of April in Tallinn and the fourth of March in Chicago, and the spreadsheet decides silently. If you control the extraction, rewrite dates as ISO 8601 (2026-04-03) before import: it is unambiguous, it sorts correctly as text, and every spreadsheet parses it the same way.

Autocorrection is the other silent editor. Excel has converted product codes, part numbers and gene names into dates for decades — the problem was severe enough in genomics that in 2020 the HUGO Gene Nomenclature Committee renamed dozens of genes to stop it. Leading zeros vanish from account numbers and postal codes for the same reason: the column was guessed to be numeric.

The defence is to import rather than open. In Excel, Data → From Text/CSV opens Power Query, where each column can be typed explicitly, and codes can be set to Text before a single value is parsed. In Google Sheets, File → Import and switch off “Convert text to numbers, dates and formulas” for the same effect.

Save CSV as UTF-8 with a byte-order mark. Without those three bytes at the start of the file, Excel on Windows falls back to the system code page and Cyrillic, Estonian or French text arrives as mojibake. Choose the delimiter to match the target: Excel in a locale that uses a decimal comma expects a semicolon, so a comma-delimited file lands entirely in column A.

For a quick job, skip files entirely: copying the table as tab-separated text and pasting it puts the values straight into cells in both Excel and Google Sheets, with no import dialog and no encoding question. It is the fastest path when you only need the numbers once.

One security note. A CSV cell that starts with =, + or @ is a formula to a spreadsheet, and opening an untrusted file can trigger it. If the table is going to someone else, or came from a source you do not control, turn on the option that prefixes such cells with an apostrophe — it costs nothing and closes a known injection route.

Three checks catch nearly everything. Count the rows and compare with the document: a missing first or last row usually means the selection rectangle clipped the table. Sum a numeric column and compare with the printed total. Then read the last row of the table on screen and the last row in the spreadsheet side by side — a footer line that wandered into the data shows up immediately.

Keep the PDF next to the spreadsheet until those three agree. The cost of a wrong extraction is not the time spent redoing it; it is the report that was built on a column which quietly lost its decimal point.