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 turns each field type into one consistent, reliable format.

Table of Contents

• Clean Phone Numbers
• Clean Emails
• Convert Date Formats

Clean Phone Numbers

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

Why it is useful. Formats like "5551234567", "(555) 123-4567", and "+1 555 123 4567" all refer to the same number but never match each other.

Technique 1 — Flash Fill.

1. Type the desired format for the first number in the next column: (555) 123-4567.
2. Press Ctrl + E. Excel fills the rest by following your pattern.

Explanation. Flash Fill learns the transformation from your one example — the fastest way to reformat a column.

Technique 2 — Strip everything, then format. =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"-","")," ",""),"(",""),")",""),"+","") → 5551234567

Explanation. Each SUBSTITUTE removes one character type, leaving digits only — the standard you can then format or match on.

Notes. Keep phone numbers as text, not numbers. Numeric storage drops leading zeros and country codes, and long numbers turn into scientific notation.

Common mistake. Reformatting to a pretty display without a common base. Always standardize to digits-only (or one display format) first, then match.

Let AI do it. Copilot: "Format all phone numbers as (555) 123-4567."

Clean Emails

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

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

Technique. =LOWER(TRIM(SUBSTITUTE(A2," ","")))

Example. A2 = " John.Smith @ Gmail.com " =LOWER(TRIM(SUBSTITUTE(A2," ",""))) → john.smith@gmail.com

Explanation. SUBSTITUTE removes every space, TRIM clears the edges, and LOWER unifies the case so the same address always reads the same.

Extracting from a jumble. Use Text to Columns (delimiter: space or @) or Flash Fill to pull an email out of a block of text.

Best practice. Validate at entry: Data → Data Validation → Custom, with a rule like =ISNUMBER(SEARCH("@",A2)) — reject bad addresses before they land.

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

Let AI do it. Copilot: "Clean the email column and flag invalid addresses."

Convert Date Formats

What it is. Turning dates that live as text 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. Mixed text dates can't be sorted, filtered, or used in math.

Technique 1 — Text to Columns.

1. Select the date column.
2. Data → Text to Columns → Delimited → Next → Next.
3. Choose Date and pick the order (DMY, MDY, YMD) your data is written in → Finish.

Excel now treats them as real dates, not text.

Explanation. You're telling Excel the order the source used, so it parses each text date correctly.

Technique 2 — DATEVALUE. =DATEVALUE("2025-03-05") → a real date you can format any way.

Display without converting. Keep the value as a date and apply a custom number format (Format Cells → Custom): yyyy-mm-dd, dd-mmm-yyyy, or mmmm d, yyyy.

Common mistake. Splitting a date into three columns (day/month/year) to "fix" it. Keep it as one real date — you can always display pieces with functions like =DAY(A2).

Notes. When a column mixes formats, run Text to Columns before combining — and always confirm whether your source is DD/MM or MM/DD first.

Let AI do it. Copilot: "Convert the date column to ISO format (yyyy-mm-dd)."