The Scenario
The lead-gen form your team ran last quarter collected 600 phone numbers from prospects across seven countries. They landed in a Google Sheet exactly as submitted — some with country codes, some without, some with formatting characters, some that look like they were typed while running.
The sales ops manager inherited this sheet and now needs to import it into Salesforce before Friday's pipeline review. Salesforce will reject malformed numbers at import time, which means fixing them post-import would be even worse than catching them now.
The bad version:
- Scroll through 600 rows and eyeball which numbers look wrong — catch maybe 40% of the actual problems.
- Export the column, run a phone validation library locally, get back a result file that uses different column names than Salesforce expects, manually map the fields.
- Discover the library flagged numbers from certain countries as invalid because it defaulted to US formatting rules, re-run with country hints, create a third output file.
The import window is tomorrow morning. Nobody else on the team has touched this sheet before.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent inside your Google Sheet that reads your data and calls BigDataCloud's phone number validation API for each row. You write the task in plain language — it handles the API calls and the writeback.
Open SheetXAI from the Extensions menu and type:
Validate each phone number in column B using BigDataCloud and add columns for is_valid, detected country code, and number type (mobile/landline)
What You Get
- Column C: is_valid — true or false per row
- Column D: detected country code (e.g., "US", "DE", "BR") inferred from the number's format and prefix
- Column E: number_type — "mobile", "landline", or "unknown"
- Rows that failed validation show the failure reason in a tooltip or adjacent note, so you know whether the issue is a wrong prefix, too few digits, or an unrecognized format
What If the Data Is Not Quite Ready
Numbers have inconsistent formatting characters — parentheses, dashes, spaces
Validate each phone number in column B using BigDataCloud — normalize formatting before validation (remove parentheses, dashes, spaces) and write is_valid and detected_country_code into columns C and D
The sheet mixes numbers with and without country code prefixes
Validate the phone numbers in column B using BigDataCloud — for rows that don't include a country code, assume the country from the "Country" column in column A and add the appropriate prefix before validating, then write is_valid into column C
Some numbers look valid but belong to the wrong country for this campaign
Validate all phone numbers in column B using BigDataCloud, write is_valid and country_code into columns C and D, then flag in column E any row where the detected country code does not match the value in the "Target Region" column in column F
Full cleanup and CRM-ready output in one pass
Validate each phone number in column B using BigDataCloud — normalize formatting, detect country code, classify type (mobile/landline), mark invalid rows, and output a filtered sheet called "CRM Ready" containing only valid rows with the phone number in E.164 format
One prompt handles the normalization, the validation, the classification, and the filtered export.
Try It
Get the 7-day free trial of SheetXAI and open any Google Sheet with a column of form-submitted phone numbers — try the prompt above before your next CRM import. Also see the spoke on email address verification if you need to clean the email column in the same sheet.