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 |
After
| Customer | Valid? | |
|---|---|---|
| Ravina | ravina@example.com | Yes |
| Shahrukh | shahrukh@example.com | Yes |
| Ruhi | ruhi.bagale@example.com | Yes |
| Raja | raja@examplecom | Check |
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.