Standardize signup emails and pull out each username
LOWER folds case out of an email for de-duplication; LEFT and FIND together peel off the part before the @ without knowing how long it is.
Task:
You run marketing operations for a small online retailer. This week's newsletter signups came in through three different forms, so the emails your export gives you are in inconsistent case — the mail platform treats "Priya.Patel@GMAIL.com" and "priya.patel@gmail.com" as two different addresses, which would double-count some subscribers. Before loading the list, put a lowercase version of each email in column C for de-duplication, and each signup's username — the part before the @, in its original case — in column D for a personalized greeting.
Learning Objectives:
- Normalize text case with LOWER for matching or de-duplication
- Locate a character's position with FIND rather than assuming a fixed offset
- Combine FIND with LEFT to extract text up to a delimiter of unknown length
Solve without hints for +5 XP
Interactive Spreadsheet
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Email (lowercase) | Username | |
| 2 | Priya Patel | Priya.Patel@GMAIL.com | ||
| 3 | Diego Ramirez | DRamirez99@Yahoo.COM | ||
| 4 | Aisha Khan | aisha_khan@Outlook.com | ||
| 5 | Marcus Lee | MLee2026@HOTMAIL.com |
What this exercise teaches (contains the answer)
LOWER(B2) forces every letter to lowercase without touching the digits or punctuation, so two spellings of the same address collapse to one string a de-duplication step can match. LEFT(B2,FIND("@",B2)-1) works for any username length because it doesn't hardcode one: FIND("@",B2) locates the @ fresh in each row, and subtracting 1 stops LEFT one character short of it, which is exactly where the username ends. A fixed count like LEFT(B2,10) would only be right for the one row whose username happens to be ten characters long.