Clean Phone Numbers, Emails & Dates in Excel: One Consistent Format for Every Field

Phone numbers, email addresses, and dates are the most format-sensitive fields in any dataset. "(555) 123-4567", "555-123-4567", and "5551234567" are the same number written three ways. This tutorial makes each field one consistent format — with an AI prompt or Excel's own tools.

Table of Contents

• Clean Phone Numbers
• Clean Emails
• Convert Date Formats

Clean Phone Numbers

What it is. Converting every phone number to one consistent format, no matter how it was typed.

Why it is useful. Mixed formats never match each other — the same number appears three different ways.

Do it with AI

The prompts.

• To display nicely: "Format all phone numbers as (555) 123-4567."
• To make matching easy: "Keep only the digits of every phone number."

Every row becomes "(555) 123-4567".

Do it manually in Excel

• Flash Fill (Ctrl + E): type the desired format in the next column once, press Ctrl+E, and Excel copies the pattern.
• Digits only (SUBSTITUTE chain): =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"-","")," ",""),"(",""),")",""),"+","") → 5551234567.

Verify

Ask Copilot "Which numbers did you change?" — numbers with country codes or extensions can get mangled. Manually, spot-check the first and last rows.

Common mistake. Not saying whether you want digits-only or a formatted look — the AI guesses and you get mixed output. Decide first, then state it.

Best practice. Keep phone numbers as text — numeric storage drops leading zeros and shows scientific notation on long numbers.

Clean Emails

What it is. Removing spaces, stray characters, and casing noise so every email is valid and matchable.

Why it is useful. "john @ gmail.com" with a space, or "John@Gmail.com" vs "john@gmail.com", breaks sending and deduplication.

Do it with AI

The prompt.

"Clean the email column — remove spaces, lowercase, and flag invalid addresses."

The first becomes "john.smith@gmail.com"; the broken one gets flagged as invalid.

Do it manually in Excel

• Formula: =LOWER(TRIM(SUBSTITUTE(A2," ",""))) — SUBSTITUTE removes spaces, TRIM clears the edges, LOWER unifies the case.
• Validate at entry: Data → Data Validation → Custom, rule =ISNUMBER(SEARCH("@",A2)) — reject bad addresses before they land.

Verify

Ask Copilot "List the emails you think are invalid" and confirm before editing. Manually, scan for any address with a space or uppercase letters left behind.

Common mistake. Deduplicating emails before lowercasing them — "John@x.com" and "john@x.com" count as two people.

Best practice. Lowercase emails before any dedupe, and let the AI flag invalid ones rather than auto-delete them.

Convert Date Formats

What it is. Turning text dates into real dates and displaying them in one chosen format.

Why it is useful. "05/03/2025" means March 5 in Europe and May 3 in the US. Text dates can't be sorted or used in math.

Do it with AI

The prompt.

"Convert this column to real dates in yyyy-mm-dd format. The data is day/month/year."

It becomes a real date displaying 05/03/2025 (March 5 in DD/MM).

Do it manually in Excel

• Text to Columns: select the column → Data → Text to Columns → Delimited → Next → Next → choose Date and the order (DMY/MDY/YMD) → Finish.
• DATEVALUE: =DATEVALUE("2025-03-05") converts a text date to a real one.
• Display without converting: keep the value and apply a custom number format like yyyy-mm-dd or mmmm d, yyyy.

Verify

Ask Copilot "Show me 5 converted dates" and check them against dates you know — this is the #1 place AI goes wrong. Manually, confirm the order matched your source.

Common mistake. Assuming the AI knows the date order — it will guess, and a wrong guess flips day and month in every row.

Best practice. Always state the source order (DD/MM or MM/DD) in the prompt, and confirm the order before converting manually.