From splitting first/last name to deduping leads and prepping CSVs for CRM import — the Excel tricks that save insurance agents hours every week.
TL;DR
Insurance agents lose hours per week to spreadsheet cleanup. Use Text-to-Columns or =LEFT/MID formulas to split names, Conditional Formatting + Remove Duplicates to dedupe leads, =LOWER + =TRIM to normalize emails, and Save As CSV UTF-8 to prep CRM imports without encoding errors.
Key Data Points
- Insurance agents typically spend 3-5 hours per week on lead-list spreadsheet cleanup, with name parsing, deduping, and CSV prep accounting for the largest share.
- The =TRIM(MID(A2, FIND(" ", A2) + 1, 100)) formula reliably splits a 'Full Name' field into Last Name even when middle names are present.
Most insurance agents spend 3-5 hours per week wrestling with spreadsheets — cleaning lead lists, prepping CSVs for CRM import, deduping carrier reports, splitting full names into first/last fields. The right Excel formulas turn a half-day of manual work into a 10-minute task.
1. Split a Full Name Into First and Last Name
Lead vendors love sending you "Full Name" in a single column. Here is how to split it.
The Easy Way (Excel 2013+)
- Click the column header
- Data → Text to Columns
- Choose Delimited → Space
- Click Finish
Done. But this breaks on names like "Mary Ann Smith" or "Juan de la Cruz."
The Formula Way (Robust)
Assuming full name is in column A starting at A2:
First name:
=LEFT(A2, FIND(" ", A2) - 1)
Last name (handles middle names):
=TRIM(MID(A2, FIND(" ", A2) + 1, 100))
Last word only (true surname):
=TRIM(RIGHT(SUBSTITUTE(A2, " ", REPT(" ", 100)), 100))
Excel 2021 / 365 Power Method
=TEXTSPLIT(A2, " ") — splits into separate cells horizontally.
2. Dedupe a Lead List
You bought 5 lead lists. There is overlap. Find it before importing.
Highlight Duplicates Visually
- Select the email or phone column
- Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values
- Pick a fill color
- Sort by color to see all duplicates
Remove Duplicates Permanently
- Select your data range
- Data → Remove Duplicates
- Choose which columns define a duplicate (usually email OR phone)
- Click OK
Find Duplicates With a Formula
=COUNTIF($B$2:$B$10000, B2) returns the count of how many times a value appears. Filter for >1 to see duplicates.
3. Standardize Phone Numbers
Lead lists arrive in 47 different phone formats: "(555) 123-4567", "555.123.4567", "+1-555-123-4567", "5551234567." Normalize them to digits only:
=TEXT(VALUE(REGEXREPLACE(A2, "[^0-9]", "")), "0000000000")
Or, in older Excel:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "(", ""), ")", ""), "-", ""), ".", ""), " ", "")
4. Validate and Clean Email Addresses
Find emails missing an "@" symbol:
=IF(ISNUMBER(SEARCH("@", A2)), "Valid", "INVALID")
Lowercase all emails (carriers and CRMs treat "Bob@gmail.com" and "bob@gmail.com" as different):
=LOWER(TRIM(A2))
5. Convert State Names to Two-Letter Codes
Build a lookup table on Sheet2 with full state names in column A and codes in column B, then:
=VLOOKUP(A2, Sheet2!$A:$B, 2, FALSE)
6. Prep a CSV for CRM Import
Most CRMs (unLocked CRM included) require:
- UTF-8 encoded CSV (not the default "Save As CSV" — use CSV UTF-8)
- Header row with exact field names
- No merged cells
- No leading/trailing spaces (
=TRIM(A2)) - No Excel-formatted phone numbers stored as numbers (forces 5551234567 to "5,551,234,567")
Pre-flight checklist:
- Run TRIM on every text column
- Convert all phone columns to TEXT format before saving
- Lowercase all email columns
- Save as CSV UTF-8 (Comma delimited), not regular CSV
- Open in Notepad to verify nothing exotic snuck in
7. Calculate Days to T65 / Renewal
For a date of birth in column B, days until 65th birthday:
=DATE(YEAR(B2)+65, MONTH(B2), DAY(B2)) - TODAY()
For policy renewal (effective date in C2, renewal in 12 months):
=DATE(YEAR(C2)+1, MONTH(C2), DAY(C2)) - TODAY()
Filter for "less than 90" to surface every client with a renewal coming up.
8. The Single-Click Cleanup Macro
For agents who do this weekly, build a macro:
```vba
Sub CleanLeads()
Cells.Replace What:=" ", Replacement:=" ", LookAt:=xlPart
Range("A:A").NumberFormat = "@"
Cells.Value = Application.Trim(Cells.Value)
End Sub
```
Bind to a keyboard shortcut. Done.
When to Stop Using Excel
Excel is great for one-time cleanup. It is terrible for ongoing lead management. The moment you find yourself running the same VLOOKUP every Monday, move the workflow into a CRM with built-in dedupe, validation, and bulk-import:
- unLocked CRM auto-detects duplicate contacts on import
- Auto-normalizes phone numbers to E.164 format
- Validates emails against bounce databases before import
- Maps fields visually (no header-name matching required)
- Surfaces import errors row-by-row instead of dumping the whole file
For more on lead import workflow, see our Contacts & Pipelines page.
Frequently Asked Questions
How do I split a full name into first and last in Excel?
Use Data → Text to Columns with a space delimiter, or use =LEFT(A2, FIND(" ", A2)-1) for first name and =TRIM(MID(A2, FIND(" ", A2)+1, 100)) for last name.
How do I remove duplicate leads in Excel?
Select the data range, go to Data → Remove Duplicates, and choose the column (usually email or phone) that defines a duplicate.
Why do my phone numbers break when I save as CSV?
Excel stores them as numbers and strips leading zeros or applies number formatting. Format the column as TEXT before saving as CSV UTF-8.
How do I prep a CSV for CRM import?
TRIM all text columns, format phone columns as TEXT, lowercase emails, and save as CSV UTF-8 (Comma delimited) — not regular CSV.
When should I stop using Excel for lead management?
When you find yourself running the same cleanup workflow weekly. Modern CRMs auto-dedupe, normalize phones, and validate emails on import.