Excel Challenge 8: Extract Email Domains

Challenge Scenario

In B2B lead generation and customer domain enrichment, marketing teams analyze email addresses to identify company affiliations and filter corporate domains (such as "apple.com" or "google.com") from generic public mail providers.

To isolate the domain name from an email string, text formulas search for the position of the "@" symbol and extract all characters to the right of it.

As a Lead Generation Analyst, your tasks are:

Domain Name (Column C): Extract the domain portion of each email address using MID(B2, FIND("@", B2) + 1, LEN(B2)) or RIGHT(B2, LEN(B2) - FIND("@", B2)).
Domain Classification (Column D): If Domain equals "gmail.com", assign "PUBLIC"; otherwise assign "CORPORATE".
Total Audited Leads (Cell B8): Count total email records using COUNTA(A2:A5).
Corporate Leads Count (Cell C8): Count how many domains are labeled "CORPORATE" using COUNTIF.

Your Sheet Layout

The email domain enrichment worksheet is organized into clear operational sections:

Lead Contacts (Columns A & B): Shows contact names and their raw email address strings.
Domain Parsing & Classification (Columns C & D): Extracts the domain text after "@" and tags each lead as Corporate vs Public.
Campaign Summary (Rows 7 & 8): Aggregates total lead volume and counts qualified high-value corporate accounts.

Cell Range	Column Name	Section	Formula / Approach	What It Does
`A2:B5`	Contact Name, Email Address	Lead Data	Incoming prospect email contacts	Read-only incoming lead database.
`C2:C5`	Domain Name	Text Extraction	`=MID(B2, FIND("@", B2) + 1, LEN(B2))`	Extracts the domain substring following the "@" symbol.
`D2:D5`	Lead Type	Classification	`=IF(C2 = "gmail.com", "PUBLIC", "CORPORATE")`	Categorizes public webmail vs enterprise corporate domains.
`B8`	Total Leads	Audit Summary	`=COUNTA(A2:A5)`	Counts total lead records (4).
`C8`	Corporate Leads	Audit Summary	`=COUNTIF(D2:D5, "CORPORATE")`	Counts enterprise corporate lead contacts (3).

Challenge Objectives

Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.

Objective #1

Cell: C2-C5

In Column C (C2:C5), extract the domain after the @ sign using MID and FIND.

Objective #2

Cell: B8

In cell B8, count total emails using COUNTA(B2:B5).

Objective #3

Cell: C8

In cell C8, count accounts with domain "cyberdyne.org" using COUNTIF(C2:C5, "cyberdyne.org").

Objective #4

Cell: D8

In cell D8, count external accounts with other domains using COUNTIF(C2:C5, "<>cyberdyne.org").

Solution & Formula Breakdown

Let's examine how Excel locates delimiter characters and slices text strings:

1. Extracting Text After '@' with FIND and MID (C2:C5)

=MID(B2, FIND("@", B2) + 1, LEN(B2))

Let's break down the mechanics:

FIND("@", B2): Locates the 1-based character position of the "@" symbol in the email address.
+ 1: Starts extraction immediately after the "@" sign.
LEN(B2): Provides a safe maximum length parameter so MID captures everything through to the end of the text.
Row 2 (sarah@acme.com): "@" is at position 6 → extracts starting from character 7 → "acme.com".
Row 3 (david@gmail.com): "@" is at position 6 → extracts starting from character 7 → "gmail.com".
Row 4 (elena@techcorp.io): "@" is at position 6 → extracts starting from character 7 → "techcorp.io".
Row 5 (marcus@innovate.org): "@" is at position 7 → extracts starting from character 8 → "innovate.org".

2. Classifying Lead Types with IF (D2:D5)

=IF(C2 = "gmail.com", "PUBLIC", "CORPORATE")

If the extracted domain is "gmail.com", it is flagged as "PUBLIC"; otherwise, it is recognized as a high-value "CORPORATE" account.

3. Summary Rollups (B8 & C8)

Total Leads (Cell B8): =COUNTA(A2:A5) returns 4.
Corporate Leads (Cell C8): =COUNTIF(D2:D5, "CORPORATE") returns 3.

Combining FIND and MID is the universal spreadsheet pattern for extracting text after any delimiter (such as dashes, slashes, or commas)!