Phone numbers are often entered with different spacing, brackets, prefixes and country codes. A consistent format makes validation, deduplication and imports easier. You can standardise numbers in Google Sheets with formulas or Apps Script, then review values that do not match your expected pattern.
Define the target format
Choose one output format for your sheet, such as digits only, a local format or an international format with country code. Keep the original column unchanged and write the standardised result to a separate column so exceptions can be reviewed.
Clean and format with Apps Script
The example below removes non-digit characters, preserves a leading plus sign where present and flags values that are too short after cleanup. Adjust the rules for your country and data quality requirements.
function standardisePhoneNumbers() {
var sheet = SpreadsheetApp.getActiveSheet();
var values = sheet.getRange("A2:A").getValues();
var output = values.map(function (row) {
var raw = String(row[0] || "").trim();
if (!raw) return ["", ""];
var hasPlus = raw.indexOf("+") === 0;
var digits = raw.replace(/\D/g, "");
if (digits.length < 7) return [digits, "Review"];
var formatted = hasPlus ? "+" + digits : digits;
return [formatted, ""];
});
sheet.getRange("B2:C").clearContent();
if (output.length) sheet.getRange(2, 2, output.length, 2).setValues(output);
}For stricter formatting, add country-specific rules or validation after cleanup. Avoid guessing missing country codes unless you have reliable context.
Using Flookup custom functions
For related cleaning and matching steps, Flookup custom functions can help. NORMALIZE can remove unwanted characters or standardise text, and FUZZYSIM, FLOOKUP, DEDUPE or ULIST can support matching and deduplication workflows around contact data. The Sheets AI Assistant can suggest formulas for your specific ranges.