Phone Number Validation in Google Sheets — Complete Guide

Dirty phone data costs businesses thousands in failed SMS campaigns, bounced calls, and wasted API credits. If you work with contact lists in Google Sheets, validating phone numbers before using them is essential.

This guide covers every method — from simple formulas to Apps Script automation — so you can clean phone data at any scale.

Why Phone Number Validation Matters

Invalid phone numbers cause real problems:

• Failed SMS deliveries — carriers reject malformed numbers, wasting your Twilio/Vonage credits
• Bad CRM data — duplicate or incorrect numbers pollute your pipeline
• Compliance risks — calling invalid numbers can trigger TCPA violations
• Wasted time — manual cleanup of thousands of rows is unsustainable

Even a 5% invalid rate in a 10,000-row list means 500 failed messages.

Method 1: Basic Formula Validation

The simplest approach uses built-in Google Sheets functions to catch obvious errors.

Check Length

Most international phone numbers (in E.164 format) are 10–15 digits:

=IF(AND(LEN(REGEXREPLACE(A2,"[^0-9]",""))>=10, LEN(REGEXREPLACE(A2,"[^0-9]",""))<=15), "Valid length", "Invalid length")

This strips all non-digit characters first, then checks the digit count.

Check for Letters or Symbols

Phone numbers should only contain digits, spaces, dashes, parentheses, and the + prefix:

=IF(REGEXMATCH(A2,"^[\+\d\s\(\)\-\.]+$"), "OK", "Contains invalid characters")

Detect Country Code

Check if the number starts with a + and country code:

=IF(REGEXMATCH(A2,"^\+[1-9]\d{0,2}"), "Has country code", "Missing country code")

Method 2: REGEXMATCH for Pattern Validation

Google Sheets supports REGEXMATCH for more precise validation.

US/Canada Numbers (NANP)

=REGEXMATCH(A2,"^\+?1?[\s\-\.]?\(?\d{3}\)?[\s\-\.]?\d{3}[\s\-\.]?\d{4}$")

Matches formats like:

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

E.164 International Format

The gold standard for phone storage:

=REGEXMATCH(A2,"^\+[1-9]\d{6,14}$")

This validates that the number:

• Starts with +
• Has a non-zero country code digit
• Contains 7–15 total digits (per ITU-T E.164 spec)

UK Numbers

=REGEXMATCH(A2,"^\+?44\s?\d{10}$")

Indian Numbers

=REGEXMATCH(A2,"^\+?91\s?\d{10}$")

Method 3: Apps Script for Bulk Validation

For large datasets, a custom Apps Script function gives you more control.

Basic Validation Function

/**
 * Validates a phone number against E.164 format
 * @param {string} phone The phone number to validate
 * @return {string} Validation result
 * @customfunction
 */
function VALIDATE_PHONE(phone) {
  if (!phone) return "Empty";

  // Strip all non-digit characters except leading +
  const cleaned = String(phone).replace(/[^\d+]/g, "");

  // Check E.164 format
  const e164Regex = /^\+[1-9]\d{6,14}$/;
  if (e164Regex.test(cleaned)) {
    return "Valid E.164";
  }

  // Check if it's a valid number missing the + prefix
  const digitsOnly = cleaned.replace(/\D/g, "");
  if (digitsOnly.length >= 10 && digitsOnly.length <= 15) {
    return "Valid (needs formatting)";
  }

  if (digitsOnly.length < 7) return "Too short";
  if (digitsOnly.length > 15) return "Too long";

  return "Invalid";
}

Use it in your sheet: =VALIDATE_PHONE(A2)

Bulk Format to E.164

/**
 * Converts a phone number to E.164 format
 * @param {string} phone The phone number
 * @param {string} defaultCountry Default country code (e.g., "1" for US)
 * @return {string} E.164 formatted number
 * @customfunction
 */
function TO_E164(phone, defaultCountry) {
  if (!phone) return "";
  defaultCountry = defaultCountry || "1";

  let cleaned = String(phone).replace(/[^\d+]/g, "");

  // Already in E.164
  if (/^\+[1-9]\d{6,14}$/.test(cleaned)) return cleaned;

  // Remove leading + if present but invalid
  cleaned = cleaned.replace(/^\+/, "");

  // If it starts with the country code, add +
  if (cleaned.startsWith(defaultCountry) && cleaned.length > 10) {
    return "+" + cleaned;
  }

  // Assume local number, prepend country code
  return "+" + defaultCountry + cleaned;
}

Usage: =TO_E164(A2, "1") for US numbers, =TO_E164(A2, "44") for UK.

Method 4: Use Makeinfo’s E.164 Validator Tool

If you need a quick check without formulas, use our free E.164 Phone Number Validator — paste numbers and get instant validation results.

For bulk operations inside Google Sheets, the Phone Number Formatter tool handles formatting at scale.

Handling Common Data Issues

Mixed Formats in One Column

When your data has numbers in different formats (555-1234, +44 20 7946 0958, (91) 98765-43210), use a normalization step first:

=REGEXREPLACE(A2, "[^\d+]", "")

This strips everything except digits and +, giving you a clean starting point.

Detecting Duplicates After Normalization

Once numbers are in E.164 format, finding duplicates is straightforward:

=COUNTIF($B$2:$B, B2) > 1

Where column B contains the normalized numbers.

Flagging Likely Test/Fake Numbers

function IS_LIKELY_FAKE(phone) {
  const cleaned = String(phone).replace(/\D/g, "");

  // All same digit
  if (/^(\d)\1+$/.test(cleaned)) return true;

  // Sequential (1234567890)
  if (cleaned === "1234567890") return true;

  // Known test prefixes (US 555, UK 7700 900)
  if (/^1?555/.test(cleaned)) return true;
  if (/^447700900/.test(cleaned)) return true;

  return false;
}

Best Practices

1. Always store in E.164 format — it’s the universal standard, and every SMS API accepts it
2. Validate on entry — use data validation rules or an add-on to catch bad numbers at input time
3. Separate country code — keep country code in a separate column if you frequently filter by region
4. Re-validate periodically — numbers get disconnected or reassigned over time
5. Use an API for high-stakes validation — for SMS campaigns, verify numbers against carrier databases (Twilio Lookup, NumVerify)

Summary

Method	Best For	Accuracy	Speed
Formulas	Quick checks, small datasets	Medium	Instant
REGEXMATCH	Pattern-specific validation	Medium-High	Instant
Apps Script	Bulk processing, custom logic	High	Fast
API validation	SMS campaigns, critical data	Highest	Depends on API

Start with formulas for quick wins, move to Apps Script for automation, and use API validation before any outbound campaign.

Related Resources

• E.164 Phone Number Validator — instant online validation
• Phone Number Formatter — format numbers to E.164
• Temporary Phone Services — test numbers for development