How to Clean a Messy Export in Five Steps

Before you fix anything in an exported file, make the problems visible. Most of what's wrong with a bad export is invisible on screen — a trailing space, a date that's really text, a number that won't add up. Four diagnostic columns will find nearly all of it in about two minutes:

=LEN($A2)                        how long is this really?
=ISNUMBER($C2)                   is this date a date?
=ISNUMBER($D2)                   is this number a number?
=COUNTIF($A$2:$A2, $A2)>1        have I seen this before?

Then work through the five steps below, in this order. The order is the part that matters — done in the wrong sequence, each step undoes the one before it.

Six faults on the left-hand data, and only one of them is visible without the columns on the right.

Step 0: work on a copy

Save the original export untouched, somewhere you won't edit it. Every step below is destructive, and the moment you realise you've mangled something is always after the point where Undo still reaches.

This costs ten seconds and it is the only step here with no downside.

Step 1: fix the shape

Before anything else, the sheet has to be a proper table: one header row, one record per row, no merged cells, no blank rows, no totals in the middle.

Merged cells break sorting, filtering and every formula that references a range. Select all, Home → Merge & Center to unmerge, and you'll be left with values in the top-left cell of each merge and blanks below.

To fill those blanks in one go:

1. Select the column.
2. F5 → Special → Blanks → OK. Only the empty cells are now selected.
3. Type = then press the up arrow — this points at the cell above.
4. Press Ctrl+Enter instead of Enter.

Every blank fills with the value above it, in one action. It's the single most useful thing in this article if you deal with exports that came out of a report designer.

Blank rows are worse than they look: they stop Ctrl+Down, break the range a Table would auto-detect, and split what looks like one dataset into several. Find them with F5 → Special → Blanks and delete the rows.

Do this first because everything after it assumes one record per row.

Step 2: clean the whitespace

Now the invisible characters. This is the step people skip, and it's the reason lookups fail later.

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

Three functions because there are three different problems:

Function	Removes
TRIM	Leading, trailing and doubled ordinary spaces
CLEAN	Non-printing control characters, common in CSV exports
SUBSTITUTE(…, CHAR(160), " ")	Non-breaking spaces

⚠️ The last one is the one to remember. TRIM does not remove non-breaking spaces. They look exactly like ordinary spaces, they arrive with anything copied from a web page or a PDF, and a value carrying one will fail every comparison while appearing perfectly normal. Your LEN column is what exposes it — a reference that should be eight characters reporting nine.

Do this before step 3, because a number with a trailing space cannot be converted to a number.

Step 3: fix the data types

Two problems, one cause: the export wrote everything as text.

Numbers stored as text

They sit to the left of the cell, they're ignored by SUM, and the status bar shows no total when you select them.

Select the column, then Data → Text to Columns → Finish. It sounds like it does nothing; it re-parses every cell in place and is the fastest fix there is.

Where that fails — usually thousands separators or a currency symbol embedded in the text — strip the offending characters first:

=VALUE(SUBSTITUTE(SUBSTITUTE($D2, ",", ""), "£", ""))

Dates stored as text

Same test, same first fix. Text to Columns handles most of them, and the ones it won't are usually a day-month order the machine's locale doesn't expect — 03/09/2026 read as 9 March in a US locale, or refused outright.

When that happens, use the third page of the Text to Columns wizard: set the column type to Date and pick the order the data is actually in. It's the only place Excel lets you tell it explicitly.

This step goes before duplicates because 1150 and "1150" aren't duplicates to Excel, and neither are two dates written differently.

Step 4: deal with the duplicates

Only now — with whitespace gone and types fixed — is a duplicate check meaningful. Run it before this point and you'll miss every pair that differed only by a trailing space.

Highlight them rather than removing them:

=COUNTIF($A$2:$A2, $A2)>1

The expanding range colours only the second and later copies, so the first of each pair stays clean and you can see which is which. The full explanation is here, and removing them safely is here.

Read them before deleting. A repeated reference in an export is sometimes a genuine duplicate row and sometimes two real records that share a reference — and those need a conversation, not a delete key.

