RapiNomor
Rapikan Nomor

Cara merapikan nomor HP di Excel

Satu kolom berisi 0812-3456-7890, +62 812 3456 7890, dan 81234567890 sekaligus? Ikuti lima langkah ini dengan rumus Excel, atau pakai cara cepat tanpa rumus di bagian akhir.

Terakhir diperbarui:

Kenapa nomor HP di Excel berantakan

Daftar nomor HP biasanya dikumpulkan dari banyak sumber: Google Form, ekspor marketplace, rekap chat WhatsApp, dan input manual admin. Setiap orang menulis nomor dengan caranya sendiri, lalu Excel menambah masalah karena membaca nomor sebagai angka. Hasilnya, satu kolom bisa berisi semua bentuk ini:

Tertulis di ExcelMasalahnya
0812-3456-7890Ada tanda hubung
+62 812 3456 7890Ada tanda +, spasi, dan kode negara
(0812) 3456 7890Ada tanda kurung
81234567890Angka 0 di depan hilang karena Excel
6.28123E+12Berubah jadi notasi ilmiah
0812xxx / 0857xxxDua nomor dalam satu sel

Aplikasi WhatsApp blast, CRM, dan link wa.me umumnya hanya menerima satu format, yaitu 628xxx tanpa spasi atau simbol. Nomor yang formatnya berbeda akan gagal diimpor atau gagal terkirim. Lima langkah di bawah ini menyeragamkan semuanya.

Rumus di artikel ini memakai koma sebagai pemisah. Jika Excel Anda memakai pengaturan bahasa Indonesia dan muncul pesan kesalahan rumus, ganti setiap koma (,) dengan titik koma (;).

Langkah 1: Atur kolom sebagai Text

Sebelum mengolah apa pun, buat kolom kosong untuk hasil dan atur formatnya sebagai teks agar Excel tidak membuang angka 0 di depan nomor:

  1. Blok kolom tempat hasil akan ditaruh, misalnya kolom B dan C.
  2. Klik kanan, pilih Format Cells, lalu pilih Text dan klik OK.

Jika angka 0 di kolom asli sudah terlanjur hilang, tidak apa-apa. Rumus di langkah 3 akan memulihkannya. Penjelasan lengkapnya ada di artikel cara mengatasi angka 0 yang hilang di Excel.

Langkah 2: Buang spasi, tanda hubung, dan simbol

Misalkan nomor asli ada di kolom A mulai baris 2. Ketik rumus ini di sel B2 untuk membuang spasi, tanda hubung, tanda kurung, tanda plus, dan titik:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),"-",""),"(",""),")",""),"+",""),".","")

Setiap SUBSTITUTE mengganti satu jenis karakter dengan teks kosong. Tarik rumus ke bawah sampai baris terakhir dengan klik dua kali kotak kecil di pojok kanan bawah sel. Hasilnya, +62 812-3456-7890 menjadi 6281234567890 dan (0812) 3456 7890 menjadi 081234567890.

Langkah 3: Seragamkan awalan jadi 62

Sekarang nomor di kolom B hanya berisi angka, tetapi awalannya masih campur: ada yang 08, 62, 0062, atau langsung 8 karena angka 0 hilang. Ketik rumus ini di sel C2:

=IF(LEFT(B2,4)="0062",MID(B2,3,20),IF(LEFT(B2,2)="62",B2,IF(LEFT(B2,1)="0","62"&MID(B2,2,20),IF(LEFT(B2,1)="8","62"&B2,B2))))

Cara kerja rumus ini, diperiksa berurutan:

  • Berawalan 0062: dua angka 0 pertama dibuang.
  • Berawalan 62: dibiarkan karena sudah benar.
  • Berawalan 0: angka 0 diganti 62.
  • Berawalan 8: ini nomor yang kehilangan angka 0, jadi ditambah 62 di depannya.

Butuh format 08xxx saja, atau ingin tahu cara lain seperti Flash Fill? Baca cara mengubah nomor 08 jadi 62 di Excel.

Setelah selesai, ubah rumus menjadi nilai tetap agar kolom A dan B boleh dihapus: blok kolom C, tekan Ctrl + C, klik kanan, lalu pilih Paste Special → Values.

Langkah 4: Cek nomor yang tidak valid

Nomor HP Indonesia dalam format 628xxx panjangnya 11 sampai 14 digit dan selalu berawalan 628. Rumus ini menandai nomor yang perlu dicek ulang, misalnya nomor telepon rumah, nomor terpotong, atau nomor yang tertulis dengan huruf O:

=IF(AND(LEFT(C2,3)="628",LEN(C2)>=11,LEN(C2)<=14,ISNUMBER(--C2)),"OK","Cek")

Saring kolom ini dengan Data → Filter untuk melihat semua baris berlabel “Cek”. Nomor yang tampil seperti 6.28123E+12 tidak bisa diperbaiki dengan rumus karena digit terakhirnya sudah hilang. Ambil ulang nomor itu dari data sumber.

Langkah 5: Hapus nomor duplikat

Setelah semua nomor seragam, barulah duplikat bisa dideteksi dengan benar. Blok datanya, lalu pilih Data → Remove Duplicates dan centang kolom nomor yang sudah rapi. Jangan lakukan ini sebelum langkah 3, karena Excel menganggap 0812-3456-7890 dan +62 812 3456 7890 sebagai dua nomor berbeda.

Ingin menandai duplikat tanpa menghapusnya? Lihat cara menghapus nomor HP duplikat di Excel.

Cara cepat tanpa rumus

Lima langkah di atas bisa memakan 30 sampai 60 menit, dan tetap ada kasus yang terlewat, seperti dua nomor dalam satu sel atau nomor yang menempel tanpa pemisah. RapiNomor mengerjakan semuanya sekaligus:

  1. Blok kolom nomor HP di Excel lalu salin, atau siapkan file .xlsx, .xls, atau .csv.
  2. Buka alat RapiNomor, lalu tempel teksnya atau seret filenya.
  3. Pilih format hasil, misalnya 628xxx. Hasil langsung muncul: duplikat dan nomor tidak valid ditandai beserta alasannya.
  4. Klik Unduh Excel. Kolom lain seperti nama dan alamat tetap utuh, dan ada kolom link WhatsApp yang bisa langsung diklik.

Semua proses berjalan di browser Anda, jadi data nomor pelanggan tidak dikirim ke server mana pun.

Panduan lainnya