Clean Up CSV Data in Google Sheets (Import Damage Included)

The Old Way (Import, Then Reformat)

Take one honest line out of a real export.

Run File, then Import, then Upload, with the default option to convert text to numbers and dates left checked. Four values, four different failures.

The ZIP code 02138 arrives as 2138. The part number 12-14 arrives as a December 14 date serial. The sixteen digit account number arrives as 1.23457E+15, because Sheets stores numbers as doubles and loses precision past fifteen digits. And if you were splitting the raw line yourself with SPLIT, the quoted name breaks into two columns.

SPLIT treats the comma as a literal delimiter. It has no idea that quotes are supposed to escape it, so Smith, John becomes "Smith in one cell and John" in the next.

The usual second move is selecting the column and choosing Format, then Number, then Plain Text.

That does nothing. Cell formatting changes how a value is displayed, not what is stored. The leading zero and the sixteenth digit were discarded at parse time, before the cell ever had a format. You are formatting a number that is already wrong.

Fix the Import First

Import damage is not a formula problem, so no formula solves it. Re-import with the conversion turned off.

1. Open File, then Import, and pick your CSV.
2. Uncheck "Convert text to numbers, dates, and formulas".
3. Import to a new sheet, not over the damaged one.
4. Confirm your ZIP codes and long IDs came in intact before doing anything else.

Everything now arrives as text. That is the point. You can always convert a clean string to a number later, but you cannot recover a digit that was never stored.

The FITS Way (For the Mess That Was Already There)

Now you have the second problem: the file itself is inconsistent. Dates in three formats, states as both CA and California, currency with and without symbols, trailing notes glued onto values.

That is where FITS earns its place, because you describe the target shape instead of anticipating every variation.

Notice the pattern in each of those. Every prompt names an explicit fallback string. That matters more on imported data than anywhere else, because a CSV always contains a few rows that are genuinely garbage. You want those flagged in the output, not silently guessed at.

For the quoted comma case, describe the fields rather than the delimiter.

Run a first pass over a hundred rows before you commit to the whole file. Sort by the fallback values, and you have an exception list instead of a spreadsheet you have to eyeball.

The Short Version

Re-import with conversion off, then clean with formulas. Doing it in the other order costs you data. From there, the individual jobs are covered separately: standardizing mixed date formats, trimming extra spaces, removing special characters without killing accents, and splitting text when the separators are mixed. The full tour is in automating Google Sheets tasks you used to need regex for.