Phone number columns in business data are almost always messy. "(555) 867-5309", "5558675309", "+15558675309", "555.867.5309", "867-5309" — seven different ways to represent the same number. And mixed in with valid numbers are invalid ones: numbers that are too short, too long, start with 0 when they shouldn't, or are completely made up.
Here's how to validate and standardize phone numbers in a spreadsheet without writing code.
What Valid Phone Number Validation Checks
Length check: US numbers have 10 digits (or 11 with country code). International numbers vary. Any number shorter than 7 digits or longer than 15 digits is almost certainly wrong.
Character composition: Phone numbers should contain only digits, spaces, hyphens, parentheses, and the + prefix. Letters anywhere in the number indicate an error.
Country code presence: For international datasets, verify that country codes are present and consistent.
Sohovi profiles your datasets for quality issues in minutes — see what's broken before it breaks your pipeline — try Sohovi free.
Known invalid patterns: Numbers starting with 555 (in the US) are fictional. Numbers beginning with 0 in some countries are local prefixes that aren't valid for mobile outreach.
Validation in Excel
Check length: After stripping non-digit characters with SUBSTITUTE, use LEN to check digit count. Strip non-digits: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"(",""),")",""),"-","")," ",""),".","") Check length: =LEN([stripped_number])
A US number should have length 10 or 11 after stripping.
Check for letters: =IF(ISNUMBER(VALUE([stripped_number])), "Numeric", "Contains letters")
Standardization: Pick One Format and Convert Everything
The most universally compatible format is E.164: +[country code][number] with no spaces or characters. For US numbers: +15555551234.
If you're standardizing US-only numbers, a consistent format like (XXX) XXX-XXXX or XXX-XXX-XXXX works well.
Sohovi detects phone number patterns in your columns and flags inconsistencies — showing you the distinct formats present and what percentage of values appear valid — before you invest time in manual standardization.
After Validation
Records with invalid phone numbers fall into two categories:
- Correctable: Wrong format but correct digits (fix the format)
- Uncorrectable: Wrong digits or clearly fake numbers (flag for removal or manual lookup)
For uncorrectable invalid numbers, decide whether the record is still useful without a valid phone number, or whether it should be excluded from call-based campaigns while remaining in email campaigns.
Sohovi validates your email list for invalid formats, duplicates, and missing fields before you send — protecting your sender reputation.
