Rumus Merapikan Data Nomor HP pada Kolom Google Sheets

Beberapa waktu lalu, saya diminta seorang teman untuk merapikan sekumpulan data dari Google Sheets yang diperoleh dari sebuah Google Form. Data ini rencananya akan digunakan sebagai buku tamu untuk undangan yang sudah mendaftarkan diri melalui form tersebut. Untuk memudahkan pemahaman, pada tutorial kali ini saya menggunakan data dummy sebagai berikut.

Data tersebut sebetulnya isinya sangat sederhana, terdiri dari nama, nomor telepon, dan email. Sekilas ada beberapa kolom yang format isiannya kurang rapi dan tidak konsisten. Misal pada kolom nama, ada nama yang per katanya masih menggunakan gabungan huruf kecil, huruf besar, dan huruf kapital. Padahal saya ingin bagian nama itu seluruhnya ingin ditampilkan dengan format huruf kapital secara seragam. Nama ‘tan Malaka’ dibuat menjadi ‘Tan Malaka’, ‘SOEKARNO’ dibuat menjadi ‘Soekarno’, dan seterusnya.

Hal lain yang perlu diperhatikan adalah format nomor telepon yang tidak konsisten. Ada nomor yang menggunakan awalan 08xxxxxxx, +628xxxxxxxx, 081-xxx-xxx-xxx, +628-xxx-xxx-xxx, dan seterusnya. Data semacam ini rasanya juga perlu disamakan formatnya untuk dua tujuan. Tujuan pertama adalah untuk merapikan data dan yang kedua adalah untuk memudahkan kita saat membaca nomor-nomor seluler tersebut.

Berikut langkah-langkah yang bisa dilakukan untuk merapikan data tersebut. Perlu dicatat bahwa langkah-langkah berikut hanya berlaku untuk Google Sheets saja, karena jika menggunakan Excel akan ada beberapa penyesuaian.

Langkah pertama adalah membuat tabel kosongan yang isi field-nya sama dengan tabel aslinya.
Untuk merapikan kolom nama dengan format huruf kapital pada setiap kata, bisa gunakan rumus “=PROPER(B2)”. Jika menginginkan namanya diubah menjadi huruf besar semua, bisa menggunakan rumus “=UPPER(B2)”.
Kolom telepon akan diproses dengan menggunakan dua langkah. Langkah pertama bertujuan untuk menghilangkan karakter atau simbol yang tidak perlu, terutama untuk nomor-nomor yang masih mengandung simbol “+” dan “-“. Kemudian juga perlu untuk mengubah karakter yang mengandung “62” di awal nomor telepon, karena nomor tersebut digunakan sebagai pengenal kode negara Indonesia. Semua itu bisa diselesaikan dengan rumus reguler expression atau dalam bahasa pemrograman biasa dikenal dengan regex. Kebetulan Google Sheets sudah mendukung dan menyediakan rumus ini. Rumus atau fungsi yang bisa dipakai dalam kasus ini adalah kombinasi ARRAYFORMULA, REGEXEXTRACT, dan REGEXREPLACE. Formula yang digunakan untuk langkah pertama merapikan kolom nomor telepon adalah “=ARRAYFORMULA(“0″&REGEXEXTRACT(REGEXREPLACE(C3:C12,”\D”,””),”8\d+”))”. Output tabel menjadi seperti ini:
Langkah kedua untuk merapikan nomor telepon bertujuan untuk menyeragamkan format penulisan nomor telepon. Misalnya, saya ingin membuat format penulisan nomor telepon dengan cara memisahkan tiga digit pertama, kedua, dan seterusnya dari sebelah kiri, lalu memisahkan per tiga digit dengan simbol “-”. Rumus atau fungsi yang bisa dipakai dalam kasus ini adalah kombinasi TEXTJOIN dan REGEXEXTRACT. Formula yang digunakan adalah “=TEXTJOIN(“-“,TRUE,REGEXEXTRACT(H3,”(\d{4})(\d{3})(\d+)”))”. Output tabel menjadi seperti ini:
Langkah selanjutnya yaitu merapikan kolom email. Karena saya ingin kolom email berisi daftar email dengan huruf kecil semua, maka saya cukup menggunakan rumus “=LOWER(D3)”. Hasilnya tampak seperti gambar di bawah ini:
Dengan sedikit memodifikasi formula di atas, saya bisa mengubah output tabel menjadi seperti di bawah ini:Keterangan rumus:
- Nama: “=UPPER(B3)”
- Nomor Telepon (1): “=ARRAYFORMULA(REGEXEXTRACT(REGEXREPLACE(C3:C12,”\D”,””),”8\d+”))”
- Nomor Telepon (2): “=TEXTJOIN(“-“,TRUE,”+62″,REGEXEXTRACT(H16,”(\d{3})(\d{3})(\d+)”))”
- Nomor Telepon (3): “=LEFT(H3,LEN(H3)-3)&”xxx”
- Email: “=UPPER(D3)”