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
E-mail la PROPER lavnyachi chuk khup students kartat – "Ravina@Example.Com" asa vichitra disto. E-mail nehmi LOWER madhe theva. Ani validity formula fakt basic check aahe – "Check" aalelya rows manually bagha, swatahun @ kiwa dot add karu naka.
Practice task
Clean ten customer e-mails, mark invalid ones, and count how many customers use each domain with COUNTIF on the extracted domain.