Split and Normalize Contact Fields

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

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.

How to get started

1

arrow-down-blue

Tell us about your project

2

arrow-down-blue

Interview candidates

(We’ll provide bios within 48 hours on average)

3

Select your consultant and start work

Find a Consultant

or email us at: [email protected]