The same company turns up as "Acme Pty Ltd", "ACME PTY. LTD." and "The Acme Company", and every lookup, pivot table and COUNTIF treats them as three customers. This page cleans company names with formulas: a matching key that ignores case, punctuation and legal suffixes, a mapping table that turns every variant into one official name, and a lookup between two lists on that key. It also covers why PROPER is not the fix it looks like.
Row 1 holds headers. Company names in A2:A7:
Acme Pty Ltd
ACME PTY. LTD.
The Acme Company
Globex Corp.
globex corporation
Initech Inc
In B2, filled down:
=IF(A2="","",TRIM(REGEXREPLACE(REGEXREPLACE(LOWER(SUBSTITUTE(A2,CHAR(160)," ")),"[^a-z0-9 ]",""),"\b(pty|ltd|limited|inc|llc|corp|corporation|co|company|the)\b","")))
The keys are acme, acme, acme, globex, globex, initech. The formula swaps non-breaking spaces (common in pasted data) for normal ones, lower-cases, deletes punctuation, deletes the listed suffix words, and trims the leftover spaces.
The key is for matching, not for display. Two cautions about the word list:
Adjust it to your data. Add gmbh, plc, sa or group if you deal with those; remove a word if it is part of real names in your list. "The Company Store" would become just "store".
Punctuation is deleted, not replaced with a space, so "Smith&Jones" becomes "smithjones" while "Smith & Jones" becomes "smith jones". Change the "" in the first REGEXREPLACE to " " if your names glue words together with punctuation.
To fill the whole column with one formula, put this in B2 instead:
=ARRAYFORMULA(IF(A2:A="","",TRIM(REGEXREPLACE(REGEXREPLACE(LOWER(SUBSTITUTE(A2:A,CHAR(160)," ")),"[^a-z0-9 ]",""),"\b(pty|ltd|limited|inc|llc|corp|corporation|co|company|the)\b",""))))
If any one of the spellings will do, take the first one for each key. In C2:
=INDEX($A$2:$A$7,MATCH(B2,$B$2:$B$7,0))
Results: Acme Pty Ltd three times, Globex Corp. twice, Initech Inc. Fast, but the "standard" depends on which row happens to come first.
For a name you choose, keep a small table of key and official name. In E2:F4:
acme, Acme Pty Ltd
globex, Globex Corporation
initech, Initech Inc
Then in D2:
=IFNA(VLOOKUP(B2,$E$2:$F$4,2,FALSE),A2)
Results: Acme Pty Ltd three times, Globex Corporation twice, Initech Inc. IFNA keeps the original name when a key is not in the table yet, so new companies are not lost; filter column D against column A to see which ones need adding. The table also handles true aliases that no cleaning can link, such as a trading name and a legal name: add a row for each key.
=PROPER(A2) capitalises the first letter of each word, so "ACME PTY. LTD." becomes "Acme Pty. Ltd." That looks tidy, but it does not make the values match, because the full stops are still there. It also damages names that should stay in capitals: =PROPER("IBM Australia") gives "Ibm Australia". Use the key for matching and the mapping table for display.
Build the same key on the second list, then look up key to key. With the second list's names in H2:H4 (ACME Pty Limited, Globex Corporation, Umbrella Corp) and their keys in I2:I4 (the Step 1 formula, pointed at H), in J2:
=XLOOKUP(I2,$B$2:$B$7,$D$2:$D$7,"not found")
Results: Acme Pty Ltd, Globex Corporation, not found. Umbrella Corp is simply not in the first list. More ways to compare lists are at https://www.snakbacks.com/compare-two-columns-duplicates
Typos and abbreviations: "Initech" and "Initek", or "Intl Business Machines" and "International Business Machines", produce different keys. For those, score how similar two keys are and review the close ones. The Levenshtein guide has a free named function for that: https://www.snakbacks.com/levenshtein-distance-google-sheets and the near-duplicates guide applies it to a whole list: https://www.snakbacks.com/find-near-duplicates-google-sheets
Disclosure: Snakbacks, the publisher of this site, sells a paid script pack and makes two free add-ons for Google Sheets.
Everything above works with formulas alone. The Data Cleanup Kit for Google Sheets (US$15, no add-on install): https://snakback.gumroad.com/l/data-cleanup-kit has a ready-made clean-text tab that builds a simpler match key (case, spaces and punctuation, without the suffix list), plus FUZZYDUPES to group the variants that differ by typos, and tabs for merging and summing, keeping the latest row, comparing lists and splitting names. If you mainly need to match one list against another despite spelling differences, the Fuzzy Match Template for Google Sheets (US$9): https://snakback.gumroad.com/l/fuzzy-formulas is the smaller option.
Our two free add-ons, Fuzzy Match and Duplicate Remover, are coming soon to the Google Workspace Marketplace.
Snakbacks publishes the Duplicate Remover and Fuzzy Match add-ons.