Clean up a messy contact list with formulas the assistant writes for you

Every list that passes through a few hands ends up the same way: names in capitals, “ana ruiz “ with a space at the end, one person twice under two spellings of an email, phone numbers in five formats. It might be an event sign-up export, a volunteer or donor list, a staff list, a class roster or a customer file. By hand, that’s the same handful of corrections on each of 400 rows, and a tired eye still misses row 312.

A spreadsheet applies one fix to every row at once, and formulas are something AI assistants write well. What they do badly is copy hundreds of rows faithfully: ask for your whole list back, cleaned, and the assistant can drop, merge or retype rows without saying so. So the assistant never sees your list: it gets your headers and five rows you make up, writes the formulas, and the spreadsheet does the rest. For a 20-row list, skip all this and fix it by hand.

By the end of this you’ll have a clean list ready for a mail merge or an import, plus a Recipe tab that turns next month’s cleanup into a quick repeat. Set aside about an hour, and note the time you start.

What you’ll be able to do

• Finish today with a clean contact list, one format per column and each person listed once.
• Get working formulas for Excel or Google Sheets, in your interface language, from five made-up rows.
• Repeat the cleanup on the next export from your Recipe tab, and time it against today.

What you need

• Excel (Microsoft 365, a recent desktop version or Excel for the web) or Google Sheets.
• A chat assistant such as Claude, ChatGPT, Gemini or Copilot; a free account is enough.
• A messy list. To practice first, generate one with prompt 1 below. With a real list, the assistant only ever sees your headers and five invented rows; Before you upload a client document explains why real contact details stay out of the chat.

Steps

1. Keep the original untouched. Name the tab with your list Original. Select everything (the box between A and 1), copy it and paste it into cell A1 of a new tab called Working. Note the number of the last row with data.

2. Let the built-in tools go first (menu names checked in October 2026):

   • Extra spaces: in Google Sheets, select the data and choose Data > Data cleanup > Trim whitespace. In Excel, that’s a formula in step 5.
   • Two items in one cell with a reliable separator, such as “Ruiz, Ana”: Data > Split text to columns in Google Sheets, Data > Text to Columns in desktop Excel (Data > Split Text to Columns in Excel for the web). Leave the columns to the right empty, because the split writes over them.
   • A pattern you can show by example, such as a first name taken from a full name: in Excel, type the first answer or two in the next column and choose Data > Flash Fill or press Ctrl+E (also in Excel for the web). In Google Sheets, type a few examples and press Ctrl+Shift+Y (⌘+Shift+Y on a Mac) for Smart Fill. Both guess, so check the compound names.
   • Duplicates wait until step 7: an email with a stray space doesn’t match its twin until it’s clean.

3. List what’s left, and make up five rows that show it. Turn on a filter (Data > Filter in Excel, Data > Create a filter in Google Sheets) and open each column’s list of values, where variants sit side by side. Tick what you find on the checklist below. Then invent five rows with the same problems in the same shape (a name in capitals stays in capitals), with quotes around cells that have extra spaces.

4. Ask for the formulas. Fill in prompt 2 and paste it into a new chat. To see whether your formulas use commas or semicolons, look at any formula already working in your files.

5. Give each formula its own helper column. On Working, leave one empty column after your data, then put each formula in row 2 of its own column, under a header such as “Email (clean)”, and copy it down to the last row. The worked example shows a good answer.

6. Check before you trust it.

   • The last row on Working still matches the number you noted.
   • Filter each clean column for blanks: the original cell should be blank in those rows too.
   • Compare ten rows with Original, spread from top to bottom, plus every kind of value the assistant said its formulas might get wrong.
   • Filter the name-changed column for Check and read those names: they’re the only ones the capitalization rule touched, so the only ones it could have spoiled. Type the right spelling over any wrong one.
   • Filter the copies column for values above 1 and sort by email. Where one copy has something the other lacks, such as a phone number, add it to the other copy’s original columns so both rows are complete. Count the extra copies, one per pair.

7. Freeze the results and remove the duplicates. Copy the clean columns, plus any that needed no cleaning, and paste them as values into a new tab called Clean, in the order your import expects: Home > Paste > Values in Excel (Paste Values in Excel for the web), Ctrl+Shift+V in Google Sheets (⌘+Shift+V on a Mac). Move rows with no email to a tab of their own, since a cleanup by email can’t tell those people apart. Then remove duplicates on the email column only (Data > Remove Duplicates in Excel, Data > Data cleanup > Remove duplicates in Google Sheets), saying the data has headers. The row count should drop by exactly the extra copies you counted.

8. Save the recipe and stop the clock. On a tab called Recipe, note the built-in tools you used, then one row per helper column: its header, the formula with an apostrophe in front so it stays as text, and what to check by hand. Export the Clean tab in the format your mail merge or import asks for, and note the time. Next month, in a copy of this file, put the new export in Original and Working, rerun the recipe and compare the two times.

Two prompts and a checklist to copy

Prompt 1, a practice list:

Make a practice contact list of 30 made-up people, with columns Full name, Email, Phone and Organization. Make it messy the way real lists are:
- names in ALL CAPITALS, in lowercase, and with extra spaces before, after or between words
- some names of three or more words: compound first names, surnames with "de", "van" or "Mc"
- emails with capital letters, and three people who appear twice with their email in different capitals
- phone numbers in at least five formats (spaces, dashes, dots, parentheses, country codes), a few starting with 0
- two rows with no email
Use invented names and organizations, emails ending in example.com or example.org, and phone numbers containing 555. Give me only a table I can copy into a spreadsheet.

Paste it into cell A1 of a new spreadsheet. If it lands in a single column, ask for comma-separated text instead and use the split tools from step 2. If a phone number lost its leading 0 on the way in, you’ve just met the last problem under What can go wrong.

