Spreadsheet seperti Microsoft Excel atau Google Sheet merupakan salah satu aplikasi perkantoran yang paling banyak digunakan sampai dengan saat ini. Dengan spreadsheet kita bisa mengolah dan menganalisa data dengan instant dan spontanitas.
Kita tentu mengetahui bahwa format email terdiri dari dua bagian, yaitu username dan domain yang dipisahkan dengan @, jadi untuk mendapatkan username nya kita bisa ambil teks sebelum karakter @.
=LEFT(A2,SEARCH("@",A2)-1)
Sedangkan domain nya kita bisa ambil teks setelah karakter @.
=RIGHT(A2,SEARCH("@",A2))
Selanjutnya kita bisa melakukan validasi atas username dan domain tersebut sebagai berikut:
1. Harus mengandung karakter @.
=ISNUMBER(SEARCH("@",A2)2. Tidak diawali selain huruf (a sampai z) dan angka (0 sampai 9), tidak case sensitif
=ISNUMBER(SUMPRODUCT(SEARCH(MID(LOWER(LEFT(A2,1)),ROW(INDIRECT("1:1")),1),"0123456789abcdefghijklmnopqrstuvwxyz")))3. Tidak diakhiri selain huruf (a sampai z) dan angka (0 sampai 9), tidak case sensitif
=ISNUMBER(SUMPRODUCT(SEARCH(MID(LOWER(RIGHT(A2,1)),ROW(INDIRECT("1:1")),1),"0123456789abcdefghijklmnopqrstuvwxyz")))4. Tidak terdapat double titik (..)
=ISERROR(SEARCH("..",A2))5. Tidak terdapat double underscore (__)
=ISERROR(SEARCH("__",A2))6. Username minimal 1 karakter
=LEN(LEFT(A2,SEARCH("@",A2)-1))>07. Username hanya berisi huruf (a sampai z), angka (0 sampai 9), titik (.), dash (-), dan underscore (_, contohnya pada email yahoo), tidak case sensitif
=ISNUMBER(SUMPRODUCT(SEARCH(MID(LOWER(LEFT(A2,SEARCH("@",A2)-1)),ROW(INDIRECT("1:"&LEN(LEFT(A2,SEARCH("@",A2)-1)))),1),"0123456789abcdefghijklmnopqrstuvwxyz._")))8. Username tidak diakhiri selain huruf (a sampai z) dan angka (0 sampai 9), tidak case sensitif
=ISNUMBER(SUMPRODUCT(SEARCH(MID(LOWER(RIGHT(LEFT(A2,SEARCH("@",A2)-1),1)),ROW(INDIRECT("1:1")),1),"0123456789abcdefghijklmnopqrstuvwxyz")))9. Domain minimal 4 karakter (termasuk titik)
=LEN(RIGHT(A2,LEN(A2)-SEARCH("@",A2)))>310. Domain harus mengandung titik (.)
=ISNUMBER(SEARCH(".",RIGHT(A2,LEN(A2)-SEARCH("@",A2))))11. Domain hanya berisi huruf (a sampai z), angka (0 sampai 9), dash (-), dan titik (.), tidak case sensitif
=ISNUMBER(SUMPRODUCT(SEARCH(MID(LOWER(RIGHT(A2,LEN(A2)-SEARCH("@",A2))),ROW(INDIRECT("1:"&LEN(RIGHT(A2,LEN(A2)-SEARCH("@",A2))))),1),"0123456789abcdefghijklmnopqrstuvwxyz.-")))12. Domain tidak diawali selain huruf (a sampai z) dan angka (0 sampai 9), tidak case sensitif
=ISNUMBER(SUMPRODUCT(SEARCH(MID(LOWER(RIGHT(A2,LEN(A2)-SEARCH("@",A2))),ROW(INDIRECT("1:1")),1),"0123456789abcdefghijklmnopqrstuvwxyz")))Dengan demikian, untuk melakukan validasi format email di spreadsheet, kita bisa menggabungkan beberapa validasi tersebut menggunakan fungsi AND sehingga rumus nya menjadi sebagai berikut :
=AND(ISNUMBER(SEARCH("@",A2)),ISNUMBER(SUMPRODUCT(SEARCH(MID(LOWER(LEFT(A2,1)),ROW(INDIRECT("1:1")),1),"0123456789abcdefghijklmnopqrstuvwxyz"))),ISNUMBER(SUMPRODUCT(SEARCH(MID(LOWER(RIGHT(A2,1)),ROW(INDIRECT("1:1")),1),"0123456789abcdefghijklmnopqrstuvwxyz"))),ISERROR(SEARCH("..",A2)),ISERROR(SEARCH("__",A2)),LEN(LEFT(A2,SEARCH("@",A2)-1))>0,ISNUMBER(SUMPRODUCT(SEARCH(MID(LOWER(LEFT(A2,SEARCH("@",A2)-1)),ROW(INDIRECT("1:"&LEN(LEFT(A2,SEARCH("@",A2)-1)))),1),"0123456789abcdefghijklmnopqrstuvwxyz.-_"))),ISNUMBER(SUMPRODUCT(SEARCH(MID(LOWER(RIGHT(LEFT(A2,SEARCH("@",A2)-1),1)),ROW(INDIRECT("1:1")),1),"0123456789abcdefghijklmnopqrstuvwxyz"))),LEN(RIGHT(A2,LEN(A2)-SEARCH("@",A2)))>3,ISNUMBER(SEARCH(".",RIGHT(A2,LEN(A2)-SEARCH("@",A2)))),ISNUMBER(SUMPRODUCT(SEARCH(MID(LOWER(RIGHT(A2,LEN(A2)-SEARCH("@",A2))),ROW(INDIRECT("1:"&LEN(RIGHT(A2,LEN(A2)-SEARCH("@",A2))))),1),"0123456789abcdefghijklmnopqrstuvwxyz.-"))),ISNUMBER(SUMPRODUCT(SEARCH(MID(LOWER(RIGHT(A2,LEN(A2)-SEARCH("@",A2))),ROW(INDIRECT("1:1")),1),"0123456789abcdefghijklmnopqrstuvwxyz"))))
Demikian artikel kali ini, semoga bermanfaat.