---
title: "Excel Tricks Every Insurance Agent Should Know (Lead Cleanup, Deduping, and CSV Prep)"
description: "From splitting first/last name to deduping leads and prepping CSVs for CRM import — the Excel tricks that save insurance agents hours every week."
url: https://unlockedcrm.ai/blog/excel-tricks-for-insurance-agents
canonical: https://unlockedcrm.ai/blog/excel-tricks-for-insurance-agents
category: "Productivity"
published: 2026-05-14
updated: 2026-05-14
author: "Jacob Lock"
source: unLocked CRM — AI CRM for insurance agents
---

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

## 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](/contacts-pipelines) page.

## FAQ

### undefined



### undefined



### undefined



### undefined



### undefined



## Related

- https://unlockedcrm.ai/blog/npn-lookup-guide
- https://unlockedcrm.ai/blog/a2p-10dlc-compliance-walkthrough
- https://unlockedcrm.ai/blog/uhc-jarvis-login-how-to-use-with-unlocked-crm

---

Source: [Excel Tricks Every Insurance Agent Should Know (Lead Cleanup, Deduping, and CSV Prep)](https://unlockedcrm.ai/blog/excel-tricks-for-insurance-agents) — unLocked CRM, the AI CRM built for insurance agents. Citation permitted with attribution and a link to https://unlockedcrm.ai/blog/excel-tricks-for-insurance-agents.