Expert guide

Clean Messy Excel Data with AI: Names, Addresses and Duplicates

Use rules for the mechanical work, AI for the judgement calls, and a review step before anything is overwritten

Free Cost Audit

Call us at +1 (385) 386-3860

Short answer: clean Excel data in three layers. First apply deterministic rules (TRIM, CLEAN, PROPER, text-to-number, date fixes) with formulas or Power Query. Then use AI only for what rules cannot handle: inconsistent company names, free-text addresses, near-duplicate records. Write AI results to a new column, review a sample, then replace the original. For data that arrives every week, build the steps into Excel automation so the cleanup runs itself.

Every business with a CRM export, an e-commerce order file or a list merged from three branches has the same problem: the same customer appears as "ACME Ltd", "Acme Limited" and "ACME LTD.", addresses are a single free-text column, and job titles come in forty spellings. Reports built on that data are wrong in ways nobody notices until a mailing goes out twice or a customer's revenue is split across four rows.

AI is genuinely useful here, but only for part of the job. Sending a whole sheet to ChatGPT and asking it to "clean this" is slow, expensive and unrepeatable. The workflow below is what we use on client data.

Layer 1: Mechanical Cleaning With Formulas or Power Query

These problems have exact answers. Fix them with rules first; AI adds nothing here except cost.

ProblemFormula fixPower Query step
Extra and non-breaking spaces=TRIM(SUBSTITUTE(A2,CHAR(160)," "))Transform → Format → Trim, plus Replace Values for the non-breaking space
Hidden line breaks=CLEAN(A2)Transform → Format → Clean
Inconsistent case=PROPER(A2) or =UPPER(A2)Format → Capitalize Each Word
Numbers stored as text=VALUE(A2) or =--A2Change Type → Decimal Number
Dates as text (e.g. 03.11.2026)=DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2))Change Type → Using Locale
Exact duplicatesData → Remove DuplicatesHome → Remove Rows → Remove Duplicates

If the same file arrives every week or month, do this layer in Power Query, not formulas. You record the steps once and click Refresh next time.

Layer 2: Build a Match Key Before You Touch AI

Most "fuzzy" duplicates become exact duplicates once you normalise them. A match key column removes the noise: case, punctuation, legal suffixes and filler words.

=LET(
  s, LOWER(TRIM(A2)),
  s1, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(s, ".", ""), ",", ""), "&", "and"),
  s2, REGEXREPLACE(s1, "\b(inc|llc|ltd|limited|corp|corporation|co|plc|the)\b", ""),
  TRIM(REGEXREPLACE(s2, "\s+", " ")))

This uses Excel's REGEXREPLACE function, available in Microsoft 365. On older versions, chain SUBSTITUTE calls instead. Sort by the key and you will often find that 70 to 90 percent of apparent duplicates now match exactly. Our Excel REGEX functions guide explains the patterns.

Layer 3: Use AI for the Judgement Calls

What is left after rules and match keys is where AI earns its place: values that mean the same thing but share few characters, and free text that needs to be split into fields.

Standardising company and product names

Send only the distinct values, not every row. If 12,000 rows contain 1,400 distinct company names, you clean 1,400 values once and map them back with XLOOKUP.

You are cleaning a list of company names from a CRM.
For each input line, return:  original | standard name | confidence (high/medium/low)
Rules:
- Use the most common real-world form of the name, e.g. "Johnson & Johnson".
- Remove legal suffixes (Inc, LLC, Ltd) unless they distinguish two companies.
- Do not merge companies you are not sure are the same; mark confidence low.
- Never invent a company that is not implied by the input.
Input:
ACME LTD.
Acme Limited
acme ltd (london)
J&J
Johnson and Johnson Inc

Splitting free-text addresses

Address columns typed by hand defeat TEXTSPLIT because the separators are inconsistent. AI handles them well when you demand a strict output format.

Split each address into: Street | Unit | City | State | ZIP | Country.
Use an empty value where a part is missing. Do not guess a ZIP code.
Return exactly one line per input, pipe-separated, no commentary.

12 oak st apt 4 springfield il 62704
Unit 3, 45 King Street West, Toronto ON M5H 1J8
PO Box 991 Austin Texas

Near-duplicate person records

For people, compare candidate pairs rather than asking AI to scan the whole list. Generate pairs that share a postcode or the first three letters of the surname, then ask the model whether each pair is the same person, with a reason. This keeps the work small and makes every decision reviewable.

Running AI Cleaning Inside Excel

  • Copilot in Excel

    Good for a few hundred values in a table you can see. Ask it to add a cleaned column next to the original.

  • ChatGPT or Claude with copy and paste

    Fine for a one-off list of distinct values. Paste back into a new column and map with XLOOKUP.

  • VBA or Office Scripts calling an API

    For thousands of values or a repeat process. See our VBA example for calling the OpenAI API from Excel.

  • Power Query fuzzy merge

    Not AI, but a built-in similarity match with an adjustable threshold. Excellent for matching a messy list against a clean master list.

The API approach is covered step by step in calling the OpenAI API from Excel VBA.

The Review Step You Must Not Skip

  • Keep the original column untouched. Put the cleaned value next to it.

  • Add a Changed? column: =IF(EXACT(A2,B2),"","changed").

  • Review every low-confidence result and a random sample of 50 others.

  • Check merges by revenue: if two customers merged, did a large account just absorb a small unrelated one?

  • Only then copy cleaned values over the source, and keep the mapping table for next time.

The mapping table is an asset. Next month, most of the messy values will already be in it, so you only send genuinely new values to AI.

Fix the Source, Not Just the Symptom

If you clean the same mess every month, the real problem is upstream: free-text entry where a drop-down belongs, three systems with no shared customer ID, or exports merged by hand. We fix those with validated entry forms, automated consolidation, and, when the data has outgrown a spreadsheet, a proper database. See converting Excel to an Access database for that path.

FAQ

AI data cleaning in Excel FAQ

Answers for teams cleaning customer, product and address data

Yes, for the parts of cleaning that need judgement: standardising company names, splitting free-text addresses, fixing inconsistent job titles, and spotting near-duplicates such as 'ACME Ltd' and 'Acme Limited'. Mechanical cleaning such as trimming spaces, fixing case and converting text to numbers is faster and more reliable with Excel formulas or Power Query.

First create a normalised key column: lower case, trimmed, punctuation removed, and common suffixes like Inc, LLC and Ltd stripped. Exact duplicates on that key catch most cases. For the remainder, Power Query's fuzzy merge or an AI model comparing candidate pairs finds matches like 'Jon Smith, 12 Oak St' and 'John Smith, 12 Oak Street'.

Use Power Query for repeatable rules that run every time new data arrives. Use AI for one-off judgement calls or columns of free text that rules cannot handle. The best setups combine both: Power Query does the mechanical steps, AI handles the ambiguous remainder, and a person reviews low-confidence results.

Only under a business agreement that prevents your data being used for training, and only the columns that need cleaning. You can often send company names or addresses without the linked contact details, emails or financial figures. Check your organisation's AI policy and any regulatory duties first.

Keep the original column, write the AI result in a new column, and add a third column flagging where they differ. Review a random sample of 50 changes, plus every change the model marked as low confidence. Never overwrite source data until the sample passes.