Fixing Messy Phone Number Formats in Your Data

You're trying to deduplicate a customer list. Two records have the same name and address, but different phone numbers: (555) 123-4567 and 5551234567. Those are the same number. Your deduplication doesn't know that.

Phone numbers are one of the worst fields to work with in raw data. People enter them however they feel like it. Systems export them in whatever format was configured years ago. Merge two data sources and you get chaos. Here's what a typical phone column looks like:

• (555) 123-4567
• 555-123-4567
• 5551234567
• +1-555-123-4567
• 555.123.4567
• 1 (555) 123-4567
• +15551234567
• 555 123 4567

Eight formats. Same number. You can't match records, you can't count unique phone numbers, and you can't build any report that groups by phone. The fix is straightforward: strip every number down to just digits, then rebuild it in one consistent format.

Here's how to do it in ExploreMyData.

Step 1: Strip out all the formatting characters

The goal is to turn every phone number into a plain string of digits. That means removing parentheses, dashes, dots, spaces, and the plus sign. Use Find & Replace from the Transform group.

Select your phone column and run these replacements one at a time:

1. Find (, replace with nothing (empty)
2. Find ), replace with nothing
3. Find -, replace with nothing
4. Find ., replace with nothing
5. Find (space), replace with nothing
6. Find +, replace with nothing

Each step adds a REPLACE() call to the pipeline. After all six, your SQL looks like a chain:

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(phone, '(', ''), ')', ''), '-', ''), '.', ''), ' ', ''), '+', '')

Not pretty SQL, but it works. Every phone number in your column is now a string of digits. (555) 123-4567 became 5551234567. +1-555-123-4567 became 15551234567.

Step 2: Handle the country code

After stripping formatting, some numbers are 10 digits (5551234567) and some are 11 (15551234567) because they had a leading "1" country code. You need to normalize this.

If all your data is US/Canada numbers, the simplest approach: use Extract Text from the Transform group. Select the phone column, set the extraction method to ends_with and put 10 in "Number of characters". Set Apply results into back to the phone column so you overwrite in place rather than adding a phone_extract column.

The generated SQL is RIGHT(phone, 10). That drops the leading "1" from 11-digit numbers and leaves 10-digit numbers untouched.

Now every phone number is exactly 10 digits. 5551234567. Consistent. Matchable. Dedupable.

Step 3: Reassemble in a readable format

A 10-digit string works for matching, but it's not great for humans reading the data. If you want a formatted output like (555) 123-4567, you can rebuild it from the parts.

Use Extract Text with the position method three times, each with a start position and a length, and name each output:

• area_code: start 1, length 3 - SUBSTRING(phone, 1, 3)
• prefix: start 4, length 3 - SUBSTRING(phone, 4, 3)
• line: start 7, length 4 - SUBSTRING(phone, 7, 4)

Then Combine Columns from the Columns group. This panel is not a column picker with a separator box. It's a single token field: you type or pick items one after another, and each one becomes either a blue column token or a plain text token. For this output you'd build six tokens in order:

1. text (
2. column area_code
3. text )  (closing paren plus a space)
4. column prefix
5. text -
6. column line

Because the separators are tokens rather than a single global setting, you can put different text between each pair, which is exactly what a phone number needs. Name the output phone_formatted. The generated SQL:

CONCAT('(', COALESCE(CAST(area_code AS VARCHAR), ''), ') ', COALESCE(CAST(prefix AS VARCHAR), ''), '-', COALESCE(CAST(line AS VARCHAR), ''))

The COALESCE around each column is the detail that saves you: in plain SQL, concatenating anything with a NULL gives you NULL, so one missing part would wipe out the whole formatted number. Here a missing part just leaves a gap, like () 123-4567, which is ugly and therefore easy to spot.

The result: (555) 123-4567. Every row. Every time.

The shortcut: one Regex Capture instead of nine steps

Six replacements plus three extracts is a lot of clicking for something a single pattern can do. Regex Capture from the Transform group takes one pattern and writes each capture group into its own new column, which collapses steps 1 and 3 into one step and handles the country code for free.

Point it at the raw phone column and use:

(\d{3})\D*(\d{3})\D*(\d{4})\s*$

Then define three capture groups: area_code at index 1, prefix at index 2, line at index 3. Each becomes a REGEXP_EXTRACT(phone, '...', n) column.

Reading the pattern: three digits, then any run of non-digits, then three digits, then any run of non-digits, then four digits, then the end of the string. The $ anchor is what makes the leading "1" disappear. Since the match has to finish at the end and only ten digits fit, +1-555-123-4567, 1 (555) 123-4567 and 15551234567 all give you 555 / 123 / 4567 without a separate country-code step. Feed those three columns into the Combine Columns step above and you're done.

The catch is the same one every regex has: anything that doesn't fit the shape returns an empty string rather than an error. Extensions like "555-123-4567 x22" fail the $ anchor and come back blank. Filter area_code with is Empty afterwards to see what didn't match.

When to just keep the digits

Honestly? For most analysis, stop after step 2. The 10-digit string is the best format for matching, joining, and deduplication. It's unambiguous. Two records with the same 10-digit string are the same phone number, period.

Only do step 3 if you're exporting the data for human consumption - a mailing list, a customer directory, a report that someone will actually read. For analytics work, digits-only is cleaner.

What about international numbers?

If your data has numbers from multiple countries, the approach changes. You can't just take the last 10 digits because country codes vary in length (1 for US, 44 for UK, 91 for India). In that case, keep the full digit string including country code and standardize on the E.164 format: +15551234567. Strip all formatting characters (step 1), then prepend a "+" using Find & Replace or Combine Columns.

Normalize your phone column →