Excel Tricks Every Insurance Agent Should Know (Lead Cleanup, Deduping, and CSV Prep)

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+)

  1. Click the column header
  2. Data → Text to Columns
  3. Choose Delimited → Space
  4. 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

  1. Select the email or phone column
  2. Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values
  3. Pick a fill color
  4. Sort by color to see all duplicates

Remove Duplicates Permanently

  1. Select your data range
  2. Data → Remove Duplicates
  3. Choose which columns define a duplicate (usually email OR phone)
  4. 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:

  1. Run TRIM on every text column
  2. Convert all phone columns to TEXT format before saving
  3. Lowercase all email columns
  4. Save as CSV UTF-8 (Comma delimited), not regular CSV
  5. 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.