Step 5: freeze it and lock it down

Your cleaned data currently lives in formulas pointing at the messy original. Delete the original and it all collapses.

1. Select the cleaned columns, copy, then Paste Special → Values over themselves.
2. Now delete the raw columns.
3. Add drop-downs to any column that should only hold certain values.
4. Format as a Table with Ctrl+T, so ranges grow with the data.

Steps 1 and 2 are in that order for a reason: paste as values before deleting, not after.

What the diagnostics won't catch

Look at the ninth row of the example. The customer is "halcyon retail" where every other row says "Halcyon Retail". None of the four checks flags it — the length is right, the types are right, the reference is unique.

Inconsistent capitalisation, abbreviations ("Ltd" against "Limited"), and the same entity entered under two spellings are invisible to formulas and obvious to a person. Sort the column alphabetically and read down it once. Variants land next to each other, and five minutes of scrolling finds what no amount of checking can.

PROPER will fix straightforward capitalisation, with the usual caveat that it renders "McDonald" as "Mcdonald".

If this export arrives every month

Then stop doing it by hand.

Power Query (Data → Get Data, built into Excel since 2016) records the cleaning steps as a recipe and replays them against next month's file in one click. It handles everything above — trimming, type conversion, removing blank rows, splitting columns — and it does it identically every time, which manual cleaning does not.

It takes an afternoon to learn and it's the right answer for anything recurring. The manual method in this article is for the file that landed on your desk today.

Does this work in Google Sheets?

Mostly. TRIM, CLEAN, SUBSTITUTE, VALUE, LEN, ISNUMBER and COUNTIF all behave the same.

The differences: there's no Text to Columns → Finish trick (use Data → Split text to columns, or multiply the column by 1), and no F5 → Special → Blanks. Sheets does have =TRIM() under Data → Data cleanup → Trim whitespace, applied to a selection in one action, which is neater than Excel's equivalent.

When it goes wrong

Two values look identical and still don't match

Compare =LEN() on both. A difference of one is almost always a non-breaking space, which TRIM won't touch.

Text to Columns did nothing

The cells are formatted as Text. Set the column to General first, then run it again.

Dates converted, but to the wrong dates

Day and month were swapped by the locale. Undo, and redo it through the wizard specifying the order explicitly. If some rows converted and others didn't, it's worse than it looks — the ones that converted may be silently wrong.

#VALUE! after wrapping things in VALUE()

Something in the column isn't a number at all — a blank, a dash, or "N/A". Wrap it: =IFERROR(VALUE(A2), A2).

Everything broke when I deleted the raw columns

The cleaned columns were still formulas. Undo, paste as values, then delete.

Common questions

Can I do all five steps in one formula?

Some of them, and you shouldn't. Separate columns let you see which step introduced a problem. Collapse them once the result is right.

How do I know the clean version is correct?

Check the row count and the column total against the original. If the export came with a stated total, match it. If both agree and the diagnostics are clear, you're done.

What if the file is too big to work with?

Past a few hundred thousand rows, formulas across every column become painful. That's Power Query territory, not a helper-column job.

Should I keep the diagnostic columns?

Delete them once the data is clean, but keep the formulas somewhere — a scratch sheet, or your notes. You'll want them again next time.

The short version

• Copy the file first. Everything below is destructive.
• Make the invisible visible: LEN, ISNUMBER, and a duplicate count.
• Shape first — unmerge, fill blanks with F5 → Special → Blanks and Ctrl+Enter, remove blank rows.
• Whitespace second. TRIM alone misses non-breaking spaces; CHAR(160) is the one that hides.
• Types third. Text to Columns → Finish fixes most of it in one action.
• Duplicates fourth, because the first three steps change what counts as a duplicate.
• Paste as values before deleting anything.
• Sort and read the text columns yourself. Capitalisation and spelling variants are invisible to formulas.
• If it's monthly, learn Power Query instead.

Next steps: once the data is clean, the things you couldn't do before become straightforward — matching it against another list, pulling values from a reference sheet, or totalling it by category. All three fail on data that hasn't been through this.