Clean Imported CSV Data in Excel: Fix 7 Common Problems

CSV files are plain text, so Excel must guess the delimiter, encoding, and data type when it opens one. A quick check after import prevents shifted columns, dates that sort incorrectly, and numbers that formulas ignore.

1. Import the CSV with the right settings

Use Data > From Text/CSV instead of double-clicking the file. In the preview, choose the delimiter used by the file (usually comma or semicolon), then choose Transform Data for a repeatable Power Query cleanup or Load for a one-time import. Confirm that the first row is headers and that the preview columns line up.

If a file is already in one column, select it and use Data > Text to Columns > Delimited, select the correct delimiter, and choose a destination that will not overwrite the source.

2. Fix columns that shifted or merged

Column shifts usually mean the delimiter or quote setting is wrong. In the import preview, test comma, semicolon, tab, and a custom delimiter. Text containing commas must be enclosed in quotes; Power Query handles quoted fields more reliably than pasting into a worksheet.

Keep the original file unchanged. Compare a few rows with the source, especially addresses and notes that contain punctuation, before deleting the raw sheet.

3. Repair dates and numbers

Formatting a text value does not convert it. Select a suspect column and look for the green warning indicator or test it with =ISNUMBER(A2). For text numbers, follow Excel Numbers Stored as Text or convert with Text to Columns > Finish.

Protect identifiers such as 00127 from losing leading zeros. In the import preview, set that column to Text (or in Power Query choose Text as its type) before loading. If the zeros have already disappeared, recover them only when the required width is known, for example =TEXT(A2,"00000") for a five-digit ID.

For dates, use Text to Columns and select the source order (for example, MDY or DMY), or use =DATEVALUE(A2) when the text is consistent. Apply one display format after conversion and sort the column to verify that it is chronological.

In Power Query, set each column's data type explicitly after selecting the correct locale. This prevents a US date from being interpreted as a day-month date on another machine.

4. Remove hidden spaces and characters

Imported keys often contain leading spaces, trailing spaces, line breaks, or non-breaking spaces. In a helper column use:

=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))

Fill down, copy the results, and paste values over the original only after checking a sample. See Remove Leading and Trailing Spaces in Excel for explanations of each character and safe cleanup patterns.

5. Find and remove duplicate rows

First decide which columns define the same record. Clean the key columns, then use Data > Remove Duplicates and keep a backup of the raw import. For a review before deletion, use How to Remove Duplicates in Excel and its duplicate-check formula. If labels need standardizing first, use Find and Replace in Excel.

6. Handle garbled characters (encoding)

Names such as é instead of é indicate an encoding mismatch. Re-import through Data > From Text/CSV and choose 65001: Unicode (UTF-8) in File Origin. If the source uses a regional legacy encoding, select that encoding instead. Do not repair mojibake with Find and Replace until the import encoding is correct.

7. Treat empty values deliberately

Blank IDs should be flagged for review, not guessed. For optional text, use N/A only when that value has a defined meaning. For numeric blanks, decide whether blank means zero, unavailable, or pending. In Power Query, use Replace Values or Fill Down only when the source rule supports it; otherwise add a status column such as =IF(A2="","Needs review","OK").

A repeatable Power Query workflow

For weekly exports, choose Transform Data and apply steps in this order: select delimiter and encoding, promote headers, remove empty rows, trim and clean text columns, set date and number types, remove duplicates, then load the result. The query records each step, so Refresh applies the same cleanup to the next CSV. For a one-off file, Text to Columns and helper formulas are faster.

Final CSV checklist

For the broader cleanup sequence, continue with Excel Data Cleaning. If a lookup still fails after import cleanup, check XLOOKUP Not Working.