5.11 E-mail Addresses
Before
| Customer | E-mail (raw) |
|---|---|
| Ravina | " Ravina@Example.COM" |
| Shahrukh | SHAHRUKH@example.com |
| Ruhi | ruhi.bagale@example .com |
| Raja | raja@examplecom |
About this data: Teaching sample for a fictional shop “GreenField Dairy”. Figures and names (e.g. Aarti Kulkarni) are made up for this lesson on e mail addresses.
After
| Customer | Valid? | |
|---|---|---|
| Ravina | ravina@example.com | Yes |
| Shahrukh | shahrukh@example.com | Yes |
| Ruhi | ruhi.bagale@example.com | Yes |
| Raja | raja@examplecom | Check |
About this data: Demo table only — not real customer or company data. Built to practise e mail addresses.
Steps in Excel
- Lower case, no spaces anywhere:
=LOWER(SUBSTITUTE(TRIM(B2)," ","")). -
Basic validity check (one @, a dot after it, no spaces):
=IF(AND(LEN(C2)-LEN(SUBSTITUTE(C2,"@",""))=1, ISNUMBER(FIND(".",C2,FIND("@",C2)))),"Yes","Check") -
Extract user and domain:
=LEFT(C2,FIND("@",C2)-1)and=MID(C2,FIND("@",C2)+1,100)(or TEXTBEFORE/TEXTAFTER in Microsoft 365).
Ravindra Bagale's Tip
Many students make the mistake of applying PROPER to e-mail addresses – it looks odd, like "Ravina@Example.Com". Always keep e-mails in LOWER case. And the validity formula is only a basic check – look at the rows marked "Check" manually, and don't add an @ or a dot yourself.
Ravindra Bagale's Tip – मराठी
E-mail ला PROPER लावण्याची चूक बरेच students करतात – "Ravina@Example.Com" असं विचित्र दिसतं. E-mail नेहमी LOWER मध्ये ठेवा. आणि validity formula फक्त basic check आहे – "Check" आलेल्या rows हाताने बघा, स्वतःहून @ किंवा dot add करू नका.
Ravindra Bagale's Tip – हिंदी
E-mail पर PROPER लगाने की गलती बहुत से students करते हैं – "Ravina@Example.Com" जैसा अजीब दिखता है. E-mail हमेशा LOWER में रखो. और validity formula सिर्फ़ basic check है – जिन rows में "Check" आया उन्हें खुद देखो, अपनी तरफ़ से @ या dot मत जोड़ो.
Practice task
Clean ten customer e-mails, mark invalid ones, and count how many customers use each domain with COUNTIF on the extracted domain.
Loading practice tasks...
- Clean and small letters (Easy): Clean B2.
- Small letters (Easy): Show B6 in small letters.
- Where is the @? (Easy): Find the position of "@" in B3.
- Has an @? (Easy): Does B5 have an "@"? (TRUE or FALSE)
- User name part (Medium): Get the text before "@" in B3.
- Domain part (Medium): Get the text after "@" in B3.
- Count @ signs (Medium): How many "@" are in B4?
- Space inside? (Medium): Does B7 have a space? (TRUE or FALSE)
- Valid or check (B3) (Hard): Show "Yes" if B3 has exactly one @ and no space, else "Check".
- Valid or check (B4) (Hard): Same test for B4.
- Emails with no @ (Hard): How many emails in B2:B7 have no "@"?
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
