LOWER to make emails comparable
Normalise case before you compare, count or remove duplicates.
Task:
Sign-ups came in from three forms, each capitalising emails differently, and you are about to de-duplicate the list. In C2:C6 put each email in lower case.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
Variations of UPPER, LOWER, PROPER
0/3The same formula in the other shapes it takes at work.
- UPPER, LOWER, and PROPER Functions
Change the case of text using case conversion functions.
CoreBeginner - LOWER to make emails comparable
Normalise case before you compare, count or remove duplicates.
Beginner - UPPER to build a code
Turn a name and a number into a consistent upper-case reference.
Intermediate
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 | |
|---|---|---|---|
| 1 | Source | Clean email | |
| 2 | Web | Priya.Shah@Example.com | |
| 3 | App | priya.shah@example.com | |
| 4 | Event | TOM.REID@EXAMPLE.COM | |
| 5 | Web | Tom.Reid@example.com | |
| 6 | App | lee.chan@example.com |
What this exercise teachesMay contain the answer
Email addresses are not case-sensitive, but a duplicate check comparing text exactly would treat Priya.Shah and priya.shah as two people. Lower-casing first makes equal things look equal, so the de-duplication finds them.