Standardise Phone Numbers in Google Sheets

The Problem: Phone Number Format Chaos

Phone numbers should be simple. Dial the number, it connects. In theory. In practice, phone numbers stored in spreadsheets are a chaos of formats.

Your CRM might store a number as +1-555-123-4567. A CSV import uses (555) 123-4567. Legacy systems store 5551234567 without any separators. International contacts are stored as +44 20 7946 0958. Some entries include extensions: 555-123-4567 x123. Others include notes: 555-123-4567 (main line).

When these numbers flow into your phone system, email integrations or appointment scheduling tools, the mismatches cause problems. Your phone system cannot dial (555) 123-4567 because it expects +15551234567. Your email integration cannot find phone numbers wrapped in text. Your API rejects formats it does not recognise.

The business impact is real: contact lists become unreliable, customer communications are delayed and integration failures go unnoticed until they break critical workflows.

Why Phone Number Formulas Are Complex

The obvious solution is to write a formula. Unfortunately, phone number formatting is deceptively complex.

A simple removal formula strips all non-digit characters: =REGEX(A2,"[^0-9]","","g"). This works for basic cases but loses important information. Is 15551234567 a US number with country code or a domestic number missing a digit? You cannot tell.

Conditional logic detects the format, removes the extraneous characters, then reformats: if the number starts with +1, keep it; if it is 10 digits, add the country code; if it has 11 digits, check whether the first digit is 1. The logic gets long and fragile and every new format requires code changes.

Regular expressions capture different phone number patterns and reformat them. For a single country this works. For international numbers, multiple formats and legacy data, the regex becomes unmaintainable.

All three approaches share a common problem: they anticipate format variations upfront. When a new variation appears, the formula breaks or produces incorrect results.

Introducing Learn from Examples

The Learn from Examples feature solves this differently from a spreadsheet formula. Instead of writing rules, you show the system a few before-and-after phone numbers and it infers the transformation. For a detailed walkthrough of the feature, see the Learn from Examples documentation.

You provide a few examples of phone numbers in various formats and what the standardised version should be. The pattern synthesis engine analyses these examples, identifies the underlying transformation logic and applies it to your entire contact list. Complex phone number logic emerges from simple examples.

Case Study: Phone Formats at Real-World Scale

Step 1: A Real Problem Worth Understanding

Phone number formats are inconsistent enough that Google maintains an entire library, libphonenumber, just to parse, format and validate them. The Android operating system has bundled it since version 4.0 because every contact list, whether it comes from Salesforce, Shopify or a plain spreadsheet, mixes domestic numbers with various separators, international numbers with country codes and legacy numbers with no formatting at all.

The same pattern shows up in address data. A 2002 report from the United States Postal Service and PricewaterhouseCoopers found that 23.6 percent of all US mail was incorrectly addressed, a figure that data quality research continues to cite. Contact data is a close cousin of address data and fragments in the same way.

Step 2: Create Example Pairs

In a dedicated sheet, you create two columns. Column A holds the messy phone numbers exactly as they appear. Column B holds the desired standardised format: country code plus area code plus local number, all digits with spaces between sections.

Step 3: Test the Pattern

You select both columns and click Test. The pattern synthesis engine detects: Remove all non-digit characters. If missing country code, add +1. Reformat to country code plus 3-digit area code plus 3-digit exchange plus 4-digit number, separated by spaces. Remove extensions.

A preview shows how the system will transform a sample of your contact numbers. The results match your intended output.

Step 4: Configure and Schedule

You select your full phone number column as input and set the output range. Configure the schedule to process the column in one batch (the auto-chaining feature handles the execution limit) and set it to repeat weekly for new incoming contacts.

Step 5: Verify and Deploy

The first run processes the full column and writes the standardised numbers to your output range. Verify a sample of rows on the first execution, then leave the schedule to clean new contacts as they arrive.

How Pattern Synthesis Works

The Learn from Examples feature applies sophisticated analysis to identify phone number transformation logic:

1. Analyse the Differences: The system compares each dirty number to its clean counterpart and identifies what changed. Dashes removed? Parentheses stripped? Characters added or reordered?
2. Extract Common Patterns: It examines your example pairs and identifies the normalisation steps that every number shares. If 100 percent of your examples strip non-digit characters and add country codes, that becomes part of the learned rule.
3. Build a Rule Pipeline: The system constructs a pipeline of transformations (remove characters, reorder sections, add prefixes, trim suffixes) that, when applied in sequence, standardise all variations to your desired format.
4. Validate Against Examples: Before applying the rule to your full contact list, the system verifies that the discovered rule correctly transforms all your example pairs.
5. Apply to Full Dataset: Once validated, the rule is applied to every phone number in your input range.

What the Real Case Shows

libphonenumber is used so widely precisely because the underlying problem never goes away. Every country formats numbers differently, users enter them inconsistently and downstream systems still need a single reliable format to match on.

Your contact list is a smaller version of the same problem. Once numbers are standardised, phone system integrations accept the data, duplicate checks stop missing entries that differ only by formatting and outreach reaches the intended number the first time. The one-time teach-by-example step removes a recurring manual correction task.

Getting Started with Your Own Data

Assess Your Phone Number Formats

Before you start, gather 5 to 7 phone numbers that represent the different formats in your contact list. Note the variations: country codes, separators, extensions, text annotations. This diversity ensures your examples capture the full transformation.

Create Example Pairs

In a new sheet, create two columns. Column A has the messy phone numbers, column B has the standardised versions. Decide on your target format upfront: domestic with country code? International with spaces? Just digits? Your examples should all follow the same target format.

Test and Validate

Use the Test function to preview the detected rule against a few sample numbers. If it matches your intention, proceed. If not, add another example pair that clarifies the expected transformation and re-run the test.

Schedule and Monitor

Select your full phone number column and configure the frequency. For ongoing contact list updates, choose Process in a loop. Monitor the first execution to verify that all numbers are standardised correctly.

For complete scheduling instructions, see the Learn from Examples documentation.

Frequently Asked Questions

What if my phone numbers include extensions?

If you want to preserve extensions, show that in your examples: 555-123-4567 x123 becomes +1 555 123 4567 x123. If you want to remove extensions, show that: 555-123-4567 x123 becomes +1 555 123 4567. The system learns from your examples.

Can it handle international numbers?

Yes. Provide examples that show your international numbers in the desired format. If you want country codes preserved, show that. If you want them stripped for certain countries, show examples of that too. The system learns the pattern from your examples.

What if I have mixed domestic and international numbers?

You can create one schedule if your standardised format handles both (for example, all numbers have country codes). If your standardised format differs between domestic and international, you may need to create separate schedules for each type or pre-filter your data.

Does it handle numbers with text annotations?

If your examples show annotations being removed, the system will remove them. If some annotations are important and should be preserved, you can show that in your examples and the system will learn which annotations to keep and which to discard.