Most insurance agents inherit messy CSV lead lists — full names crammed in one column, duplicate contacts, phone numbers in five formats.
TL;DR
Before importing a lead list into a CRM, clean it in Excel: Flash Fill names, normalize phones to E.164, dedupe by phone, validate emails, two-letter states, padded zips. unLocked's import tool catches what you miss.
Key Takeaways
- Use Excel Flash Fill (Ctrl+E) to split full names into first/last in seconds
- Normalize phones to E.164 (+15551234567) or A2P 10DLC and dialers break
- Dedupe by normalized phone, not by name — name spellings vary
- Pad zips to 5 digits — Excel strips leading zeros and breaks Northeast addresses
Every insurance agent has done it: bought a list, scraped a public record, exported a carrier book, or inherited a producer's contacts — and gotten back a CSV that is a disaster. Names crammed in one column. Phone numbers like (555) 123-4567, 555.123.4567, and 5551234567 mixed together. Duplicate records under three different spellings of the same person. Email addresses with trailing spaces.
Importing that into a CRM without cleanup poisons the database. Duplicates, broken merge tags, A2P violations from invalid numbers, bounced email sequences. Here is the actual cleanup playbook before you import.
Step 1: Split Full Names Into First and Last
If you have a single Full Name column, Excel handles it three ways:
Easiest — Flash Fill (Excel 2013+):
- In column B next to
Full Name, type the first name from row 1 - Press Ctrl+E
- Excel auto-fills the rest by pattern
Formula approach:
```text
First name: =LEFT(A2, FIND(" ", A2) - 1)
Last name: =MID(A2, FIND(" ", A2) + 1, LEN(A2))
```
Edge cases to fix manually:
- Two-word last names (Van Buren, De La Cruz)
- Suffixes (Jr., III, Sr.) — strip these before splitting
- Single-name entries (just a first name) — flag and review
Step 2: Normalize Phone Numbers
Carriers, CRMs, and dialers expect E.164 format (+15551234567). Mixed-format columns break A2P 10DLC compliance and dialer auto-dial.
Strip everything that is not a digit:
```text
=TEXT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"(",""),")",""),"-","")," ",""),"0000000000")
```
Then prepend +1 for US:
```text
="+1"&RIGHT(B2,10)
```
Validate length — any cell that is not 12 characters (+1 + 10 digits) is broken and needs review.
Step 3: Dedupe the List
Excel's built-in Remove Duplicates (Data tab) is the fastest path, but it only matches exact strings. The real-world insurance list has:
- "John Smith" and "Jon Smith" with the same phone
- Same person under two email addresses
- Same household at the same address with different first names (which are NOT duplicates — they are family members)
Better approach:
- Sort by normalized phone number
- Use
=COUNTIF(C:C, C2) > 1to flag any phone appearing more than once - Review flagged rows manually — keep the most complete record
- Then use Remove Duplicates on normalized phone
For 5,000+ row lists, use a CRM import tool that does fuzzy matching (Levenshtein distance) instead of Excel.
Step 4: Clean Email Addresses
```text
Lowercase: =LOWER(TRIM(A2))
Strip spaces: =SUBSTITUTE(LOWER(TRIM(A2))," ","")
Validate format: =IF(ISNUMBER(SEARCH("@",A2))*ISNUMBER(SEARCH(".",A2)),"valid","INVALID")
```
Drop any "INVALID" rows before import — they will bounce, hurt sender reputation, and waste send credits.
Step 5: Normalize State and Zip
- States: convert to two-letter abbreviations (CA, TX, FL) — most CRMs expect this
- Zips: pad to 5 digits with leading zeros (
=TEXT(A2,"00000")) — Excel strips leading zeros by default and Massachusetts/New Jersey/Connecticut zips break otherwise
Step 6: Add the Fields Your CRM Needs Before Import
Most insurance CRMs need at minimum:
- First name, Last name (split)
- Phone (E.164)
- Email (validated)
- State (two-letter)
- Lead source (set this column to the list name)
- Tag column for segmentation
- Date added
If the import is for Medicare, also: DOB (or T65 date), Medicare ID if known, county.
What unLocked CRM Does on Import
unLocked's import tool runs the same cleanup automatically:
- Splits full names into first/last with edge-case detection
- Normalizes phones to E.164 and validates against A2P 10DLC rules
- Fuzzy-matches duplicates against your existing contacts (not just within the import)
- Validates email syntax and flags catch-all/disposable domains
- Geocodes addresses for territory routing
- Adds the import batch as a tag automatically so you can sequence the batch separately
The point: do as much in Excel as you can to preserve full control, then let the CRM import tool catch what you missed.
Bottom Line
Garbage in, garbage out. Five minutes of Excel cleanup before a CRM import saves hours of duplicate hunting, broken merge tags, and A2P violations later. Split names, normalize phones to E.164, dedupe by phone, lowercase emails, two-letter states, padded zips. Then import.
Frequently Asked Questions
What is the fastest way to split full names in Excel?
Flash Fill (Ctrl+E in Excel 2013+). Type the first name from row 1 in the next column, hit Ctrl+E, and Excel auto-fills the rest by pattern.
Why do phone numbers need to be in E.164 format?
A2P 10DLC compliance, dialers, and most CRM SMS engines expect E.164 (+15551234567). Mixed formats cause silent failures and compliance flags.
Does unLocked CRM auto-deduplicate on import?
Yes. unLocked fuzzy-matches incoming contacts against your existing database and within the import batch, flagging likely duplicates for review before they are created.
What's the most common cleanup mistake?
Stripping leading zeros from zip codes. Excel does this by default. Northeast states (MA, NJ, CT, RI, VT, NH, ME) all have zips starting with 0 and break when those are stripped.