Guided project
Clean a CRM export in Excel
Fix messy names and emails, spot duplicates, count real contacts.
You will use: PROPER, TRIM, LOWER, TEXTBEFORE, TEXTAFTER, COUNTIF, IF, COUNTA, UNIQUE
Starting data
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Raw name | Raw email | Name | Company | Duplicate | Unique contacts | ||
| 2 | jordan LEE | JORDAN.LEE@ACME.COM | ||||||
| 3 | maria garcia | maria@northwind.io | ||||||
| 4 | SAM o'brien | Sam.OBrien@Acme.com | ||||||
| 5 | Jordan Lee | jordan.lee@acme.com | ||||||
| 6 | priya shah | PRIYA@globex.net | ||||||
| 7 | maria GARCIA | maria@northwind.io |
Build it step by step
Step 1
Clean the name in C2: no extra spaces, each word capitalized. Fill to C7.
Step 2
Clean the email in D2: lowercase, no stray spaces. Fill to D7.
Step 3
Pull the company name out of the email domain in E2 (acme, northwind). Fill to E7.
Step 4
Flag repeat emails as "Duplicate" in F2, leaving first appearances blank. Fill to F7.
Step 5
How many unique contacts are there? Put it in I1.