Why the paste refuses to add up
A spreadsheet decides whether a cell holds a number or a string the moment you paste it, and anything it cannot parse becomes text — silently, with no error, and with SUM quietly excluding it. The causes are a short list: a currency symbol or a unit attached to the value, a thousands separator the locale does not expect, a negative written as parentheses or with the minus trailing behind, and invisible padding. That last one catches people repeatedly, because PDF extraction often produces a non-breaking space rather than a normal one and the two are indistinguishable on screen. All four are stripped here, and the output is a bare decimal that any sheet will take.
Accounting negatives
Finance has written negatives as parentheses for far longer than spreadsheets have existed, so (2,300) means −2,300 and every statement you copy from will use it. Some ledger exports put the minus sign after the digits instead, as 1,234.50-. Both are read as negative here. On the way out you can choose which convention to print, with one warning attached: parentheses are a display format, and a value exported that way will land as text again the next time somebody pastes it. Use the minus sign for anything that has to be calculated with, and reserve the parentheses for a document a person reads.
1,234.56 against 1.234,56
Most of continental Europe reverses the two separators, so a German or Spanish export writes one thousand two hundred thirty-four point five six as 1.234,56. Read with US assumptions that becomes 1.234, which is a factor of a thousand out and looks entirely plausible in a column — this is the single most dangerous ambiguity in pasted numbers. On the automatic setting each line is judged on its own:
| The line contains | Decision | Example |
|---|---|---|
| Both a dot and a comma | Whichever comes last is the decimal point | 1.234,56 → 1234.56 |
| Commas only, in strict 3-digit groups | Thousands separators | 1,234,500 → 1234500 |
| Commas only, not in 3-digit groups | Decimal comma | 12,5 → 12.5 |
| Dots only, in strict 3-digit groups | Thousands separators | 1.234.500 → 1234500 |
| Dots only, otherwise | Decimal point | 3.2 → 3.2 |
| Spaces or apostrophes | Always thousands separators | 1 234 567 → 1234567 |
The one case automatic detection cannot solve is a lone 1,234, which is one thousand two hundred thirty-four in one convention and 1.234 in the other. It is read as thousands. If your source is European, set the convention explicitly rather than trusting the guess.
Suffixes, percentages and lines with two numbers
A magnitude letter or word directly after the number multiplies it: 3.2M is 3,200,000, and k, M, B, bn, thousand, million and billion are all recognised. A percent sign is stripped and the digits kept as written, so 12% becomes 12 rather than 0.12 — if you want the decimal fraction, divide afterwards in the sheet. Lines holding more than one number use the first, which means 2024 45,000 reads as 2024 and not the amount you wanted. Those lines are flagged in the table so you can spot them, but the fix is to remove the leading label before pasting.
Rounding, and where the total comes from
Choosing a decimal place count rounds each displayed line. The total and average are computed from the unrounded values and formatted once at the end, which means adding up the visible column by hand can differ from the stated total by a unit or two. That is the correct behaviour for accuracy and the wrong behaviour for a reconciliation where the printed figures have to foot exactly — for that, leave the decimals as written and let the source values speak. Up to 5,000 lines per run.
Questions people ask
Can I paste a whole table?
It expects one value per line and takes the first number it finds on each, so a multi-column table gives you the leftmost figure from every row. Run the table through the CSV table cleaner first, select the single column you care about, and paste that here. If the table came out of a PDF with columns separated by runs of spaces, the CSV cleaner will not split it either — you will need to fix the delimiter by hand.
It says 12% became 12, not 0.12.
That is deliberate. Percentages in a pasted report are almost always meant to stay as the number you can read — a 12% margin stays 12 in a column of margins. Converting to a fraction would silently break every such column while helping the smaller case where you were about to multiply. Divide by 100 in the spreadsheet if you need the fraction.
The output went back into Excel as text again.
The grouped, currency and compact output modes all add characters a sheet cannot parse — separators, symbols, a trailing M. Pick plain output for anything you are going to calculate with, then apply number formatting in the spreadsheet, which changes the display without touching the stored value. Parentheses for negatives have the same effect for the same reason.
A negative came out positive.
Check the negative display setting first — the "no sign" option prints the magnitude only, though the total and average still use the signed value. If the setting is on minus and the sign still vanished, the source probably marked negatives some other way: a triangle, a red font with no character at all, or a CR/DR suffix. Colour and font carry no information once the text is pasted, so those values arrive as positive with nothing to detect.