Cleaning messy data in Excel: TRIM, text functions, Remove Duplicates, Flash Fill and data types

Data rarely arrives ready to use. A CRM export has names in capitals, a supplier sheet has prices stored as text, and a list copied from a web page carries spaces you cannot see. Every formula and chart built on that data inherits the mess, and the errors are usually quiet: a total that is slightly low, a customer counted twice, a lookup that fails for one region.

This guide covers the Excel tools that fix most of it: text functions such as TRIM and CLEAN, Remove Duplicates, Flash Fill, and the steps for turning text into real numbers and dates. It also covers the order to do them in, because the order matters more than most people think.

Start with a copy and a quick inspection

Microsoft’s top ten ways to clean your data opens with the step people skip: keep the original. Save the raw export in its own workbook or sheet and clean a copy. Several of the tools below change data permanently, and you will want to compare before and after.

Then spend five minutes looking before you change anything. Sort each important column both ways to see the odd values at the top and bottom. Apply a filter and scan the dropdown lists for near-duplicates such as “Mumbai”, “mumbai” and “Mumbai “. Check that each column holds one kind of thing and that there are no blank rows in the middle. Microsoft recommends a simple tabular layout, with one type of data per column, and converting the range to an Excel table where you can.

Fix the text: TRIM, CLEAN, case and SUBSTITUTE

Most text problems fall into a few groups, and each has a function.

• TRIM removes extra spaces, leaving single spaces between words and none at either end. It removes the standard space character only.
• CLEAN removes the first 32 non-printing characters in the 7-bit ASCII code (values 0 to 31), the kind that come from line breaks and older systems.
• UPPER, LOWER and PROPER standardise capitalisation. PROPER turns “PRIYA SHAH” into “Priya Shah”.
• SUBSTITUTE replaces one piece of text with another. Use it for characters TRIM and CLEAN do not touch.

The character that causes the most confusion is the non-breaking space, value 160. Web pages use it often, it looks exactly like a normal space, and Microsoft’s TRIM page confirms that TRIM does not remove it. The fix is to convert it first, then trim:

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

Microsoft’s cleaning guide lists value 160 among the higher Unicode characters you can replace with SUBSTITUTE so that TRIM and CLEAN can finish the job. For text, Find and Replace (Ctrl+H) is often faster than a formula, for example to strip a prefix such as “SKU-” from every code.

Make numbers and dates real

A number stored as text looks like a number but does not behave like one. It can sort in the wrong order and drop out of totals. Excel often marks these cells with a small green triangle in the corner. Microsoft’s page on converting numbers stored as text gives two main fixes: select the cells, click the warning icon and choose Convert to Number, or use =VALUE(A2) in a helper column and paste the results back as values. If the green triangles do not appear, background error checking may be switched off in File, Options, Formulas.

Dates are the same problem with higher stakes, because a date stored as text cannot be filtered by month or used in date maths. Watch for mixed formats in one column, especially in files shared between the UK and other countries where day and month may be swapped. Fix dates at the start, before anything depends on them.

Splitting and joining columns belongs here too. Text to Columns, or LEFT, MID and RIGHT, can split a combined field. The ampersand or TEXTJOIN can join fields back together.

Flash Fill: clean by example

Flash Fill watches what you type and repeats the pattern. Type “Priya” next to “Priya Shah”, start typing the next first name, and Excel offers to fill the rest of the column. If no preview appears, run it from the Data tab or press Ctrl+E. It works well for splitting names, extracting codes, and reformatting phone numbers or IDs.

Two cautions. Flash Fill guesses, so check the results on rows that look different from your examples, such as people with middle names. And it writes values, not formulas. ICAEW’s Excel Community describes this as the significant limitation of Flash Fill: it can only create static text. When the source data changes, the filled column does not. For a one-off clean that is fine. For a report you refresh every week, use a formula or Power Query instead.

Remove duplicates, carefully

Remove Duplicates on the Data tab deletes rows whose values match in the columns you choose. Microsoft is clear that the deletion is permanent, and suggests filtering for unique values or using conditional formatting first to see what will go.

The details matter. Excel compares what is displayed, not what is stored, so the same date shown as “12/8/2017” in one cell and “Dec 8, 2017” in another counts as unique. A value formatted as 1.00 is not a duplicate of the same value formatted as 1. That is why you clean text, numbers and formats before removing duplicates, not after. Also decide which columns define a duplicate. Two orders from the same customer on the same day are not duplicates if the order IDs differ.

A worked example (illustration only)

A consultant receives a 2,000-row lead list merged from three event sign-up sheets. A first look shows names in mixed case, emails with trailing spaces, a “Company size” column holding numbers as text, and some people listed twice.

1. Copy the raw sheet to a new tab called Raw and work on the copy.
2. Add helper columns: =PROPER(TRIM(SUBSTITUTE(A2,CHAR(160)," "))) for names and =LOWER(TRIM(B2)) for emails.
3. Convert Company size with Convert to Number, then sort it to spot anything still wrong.
4. Paste the helper columns over the originals as values and delete the helpers.
5. Filter for unique values on the email column to preview the duplicates, then run Remove Duplicates on email only.
6. Note the starting row count, the final count and each step in a short log on a Notes tab.

Lowercasing the emails before removing duplicates is the step that matters. Without it, “Priya@Example.com” and “priya@example.com” would both survive.

Common mistakes and when to stop using these tools

• Cleaning the only copy, then finding that a step removed rows you needed.
• Removing duplicates before standardising case, spaces and formats.
• Assuming TRIM fixed everything when non-breaking spaces remain.
• Using Flash Fill in a recurring report and forgetting the results never update.
• Leaving helper formulas pointing at columns you later delete.

These tools are right for one-off or occasional clean-ups of a single file. If you clean the same export every week, or combine several files, the manual steps become a cost and a risk, because every repeat is another chance to miss one. That is the point to move the same steps into Power Query, where they are recorded once and replayed on each refresh.