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.
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.
Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.
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 |
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.