Phone columns are the quietest way to wreck a lead list. HubSpot, Salesforce, Pipedrive, and most dialers want one consistent format, usually E.164 (+14155550123). Exports give you (415) 555-0123, 415.555.0123 ext 4, and 00 44 20 7946 0958 in the same column. Fix it in Sheets before you import.
1. Strip everything that isn't a digit or a leading plus
=REGEXREPLACE(TRIM(A2),"[^\d+]","")
2. Split off extensions first
Extensions get glued onto the number once punctuation is removed. Pull them into their own column before step 1:
=IFERROR(REGEXEXTRACT(LOWER(A2),"(?:ext\.?|x)\s*(\d+)$"),"")
and remove them from the main value:
=TRIM(REGEXREPLACE(LOWER(A2),"(?:ext\.?|x)\s*\d+$",""))
3. Convert 00 international prefixes to +
=IF(LEFT(B2,2)="00","+"&MID(B2,3,20),B2)
4. Add a country code where it's missing
For a US/Canada list, a bare 10-digit number gets +1; an 11-digit number starting with 1 gets +:
=IF(LEFT(C2,1)="+",C2,IF(LEN(C2)=10,"+1"&C2,IF(AND(LEN(C2)=11,LEFT(C2,1)="1"),"+"&C2,C2)))
Use your own country's code if the list is mostly local numbers, and use the Country column when you have one.
5. Flag anything that still looks wrong
=IF(REGEXMATCH(D2,"^\+\d{8,15}$"),"ok","check")
Filter on "check" and fix or blank those rows by hand. A blank phone field is better than a wrong one that your reps dial.
Test ten rows first
Import ten rows, open the records, and click-to-call one. If it dials correctly, import the rest.
Rather hand off the cleanup? Send up to 200 rows to the $25 Quick Clean and get back a cleaned CSV (same-email duplicates merged, possible duplicates flagged) plus a change log within 24 hours. No CRM login needed: email us your CSV (reply to your receipt or send it to slrescue@mail.grokbot.com). Bigger list? Intro $99 covers up to 500 rows, and Growth ($299, up to 2,000) and Bulk ($499, up to 5,000) are on pureservices.store. Want to check the file first? The free CSV preflight checker runs in your browser.
Top comments (0)