You copied a table, pasted it into Excel, and the whole thing is sitting in column A. Or you opened a CSV and got one column of comma-riddled text instead of a grid.
There are five separate problems that all look like "the paste went wrong", and they have five different fixes. Work out which one you have first, otherwise you will fix the wrong thing.
What happened. Excel got plain text, not a table. When you copy from a web page, the clipboard normally carries two versions: HTML and plain text. Excel prefers the HTML one and builds a real grid from it. If the HTML version is missing — because you copied out of a terminal, a code block, a PDF, a chat window, or because the source pasted through a plain-text step on the way — Excel falls back to the text, and text is one string per line.
The fix, for a paste. Select the column, then Data, Text to Columns, Delimited, tick the character that separates your values (usually Tab or Comma), Finish. On Mac the menu is the same.
Before you click Finish, look at the Column data format step. Set any column of IDs, postcodes or part numbers to Text here. This is the one moment where you can stop Excel from mangling them, and problems 3 and 4 below never happen if you use it.
The fix, for a CSV. Do not double-click the file. Use Data, From Text/CSV, pick the file, and Excel shows you a preview with a delimiter dropdown and a per-column type. Choose the right delimiter, set the awkward columns to Text, then Load.
What happened. Double-clicking a CSV does not open a dialog. Excel splits it on the system list separator, which is a comma in the United States and the United Kingdom and a semicolon in Germany, France, Spain, Italy, the Netherlands, Brazil and much of the rest of the world, because those locales use a comma as the decimal point.
So a comma-separated file opens as one column on a German machine, and a semicolon-separated file opens as one column on an American one. Same file, same Excel, different result.
Three fixes, in the order you should try them:
sep=, on a line of its own at the very top of the file. Excel reads that line as an instruction and every other tool ignores it as a stray header.What happened. The value is text that looks like a number. $1,234.50, 4.3%, 1 234,50, (560) for negative. SUM ignores text, so it returns zero, and sorting is alphabetical, so 9 comes after 1,417.
Left-aligned by default means text. Right-aligned means number. That is the quickest check, and it is why a column of numbers that is all shoved to the left is a warning sign.
The fix. Text to Columns again — running it and just clicking Finish converts anything convertible. If that leaves values behind, the cause is usually one of these three:
=VALUE(SUBSTITUTE(SUBSTITUTE(A2, CHAR(160), ""), ",", "")).=LEN(A2) tells you: if it is one longer than the characters you can see, that is what you have. =CLEAN(TRIM(A2)) handles most of them.=SUBSTITUTE(A2, UNICHAR(8722), "-").What happened. Excel guesses a type for every cell as it arrives. 00734 looks like a number, so the zeros go. 1-2 and 3/4 look like dates, so they become 2 January and 4 March. 1E5 looks like scientific notation, so it becomes 100000. SEPT2 becomes a date, which is the reason human genetics renamed several genes.
This is not recoverable afterwards. Once 00734 is the number 734, the zeros are not stored anywhere. You have to re-import.
The fix, in order of preference.
What happened. Merged cells in the source table. A cell with rowspan="3" occupies three rows on screen but exists once in the markup. If whatever copied the table took the markup literally, the second and third rows are short by one cell, and everything after the merge shifts left.
The tell is that the top of the table is right and the bottom is wrong. It often survives unnoticed for a long time, because each row is individually plausible.
The fix. There is no fix at the Excel end — the information about which column a value belonged to is already gone. You have to re-copy with something that expands merged cells into a proper rectangular grid. Excel's own Data, From Web does this. So does pandas.read_html. So does GridPick, which additionally lets you choose whether a merged value repeats down the rows it covered or leaves blanks under the first one.
Most of this comes from the same root cause: the clipboard and the CSV file both throw away the structure that the web page had, and then Excel guesses at it. Every fix above is a way of guessing less.
The way to avoid the whole class of problem is to export a file that carries its own structure — a real .xlsx, where numbers are stored as numbers and text stays text, so there is nothing left for Excel to interpret. Failing that, a CSV with a byte order mark, an explicit delimiter, and quoting on anything that could be mistaken for something else.
GridPick does this for you. It reads real tables and div-based grids, expands merged cells, warns you when a grid is only half loaded, and exports to Excel, CSV, JSON or Markdown. The free version has no row limit; Pro is a one-time payment.