What You Will Learn
You will learn how to use Gemini and formulas to split messy contact records into structured fields such as first name, last name, company, email domain, country, and job title.
Required Dataset
No external dataset is required.
Create three tabs:
Raw_Contacts
Contact_Normalization
Contact_Check
Before You Start
In Raw_Contacts, create a small list of messy contact records in column A. Use examples like:
Maria Santos | VP Operations | BrightLane Retail | [email protected] | United States
Ravi Kumar | Director, Procurement | Atlas Manufacturing | [email protected] | India
Amelia Garcia | CFO | FinanceCo | [email protected] | Spain
Each row should contain name, title, company, email, and country in one messy text string.
Step-by-Step Instructions
Step 1. Ask Gemini to inspect the messy records
Open Gemini and enter:
Act as a data cleanup analyst.
Review the Raw_Contacts tab. Recommend how to split each messy contact record into Full Name, First Name, Last Name, Job Title, Company, Email, Email Domain, and Country. Provide formulas and note any rows that may need manual review.
Step 2. Create normalized fields
In Contact_Normalization, create columns for:
Raw Contact
Full Name
First Name
Last Name
Job Title
Company
Email Domain
Country
Review Needed
Copy the raw contact strings into Raw Contact.
Step 3. Add splitting formulas
Assuming the raw contact is in A2, use:
=TRIM(INDEX(SPLIT(A2,”|”),1,1))
for Full Name.
Use:
=TRIM(INDEX(SPLIT(A2,”|”),1,2))
for Job Title.
Use:
=TRIM(INDEX(SPLIT(A2,”|”),1,3))
for Company.
Use:
=TRIM(INDEX(SPLIT(A2,”|”),1,4))
for Email.
Use:
=TRIM(INDEX(SPLIT(A2,”|”),1,5))
for Country.
For First Name, use:
=PROPER(INDEX(SPLIT(B2,” “),1,1))
For Last Name, use:
=PROPER(INDEX(SPLIT(B2,” “),1,COUNTA(SPLIT(B2,” “))))
For Email Domain, use:
=IFERROR(LOWER(REGEXEXTRACT(G2,”@(.+)$”)),””)
Step 4. Add a review flag
Use:
=IF(OR(B2=””,E2=””,F2=””,G2=””,H2=””),”Yes”,”No”)
This flags incomplete rows.
Expected Result
You should have a clean contact table with separate fields for name, title, company, email, domain, country, and review status.
Check Your Work
Confirm that each raw contact was split correctly, email domains do not include @, names are readable, and incomplete rows are flagged for review.
Optional Extension
Ask Gemini:
Review the Contact_Normalization tab and identify rows with personal email domains, unclear job titles, missing countries, or company names that may need standardization.
