Challenge Scenario
In marketing operations, analytics teams standardize tracking URLs using structured string codes formatted as SRC-[CHANNEL]-[YEAR]-[REGION]. To aggregate channel performance in reports, analysts parse out the specific Traffic Source and Campaign Year.
As a Marketing Data Analyst, you are cleaning four tracking codes:
- Traffic Source (Column B): Extract the channel name starting at character 5 up to the next hyphen using
=MID(A2, 5, FIND("-", A2, 5)-5).
- Campaign Year (Column C): Extract the 4-digit year following the second hyphen using
=MID(A2, FIND("-", A2, 5)+1, 4).
- Google Campaigns Count (Cell B7): Count campaigns originating from
"GOOGLE" using COUNTIF(B2:B5, "GOOGLE").
- 2026 Campaigns Count (Cell C7): Count campaigns launching in
"2026" using COUNTIF(C2:C5, "2026").
Your Sheet Layout
Understanding the campaign parsing schedule:
| Cell Range |
Column Name |
Section |
Formula / Approach |
What It Does |
A2:A5 |
Tracking Code |
Raw Strings |
Structured campaign naming strings |
Read-only tracking tag keys. |
B2:B5 |
Traffic Source |
Channel Parsing |
=MID(A2, 5, FIND("-", A2, 5)-5) |
Extracts channel name (GOOGLE, META, TIKTOK). |
C2:C5 |
Campaign Year |
Date Parsing |
=MID(A2, FIND("-", A2, 5)+1, 4) |
Extracts 4-digit year (2025, 2026). |
B7 |
Google Campaigns |
Summary |
=COUNTIF(B2:B5, "GOOGLE") |
Total campaigns run on Google (2). |
C7 |
2026 Campaigns |
Summary |
=COUNTIF(C2:C5, "2026") |
Total initiatives scheduled for 2026 (3). |
Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.
1
In cells B2:B5, extract traffic source channel using MID and FIND.
2
In cells C2:C5, extract the 4-digit campaign year.
3
In cell B7, count total campaigns targeting "GOOGLE".
4
In cell C7, count total campaigns scheduled for year "2026".
Solution & Formula Breakdown
Let's examine how dynamic string slicing extracts text between delimiters:
1. Dynamic Channel Extraction with MID & FIND (B2:B5)
=MID(A2, 5, FIND("-", A2, 5)-5)
The code starts with SRC- (4 characters). Slicing starts at position 5:
SRC-GOOGLE-2026-US: Second hyphen is at position 11. Length = 11 - 5 = 6 → MID(..., 5, 6) = GOOGLE.SRC-META-2026-EU: Second hyphen is at position 9. Length = 9 - 5 = 4 → META.SRC-TIKTOK-2025-ASIA: Second hyphen is at position 11. Length = 11 - 5 = 6 → TIKTOK.SRC-GOOGLE-2026-LATAM: Evaluates to GOOGLE.
2. 4-Digit Year Extraction (C2:C5)
=MID(A2, FIND("-", A2, 5)+1, 4)
- Starts immediately after the second hyphen and extracts exactly 4 characters → 2026, 2026, 2025, 2026.
3. Summary Counts (B7 & C7)
- Cell B7 (Google Count):
=COUNTIF(B2:B5, "GOOGLE") → 2 campaigns.
- Cell C7 (2026 Count):
=COUNTIF(C2:C5, "2026") → 3 campaigns.
Adding the 3rd argument to FIND("-", A2, 5) tells Excel to search starting from character 5, automatically skipping past the very first hyphen in SRC-!