Job titles are the column everyone wants to segment on and nobody cleans. One export gives you "VP Sales", "V.P., Sales", "Sr. Mgr - Marketing" and "n/a", and your "Directors and above" list quietly misses half of them. These five fixes run in Google Sheets before you import into HubSpot, Salesforce, Pipedrive, or your sequencer. Assume the raw title is in E and you work in F.
1. Clean the whitespace first
Exports carry double spaces, line breaks and non-breaking spaces that make identical titles look different.
=TRIM(REGEXREPLACE(CLEAN(E2),"[\s\x{00A0}]+"," "))
Put this in F and run every later step on F, not E.
2. Expand common abbreviations
"Sr.", "Mgr" and "Dir." should read the same as the long form so filters catch them.
=REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(F2,"(?i)\bsr\.?\s","Senior "),"(?i)\bmgr\b\.?","Manager"),"(?i)\bdir\b\.?","Director")
Add more pairs the same way (Eng to Engineering, Mktg to Marketing) once you see which ones your list uses.
3. Bucket seniority
One column you can filter on beats fifty spellings. VP is checked before C-level so "Vice President" doesn't land in the president bucket.
=IFS(REGEXMATCH(LOWER(G2),"\b(vp|v\.p|vice president|svp|evp|head of)\b"),"VP/Head",REGEXMATCH(LOWER(G2),"\b(ceo|cfo|cto|coo|cmo|cro|chief|founder|co-founder|owner|president)\b"),"C-level/Owner",REGEXMATCH(LOWER(G2),"\bdirector\b"),"Director",REGEXMATCH(LOWER(G2),"\b(manager|lead|supervisor)\b"),"Manager",TRUE,"IC/Other")
G is the output of step 2. Spot-check the "IC/Other" rows; that is where odd titles like "Partner" or "Principal" end up, and you decide where they belong.
4. Bucket department
Seniority without function still mixes your CFO with your CMO.
=IFS(REGEXMATCH(LOWER(G2),"sales|account exec|business development|\bsdr\b|\bbdr\b|revenue"),"Sales",REGEXMATCH(LOWER(G2),"marketing|growth|demand gen|brand|content"),"Marketing",REGEXMATCH(LOWER(G2),"engineer|developer|\bit\b|technology|software"),"Engineering/IT",REGEXMATCH(LOWER(G2),"operations|\bops\b|finance|\bhr\b|people"),"Ops/Finance/HR",TRUE,"Other")
Keep the raw title too. The buckets are for filtering, and the original is what you show in a personalized line.
5. Flag junk titles for review
Blank cells, placeholders, pasted emails or URLs, and whole bios stuffed into the title field should never reach a merge field.
=IF(OR(LEN(TRIM(E2))=0,REGEXMATCH(LOWER(TRIM(E2)),"@|http|^(n/?a|none|test|-+|\.+)$"),LEN(E2)>80),"REVIEW","OK")
Filter to REVIEW, fix or blank those rows, and leave title out of your opener for anything you blank.
Run all five on a copy, sort by the seniority column to eyeball each bucket, then import only the cleaned sheet with the two bucket columns mapped to custom properties.
Rather hand off the cleanup? Drop your file into the free CSV preflight checker to see what will break, right in your browser. Or 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 tool login needed. Prefer to do it yourself? The HubSpot/Salesforce Import Checklist ($9) has every pre-import check on one page. Bigger lists and pricing at pureservices.store.
Top comments (0)