How to clean a messy CSV or Excel file

Most messy exports fail in the same few ways. Work through them in this order and the file usually becomes something you can total, filter and send on.

1. Fix the headers first

Put one header row at the top, one name per column, no blank or merged header cells. Trim extra spaces and use the same name for the same thing, so not "Amount", "amt" and "Total ($)" in one file.

2. Open CSV files with the right encoding

If accented letters turn into strange symbols, the file is probably UTF-8 and Excel opened it with another encoding. In Excel use Data, then From Text/CSV, and choose UTF-8 as the file origin instead of double-clicking the file.

3. Convert numbers stored as text

A green triangle in the corner of a cell, or numbers aligned to the left, usually means text. Select the column and use the warning menu to convert to number, or use Data, then Text to Columns, then Finish. Remove currency symbols first if they stop the conversion, and check whether your source uses a comma or a point as the decimal mark.

4. Make dates real dates

A date like 03/04/2026 means 3 April in one country and 4 March in another. Decide which your source uses before converting, and spot-check a date whose day is above 12. Dates that stay left-aligned after conversion are still text.

5. Trim spaces and find duplicates

Use the TRIM function on text columns, then look at duplicates with conditional formatting (Duplicate Values) before you use Data, then Remove Duplicates. Keep a copy of the original so you can compare.

When to hand it off

If the file is large, the layout changes partway down, or you want totals and a record of every change, send it to us. You get one formatted .xlsx workbook with consistent headers, fixed data types, a duplicates sheet, total formulas on the columns you name and a change report. Up to 10 MB and 20,000 rows per file. It is an AI-assisted process with automated checks, not a human review, and it is not accounting or tax advice.

See the service and what is not covered

Written by Hexloom Labs with AI assistance. Not legal, tax or accounting advice. Check the steps against your own software version.