Prompt 2, the formulas:

I'm cleaning a contact list in [Excel for Microsoft 365 / Excel 2021 / Excel for the web / Google Sheets]. The interface is in [language], and my formulas separate arguments with [commas / semicolons].

The list is on a tab called "Working", headers in row 1, data in rows 2 to [last row]:
A: [header]
B: [header]
C: [header]
D: [header]

Five made-up rows with the same problems as my real data (quotes show extra spaces):
[your five rows]

What I need fixed:
[the problems you ticked, in your own words]

For each fix, give me one formula for row 2 to put in a helper column, starting at column [letter], and copy down. Rules:
- Only functions my program and version have, with the function names of my interface language.
- Read from the original columns; never overwrite them.
- Leave the cell empty when the source cell is empty.
- Change only what the rule covers. Change the capitals of a name only when it is written entirely in capitals or entirely in lowercase.
Also add a column counting how many times each clean email appears, and one marking every name the capitalization rule changed.
Then tell me which kinds of values each formula would get wrong, so I can check those rows by hand. If anything about my data is unclear, ask me first.

The checklist:

[ ] Spaces before, after or doubled inside a cell
[ ] Spaces that survive TRIM (text copied from web pages or emails)
[ ] Names in all capitals or all lowercase
[ ] Full name in one cell, when the import wants first and last name apart
[ ] Emails with capitals or a stray space
[ ] The same person more than once
[ ] Phone numbers in several formats, some with a country code
[ ] Phone numbers that lost a leading 0
[ ] Rows with a name but no email, and empty rows

A worked example

Five made-up rows from a volunteer list, in columns A to D:

Full name | Email | Phone | Organization
"  ana ruiz" | "Ana.Ruiz@example.com " | (555) 010-4417 | Northside Library
"KEVIN MCDONALD" | kevin.mcdonald@example.org | 555.010.2290 | Harbor Tech
"Lucía de la Cruz" | LDELACRUZ@EXAMPLE.COM | +1 555 010 7765 | Lakeview Food Bank
"Ana Ruiz" | ana.ruiz@example.com | 5550104417 | Northside Library
"Tom  Becker" | tom.becker@example.net | 555-010-3381 | Becker & Sons

For Excel or Google Sheets in English, with commas, a good answer looks like this:

F  Name, spaces fixed  =TRIM(SUBSTITUTE(A2,UNICHAR(160)," "))
G  Name (clean)        =IF(OR(EXACT(F2,UPPER(F2)),EXACT(F2,LOWER(F2))),PROPER(F2),F2)
H  Email (clean)       =LOWER(TRIM(SUBSTITUTE(B2,UNICHAR(160)," ")))
I  Phone (digits)      =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2," ",""),"-",""),".",""),"(",""),")","")
J  Copies of email     =IF(H2="","",COUNTIF(H:H,H2))
K  Name changed        =IF(EXACT(F2,G2),"","Check")

On these rows:

• F turns invisible non-breaking spaces (character 160) into ordinary ones before TRIM removes the extra spaces.
• G fixes capitals only where a name is all capitals or all lowercase: “Lucía de la Cruz” stays as typed, and “KEVIN MCDONALD” becomes “Kevin Mcdonald”, which is wrong and gets fixed by hand in step 6.
• H turns rows 1 and 4 into the same ana.ruiz@example.com, so J shows 2 for each. K marks rows 1 and 2 for a look.
• I leaves Lucía’s +1 in place, so the column still mixes two forms. Pick a rule (“every number without a + is in the US”) and ask for a formula that adds the code.

What can go wrong and how to check

Formulas for another app, version or language

A #NAME? error usually means a function your program doesn’t have or spells differently; a formula Excel refuses outright often has the wrong separator. TEXTSPLIT, for example, exists in Excel for Microsoft 365 and Excel 2024 but not in Excel 2021, and Google Sheets has SPLIT instead. Paste the formula and the exact error back into the chat and ask for a version that works in yours.

Names that PROPER gets wrong

PROPER capitalizes the first letter of each word and lowercases the rest, so “VAN DER BERG” becomes “Van Der Berg”. That’s why step 6 has you read every name the rule changed, watching for particles and prefixes such as de la, van der, Mc and Mac. No formula knows how each family spells its name; that part is yours.

Invisible spaces and hidden duplicates

Text copied from web pages and emails can carry non-breaking spaces, which TRIM doesn’t remove in either app, and neither does Trim whitespace in Google Sheets. An email that looks right then fails to match its twin. =LEN(B2) gives them away by counting more characters than you can see; the SUBSTITUTE with UNICHAR(160) in the example fixes them. No formula catches someone who signed up with two different addresses, so if that matters, sort by the clean name and look.

Phone numbers that lose a leading zero

A spreadsheet reads 02014455 as a number and drops the 0 when you type, paste or import it. Format the phone column as text before the data goes in: in Excel, Format Cells (Ctrl+1 on Windows), then Number > Text; in Google Sheets, Format > Number and the plain text option. That won’t bring back zeros already lost, so take those numbers from the original export instead of guessing.

Where to go next

Your Clean tab is ready for its mail merge, and next month starts from the recipe instead of from scratch. From here:

• Describe the formula you need in plain words takes the same describe-and-test method to lookups, overdue flags and counts. Pick the next step in your spreadsheet that you still do by hand.
• With each person listed once, a pivot table can count sign-ups by organization or event; A monthly summary you build once and refresh each month sets one up.
• If the next list starts as a pile of PDF forms, From a stack of PDFs to a checked spreadsheet turns them into rows like these without retyping.
• So the next list arrives clean, A request form that fills a spreadsheet collects sign-ups in separate fields and drop-down lists, one answer per column.