How to clean up a contact list with AI

Steps

1. 1

   Make a copy of the file.

   Never clean the only copy. If something goes wrong you can start over.

2. 2

   Look at 20 rows and write down the problems.

   All caps names, full names in one cell, phone numbers in five formats, the same person twice, emails with spaces. That list becomes your prompt.

3. 3

   Decide how big the job is.

   A small list can go straight into the AI. A big list is better cleaned with formulas the AI writes for you, so the data stays in your spreadsheet.

4. 4

   Use a prompt below.

   Paste a sample, tell it the rules, and ask for the exact output.

5. 5

   Spot check.

   Pick ten random rows and compare old and new. Look hard at names like "McDonald" or "de la Cruz", which AI and formulas can get wrong.

6. 6

   Load the clean list.

   Then keep it clean by fixing rows as you go.

Prompts you can copy

Clean a small list

You are cleaning a contact list for a sales team.
Here are the rows, with the header row first: [paste rows from your sheet].
Fix these things:
- Names in normal case, like "Mary Smith", not "MARY SMITH".
- Split full name into First Name and Last Name columns.
- Phone numbers in this format: [for example (555) 123-4567].
- Trim extra spaces. Emails in lower case.
- Do not change company names except to fix extra spaces.
Give the result as a table I can paste back into my spreadsheet.
After the table, list any rows you were not sure about and why.

Get formulas for a big list

I have a contact list in [Excel / Google Sheets] with about [number] rows.
Columns: [list the column letters and what is in each, for example A is Full Name, B is Email, C is Phone].
I want to: [split names into first and last / make emails lower case / format phones / flag duplicate emails].
Give me the exact formulas to put in new columns, one step at a time, with the cell to start in.
Explain each step in one plain sentence. Assume I am not a spreadsheet expert.

Find duplicates and bad rows

Here is a contact list: [paste rows].
Find:
1. Rows that look like the same person, even if spelled a bit differently or with a different email.
2. Emails that look broken, like missing an @ or with spaces.
3. Rows with missing company or missing name.
Give me a short list for each, with the row numbers. Do not delete anything. I will decide.

Example

This is a made-up example. A messy row: "JOHN o'BRIEN, JOHN.OBRIEN@ACMEPIPE.COM , 5551234567, acme pipe & supply"

After the first prompt, a good result is: First Name John, Last Name O'Brien, Email john.obrien@acmepipe.com, Phone (555) 123-4567, Company acme pipe & supply.

Notice the company name was left alone, because the prompt said not to change it. You can fix company names in a second pass once you decide how you want them to look.

Tips

• Give the AI one example of a perfect row. "Make every row look like this one" works better than a long list of rules.
• For big files, use formulas, not copy and paste. Formulas show their work and you can check them.
• Copilot in Excel and Gemini in Google Sheets can help inside the sheet on some plans. If you have them, try them on a copy first.
• Keep the old columns until you are sure the new ones are right. Delete them last.

What not to do

• Do not paste a full customer list with private details into an AI tool unless your company says it is allowed. Use a small sample, or remove the private columns first.
• Do not let the AI "fill in" missing emails or phone numbers. It will guess, and guesses bounce.
• Do not merge duplicates without looking. Two John Smiths at one company can be two real people.

For more on keeping your list in good shape, see data health prompts and CRM prompts.

FAQ

Can I upload my whole spreadsheet to ChatGPT?

ChatGPT and some other tools can read uploaded files on many plans. Only upload customer data if your company rules and the tool's data settings allow it. When in doubt, use a sample or formulas.

What is the safest way to clean a big list?

Ask the AI to write formulas, then run them in your own spreadsheet. Your data never leaves the file.

Can AI check if an email address is real?

No. It can spot emails that look broken, but it cannot tell if an inbox exists. Use an email checker tool for that.

How do I stop the list from getting messy again?

Pick one format for names, phones and companies and write it down. Use the same prompt or formulas every time you add new rows.

Rather have it done for you? We build the system that handles your outreach, one time, and you own it. See how it works.

First posted October 28, 2026. Last updated October 28, 2026. Free to copy and use, CC BY 4.0.