"Hi JOHN," and "Acme Inc" next to "ACME, Inc." are the two fastest ways to look sloppy in outreach and split one account into three in your CRM. These six formulas clean name and company columns in Google Sheets before you import. Assume full name is in A and company is in C.
1. Fix capitalization
PROPER handles ALL CAPS and all-lowercase names in one pass.
=PROPER(TRIM(A2))
Spot-check names like McDonald, O'Neil, and van der Berg afterwards; no formula gets every one right.
2. Split full name into first and last
First: =IFERROR(LEFT(B2,FIND(" ",B2)-1),B2)
Last: =IFERROR(REGEXEXTRACT(B2,"\s(\S+)$"),"")
Middle names drop out of both columns, which is usually what a CRM import wants.
3. Strip titles and suffixes
Dr., Mr., Ms., Jr., III, and PhD end up in the first or last name field and break personalization.
=TRIM(REGEXREPLACE(B2,"(?i)^(dr|mr|mrs|ms|prof)\.?\s+|,?\s+(jr|sr|ii|iii|phd|mba)\.?$",""))
4. Flag placeholder names
Scraped and form-fill lists are full of "Test", "N/A", "asdf", and email addresses typed into the name box.
=IF(REGEXMATCH(LOWER(B2),"^(test|n/?a|none|asdf|unknown|-)$|@"),"placeholder","")
5. Normalize company suffixes
So "Acme Inc", "ACME, Inc.", and "Acme Incorporated" match as one account.
=TRIM(REGEXREPLACE(PROPER(C2),"(?i),?\s+(inc|incorporated|llc|ltd|limited|corp|corporation|co|gmbh)\.?$",""))
Keep the original column too, and import the cleaned one as a matching key rather than the display name if your legal names matter.
6. Find company duplicates
=IF(COUNTIF(D$2:D2,D2)>1,"dup company","")
Duplicate companies aren't always wrong (several contacts per account is normal), but they tell you where your CRM will create duplicate company records unless you match on domain.
Before you import
Map first name, last name, and the cleaned company to separate CRM fields, and match companies on website domain where your CRM allows it. Name matching alone will still miss "IBM" versus "International Business Machines".
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)