Cleaning Messy Data in Excel: Duplicates, TRIM, Text-to-Columns, Power Query
Working through a deliberately filthy customer export, in the order that actually gets it clean
5 Minutes Read
Cleaning Messy Data in Excel: Duplicates, TRIM, Text-to-Columns, Power Query
Most Excel cleaning tutorials use a tidy practice sheet with one deliberate flaw, a single duplicate row, a single stray space, easy to spot and easy to fix in isolation. Real exports don't arrive that way. They arrive with all of it at once, trailing spaces, duplicate rows that don't look like duplicates because of those spaces, dates in two different formats, and a combined field that should really be two columns. This guide works through one genuinely messy sheet using all four techniques, in the order that actually gets it clean rather than the order that looks cleanest in a tutorial.
What You'll Learn
# | Technique | What it helps you do |
|---|---|---|
1 |
| Standardise messy text and remove unwanted characters |
2 | Remove Duplicates | Eliminate repeated records after cleaning |
3 | Text to Columns | Split combined fields into separate columns |
4 | Date cleaning | Handle inconsistent date formats safely |
5 | Power Query | Turn one-time cleaning into a repeatable workflow |
The data, a customer export:
Customer Name | Full Address | Signup Date | Status |
|---|---|---|---|
Rahul Sharma | 12 MG Road, Bangalore | 03/12/2026 | active |
rahul sharma | 12 MG Road, Bangalore | 2026-03-12 | Active |
Priya Iyer | 45 Anna Salai, Chennai | 15/03/2026 | ACTIVE |
Vikram Nair | 8 Park Street, Kolkata | 03/18/2026 | inactive |
Priya Iyer | 45 Anna Salai, Chennai | 15/03/2026 | active |
Look closely and this table already has three of the four problems this guide covers: in the raw export, Rahul Sharma has a trailing space after the name, while the status values use inconsistent capitalisation. Rows 1 and 2 represent the same customer, but the whitespace difference needs to be cleaned before the records are compared. The trailing space after Rahul Sharma is not visually obvious in the table, which is exactly why this kind of issue is easy to miss in a real export. Priya Iyer also appears twice with inconsistent status capitalisation. And "Full Address" is really two pieces of information, street address and city, stuck together.
Step 1: Why order matters more than the tools
It's tempting to jump straight to Data → Remove Duplicates, since duplicates feel like the obvious first problem. Don't. Clean the text first so that accidental whitespace and other formatting inconsistencies are removed before you compare records. For example, a value such as Rahul Sharma with a trailing space is different from Rahul Sharma when the underlying text values are compared. Standardising the text first makes the duplicate check more reliable and gives you a cleaner dataset to work with.
Show Image
The fix is sequencing, not a better tool. Clean the text first, then deduplicate. That single ordering decision is the difference between this workflow actually working and silently leaving duplicates behind.
Step 2: TRIM and CLEAN first
=TRIM(A2)TRIM removes leading and trailing spaces and collapses multiple internal spaces down to one. Apply it to every text column that might have inconsistent spacing, in this case Customer Name and Status, in a helper column, then paste the results back as values before moving on.
For capitalisation inconsistency, "active" vs "ACTIVE" vs "Active", PROPER or UPPER applied consistently across the column normalises it the same way TRIM normalises whitespace:
=PROPER(TRIM(A2))Nesting TRIM inside PROPER handles both problems in one formula: strip the stray whitespace, then standardise the capitalisation. Run this on Customer Name and Status before doing anything else.
Where this bites people: leaving the cleaned results in helper columns and then removing or changing the formulas later can make the workflow harder to audit or reproduce. Once you've verified the cleaned values, copy the results and use Paste Special → Values to replace the helper formulas with fixed values before deduplicating.
Step 3: Remove Duplicates, now that the text is clean
With the Customer Name and Status cleaned and pasted as values, Data → Remove Duplicates can now correctly identify rows 1 and 2 as the same customer because the trailing whitespace that made the underlying values different has been removed. Select all the columns that should match for a row to count as a true duplicate, not just Customer Name alone, since two different customers could coincidentally share a name.
Where this bites people: selecting every column including a column that's expected to vary, like a row ID or timestamp added during export. If that column is included in the duplicate check, genuinely duplicate customer records won't be caught, because that one column makes every row technically unique. Uncheck columns that aren't part of what actually defines a duplicate here.
Step 4: Text-to-Columns for the combined address field
"Full Address" mixes two pieces of information, street address and city, separated by a comma. Select the column, then Data → Text to Columns → Delimited → Comma, and Excel splits it into two columns at each comma.
Where this bites people: running Text to Columns without enough empty space immediately to the right. Excel can overwrite data in the columns immediately to the right of the original column, so make sure there is enough empty space before running it. Insert blank columns before splitting if necessary.
The mixed date formats in the original table, 03/12/2026 next to 2026-03-12, are a related but separate problem: Text to Columns can help isolate a date string, but fixing an actual mixed-format date column usually needs a combination of DATEVALUE and manual inspection, since Excel can silently misinterpret an ambiguous date like 03/12/2026 as either the 3rd of December or the 12th of March depending on regional settings. When a date column looks inconsistent, check the format explicitly rather than trusting AutoFit to catch it.
Step 5: When this stops being a one-time fix
Everything above works well for a single cleanup. The real limitation shows up the next time this same export lands in your inbox next week, since none of the steps above are remembered; you'd redo every one of them by hand.
This is exactly the problem Power Query solves. The same TRIM, capitalisation, deduplication, and column-splitting logic can be built once inside Power Query's Transform Data editor, and it reapplies automatically every time the underlying file refreshes. For a cleanup you'll only ever do once, the manual steps above are the faster path. For anything recurring, monthly exports, a live data source, weekly reports, building the same logic in Power Query once is the better long-term investment, and it's the same underlying tool that sits inside Power BI too, so the skill transfers directly.
Common mistakes
Deduplicating before cleaning text. Trailing spaces and other text inconsistencies can hide real duplicates from a value-based comparison. Clean the text before running Remove Duplicates.
Leaving cleaning formulas in place when you no longer need them. Once you've verified the results, use Paste Special → Values to freeze the cleaned data and make the final dataset easier to audit and work with.
Including a column that's expected to vary in the duplicate check. A row ID or export timestamp included in the comparison makes every row look unique, defeating the point of deduplicating at all.
Running Text to Columns without enough empty space for the split data. Excel can overwrite data in the columns immediately to the right of the original column, so make sure there is enough empty space before splitting.
Not keeping a copy of the original messy data. Cleaning steps are hard to undo once several are stacked on top of each other; keep the raw export on a separate tab before you start.
Trusting a date column's display format without checking it. A date that displays consistently can still be stored inconsistently underneath, especially after combining two exports from different regional settings.
Where to go from here
This guide covers the manual cleaning toolkit; for the fuller working set of Excel skills these fit into, cleaning, lookups, Pivot Tables, and Power Query together, the Excel for Data Analysis guide is the place to go next. If TRIM, PROPER, and the other formulas used here felt unfamiliar, the Excel formulas guide covers them alongside the rest of the working set analysts use weekly.
Power Query is genuinely the same tool inside Power BI, so if you're heading that direction next, the Power BI for Beginners tutorial picks up cleaning exactly where this guide leaves off, applied to a multi-table model rather than a single sheet.
Quiz
TEST WHAT YOU LEARNED
Question 1 of 15
Q1: Why does this guide recommend cleaning text with TRIM before running Remove Duplicates?
FAQ
FREQUENTLY ASKED QUESTIONS
Because later steps depend on the output of earlier ones. Cleaning whitespace before deduplication ensures that values such as `Rahul Sharma ` and `Rahul Sharma` are treated as the same text value before you compare records. Capitalisation can also be standardised for consistency, but it is not what makes Excel's Remove Duplicates case-sensitive.
TRIM removes leading, trailing, and extra internal spaces. CLEAN removes non-printable characters that sometimes come through in exports from other systems. They solve different problems and are sometimes used together on the same column.
Once you've verified the cleaned results, pasting them as values removes the dependency on the helper formulas and leaves you with a fixed, auditable dataset. Remove Duplicates can compare calculated values, so Paste Special → Values is a workflow choice rather than a requirement for deduplication.
Whatever columns you select in the dialog. Selecting too many columns, including ones expected to vary like a row ID, can make genuinely duplicate records look unique. Select only the columns that actually define what makes two rows the same customer or record.
Text to Columns writes the split pieces into the columns immediately to the right of the original column. If those columns contain existing data, that data can be overwritten, so insert blank columns first or confirm there is enough empty space before running the operation.
Carefully, and not automatically. Excel can misinterpret an ambiguous date depending on regional settings, so a mixed-format date column usually needs manual inspection alongside DATEVALUE, rather than a single formula that fixes everything at once.
For a genuine one-time cleanup, the manual steps are faster. For anything recurring—a report you rebuild monthly or a file that refreshes regularly—Power Query is worth the setup time, since the same cleaning logic reapplies automatically every time.
Yes, it's the same underlying engine in both, so cleaning logic and habits built in one transfer almost directly to the other.
Yes. Cleaning steps are difficult to reverse once several are stacked on top of each other, and having the raw export on a separate tab makes it possible to start over if something goes wrong partway through.
Because this data has both problems at once—inconsistent spacing and inconsistent capitalisation. Nesting the two formulas fixes both in a single step rather than needing two separate helper columns.
Apply it to a helper column first and visually compare a handful of rows against the original before pasting the results back as values and deleting the raw column.
It keeps the first occurrence of a duplicate and removes the rest, so if row order matters—for example, you want to keep the most recent record—sort the data intentionally before deduplicating rather than relying on whatever order it happened to arrive in.
Widen the column and look for text that appears slightly indented from the cell's left edge, or use a formula like `=LEN(A2)` next to `=LEN(TRIM(A2))` and compare the two; a mismatch confirms extra whitespace exists.
Yes, the same problems—inconsistent whitespace, case, duplicates, and combined fields—show up regardless of source. Data pulled from a database is often cleaner by default, but not guaranteed to be, especially if it passed through several systems first.
Power Query, in more depth than the brief mention here, since it turns everything covered in this guide into a repeatable, refreshable process rather than a manual one; the Excel for Data Analysis guide linked above is the place to continue.