Data kontak hasil ekspor formulir atau CRM sering masuk ke Excel dengan nama, alamat, dan kode pos menumpuk di satu kolom. Cara memisahkan nama dan alamat di Excel tanpa rumus bisa dilakukan lewat fitur Text to Columns dan Flash Fill. Keduanya sudah tersedia di Excel versi desktop maupun Microsoft 365, jadi Anda tidak perlu memasang add-in apa pun.
Sebelum mulai, pastikan penulisan data cukup konsisten. Kalau pemisah antar bagian berbeda-beda dalam satu kolom, hasil pemisahan akan berantakan. Periksa dulu beberapa baris teratas untuk melihat polanya.
Siapkan Data Sebelum Dipisah
Buat salinan lembar kerja sebelum mengubah apa pun. Klik kanan tab sheet, pilih Move or Copy, centang Create a copy. Kalau hasil pemisahan keliru, Anda tinggal menghapus salinannya.
Sisakan beberapa kolom kosong di sebelah kanan data. Text to Columns menulis hasilnya ke kolom sebelah kanan dan bisa menimpa isi yang sudah ada di sana tanpa peringatan. Klik header kolom di sebelah kanan data, tekan Ctrl+Shift+Plus untuk menyisipkan kolom kosong sebanyak perkiraan jumlah bagian data.
1. Pisahkan Alamat dengan Text to Columns
Text to Columns bekerja dengan membelah isi sel berdasarkan karakter pemisah, misalnya koma, titik koma, atau spasi. Fitur ini cocok untuk alamat yang setiap bagiannya dipisah koma.
Langkah Text to Columns
- Blok kolom yang berisi alamat, termasuk baris judulnya.
- Buka tab Data, klik Text to Columns.
- Pilih Delimited, klik Next.
- Centang pemisah yang dipakai. Kalau alamat ditulis “Jalan Melati No. 5, Bandung, 40123”, centang Comma.
- Buka tab Data Preview untuk melihat hasilnya. Kalau ada bagian yang masih menyatu, tambahkan centang pada pemisah lain.
- Klik Next, lalu pada bagian Column data format pilih General untuk semua kolom.
- Isi Destination dengan sel paling kiri dari kolom kosong yang sudah disiapkan.
- Klik Finish.
Kalau tujuan tidak diisi, Excel menimpa kolom di sebelah kanan data asli.
Perhatikan format kode pos. Angka seperti 40123 akan berubah menjadi 40123 biasa, tetapi kode pos dengan nol di depan bisa kehilangan nol tersebut. Ubah format kolom kode pos menjadi Text sebelum menjalankan Text to Columns, atau pilih format Text pada langkah keenam untuk kolom yang bersangkutan.
2. Rapikan Nama dengan Flash Fill
Nama di kolom kontak biasanya tidak punya pemisah tetap. Ada yang menulis nama lengkap, nama depan saja, atau menambahkan gelar di belakang. Flash Fill mengenali pola dari contoh yang Anda tulis, lalu menerapkannya ke baris lain.
Langkah Flash Fill
- Buat kolom baru di sebelah kolom nama asli, beri judul “Nama Depan”.
- Pada baris pertama, ketik manual nama depan dari data di kolom sebelahnya. Misalnya sel asli berisi “Rina Kusuma”, ketik “Rina”.
- Turun ke baris kedua, ketik nama depan dari data kedua.
- Setelah dua contoh, Excel biasanya menampilkan saran abu-abu untuk baris berikutnya. Tekan Enter untuk menerimanya.
Kalau saran tidak muncul, pilih seluruh kolom hasil dan tekan Ctrl+E, atau buka tab Data lalu klik Flash Fill. Tekan Ctrl+Z bila hasilnya salah, tulis ulang contoh ketiga, lalu jalankan lagi.
Flash Fill berhenti bekerja saat polanya berubah. Data yang tercampur antara “Rina Kusuma” dan “Kusuma, Rina” harus dibagi menjadi dua kelompok dan diproses terpisah. Mengurutkan kolom berdasarkan pola penulisan lebih dulu akan menghemat waktu.
3. Gabungkan Kembali dengan Operator &
Setelah nama dan alamat terpisah, kadang Anda perlu menggabungkannya dalam urutan berbeda, misalnya “Nama Lengkap, Kota”. Tulis rumus sederhana di kolom baru:
=B2 & ", " & D2Ganti B2 dengan sel nama dan D2 dengan sel kota. Tanda kutip berisi koma dan spasi sebagai pemisah. Salin rumus ke bawah dengan menarik titik di sudut kanan bawah sel.
Kalau ingin hasilnya menjadi teks tetap, blok kolom hasil, tekan Ctrl+C, lalu klik kanan dan pilih Paste Special > Values. Kolom rumus bisa dihapus setelahnya.
4. Bersihkan Sisa Spasi dan Karakter Tak Terlihat
Hasil pemisahan sering menyisakan spasi di awal atau akhir teks. Spasi ini membuat pengurutan dan pencarian tidak akurat karena “Bandung” dan “Bandung ” dianggap berbeda.
- Pilih kolom hasil, buka Data > Text to Columns > Finish untuk memicu pembersihan cepat. Cara ini tidak mengubah apa pun selain memangkas spasi di tepi teks.
- Untuk karakter tak terlihat seperti spasi non-breaking dari halaman web, gunakan Find and Replace. Tekan Ctrl+H, masukkan spasi pada kolom Find, kosongkan Replace, lalu Replace All.
- Periksa sel yang tampak kosong tapi ikut terhitung saat menggunakan fungsi COUNTA. Sel seperti ini biasanya menyimpan spasi tunggal.
5. Cek Hasil Sebelum Dipakai
Lakukan pemeriksaan cepat sebelum data dipakai untuk surat massal atau impor ke sistem lain.
- Urutkan kolom kota dan lihat apakah ada nama kota yang terpecah karena ejaan berbeda.
- Hitung jumlah baris antara data asli dan data hasil dengan fungsi COUNTA. Jumlahnya harus sama.
- Cari sel kosong di kolom nama depan atau kota. Sel kosong menandakan pola tertentu tidak terbaca.
- Simpan file sebagai salinan baru dengan nama berbeda, misalnya Data_Kontak_Rapi.xlsx, agar data asli tetap utuh.
Kalau ada kolom yang isinya bercampur antara nama dan alamat tanpa pemisah tetap, pemisahan otomatis memang tidak bisa diandalkan. Pengisian manual untuk kelompok data kecil biasanya lebih cepat daripada memaksakan pola yang tidak konsisten.
Ringkasan
Text to Columns menangani data yang punya pemisah jelas seperti koma, sedangkan Flash Fill menebak pola dari contoh yang Anda tulis. Selalu siapkan kolom kosong di kanan, simpan salinan file, dan periksa format kode pos sebelum menjalankan pemisahan.
Pertanyaan yang Sering Diajukan
Apakah Flash Fill tersedia di Excel versi lama?
Flash Fill muncul pertama kali di Excel 2013 dan tersedia di versi setelahnya, termasuk Microsoft 365. Kalau menu Flash Fill tidak ada di tab Data, kemungkinan versi Excel Anda lebih lama atau fitur ini dinonaktifkan lewat Options > Advanced.
Kenapa nol di depan kode pos hilang setelah dipisah?
Excel menganggap angka seperti 01234 sebagai bilangan dan membuang nol di depan. Ubah format kolom tujuan menjadi Text sebelum menjalankan Text to Columns, atau pilih format Text pada langkah pengaturan kolom di wizard.
Bisakah memisahkan nama dan alamat tanpa menambah kolom kosong?
Bisa, tetapi hasilnya akan menimpa kolom di sebelah kanan data asli. Untuk menghindari kehilangan data, sisipkan kolom kosong terlebih dulu atau pindahkan proses ke sheet baru dengan menyalin data ke sana.
