Customer names often appear in multiple forms across imports, forms and CRM exports. You can standardise them in Google Sheets by combining a small mapping table, clear formatting rules and an Apps Script function that applies those rules consistently.
Start with example pairs
Create an Examples sheet with original and preferred name values, such as “J. Smith” to “John Smith” or “ACME, Inc.” to “Acme Inc”. These pairs help you define the rules and catch exceptions that need review.
Apply rules with Apps Script
Use Apps Script to apply exact mappings first, then general cleanup such as trimming spaces, fixing punctuation and normalising case. Write the result to a new column so the original name remains available.
function standardiseCustomerNames() {
var ss = SpreadsheetApp.getActive();
var data = ss.getSheetByName("Customers").getRange("A2:A").getValues();
var examples = ss.getSheetByName("Examples").getRange("A2:B").getValues();
var map = {};
examples.forEach(function (r) {
var from = String(r[0] || "").trim().toLowerCase();
var to = String(r[1] || "").trim();
if (from) map[from] = to;
});
var out = data.map(function (r) {
var name = String(r[0] || "").trim();
if (!name) return [""];
var key = name.toLowerCase().replace(/\s+/g, " ");
if (map[key]) return [map[key]];
var cleaned = key.replace(/\b\w/g, function (c) { return c.toUpperCase(); }).replace(/\s+/g, " ").trim();
return [cleaned];
});
ss.getSheetByName("Customers").getRange("B2:B" + (out.length + 1)).setValues(out);
}Adjust the script for titles, suffixes, initials or company naming conventions used in your data. Review ambiguous cases before treating the output as final.
Using Flookup custom functions
If you prefer formulas, Flookup custom functions can help with normalisation and matching. NORMALIZE can clean text in-sheet, and FUZZYSIM, FLOOKUP, ULIST or DEDUPE can support fuzzy matching and deduplication when names vary slightly. The Sheets AI Assistant can also suggest formulas based on your ranges.