---
title: "Excel Data Cleanup for Insurance Agents: Split Names, Dedupe Lists, Fix Phones"
description: "Most insurance agents inherit messy CSV lead lists — full names crammed in one column, duplicate contacts, phone numbers in five formats. Here is the practical Excel and CRM-import cleanup playbook."
url: https://unlockedcrm.ai/blog/excel-data-cleanup-for-insurance-agents
canonical: https://unlockedcrm.ai/blog/excel-data-cleanup-for-insurance-agents
category: "Operations"
published: 2026-05-20
author: "Jacob Lock"
source: unLocked CRM — AI CRM for insurance agents
---

# Excel Data Cleanup for Insurance Agents: Split Names, Dedupe Lists, Fix Phones

## 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.

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+):**
1. In column B next to `Full Name`, type the first name from row 1
2. Press Ctrl+E
3. 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:**
1. Sort by normalized phone number
2. Use `=COUNTIF(C:C, C2) > 1` to flag any phone appearing more than once
3. Review flagged rows manually — keep the most complete record
4. 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.

## FAQ

### undefined



### undefined



### undefined



### undefined



---

Source: [Excel Data Cleanup for Insurance Agents: Split Names, Dedupe Lists, Fix Phones](https://unlockedcrm.ai/blog/excel-data-cleanup-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-data-cleanup-for-insurance-agents.