Apa gunanya split cell dan bagaimana caranya memisahkan content sel menjadi beberapa bagian? Prosedur ini sangat berguna untuk membantu mempermudah analisa data. Ada bebagai alternatif cara untuk melakukan split cells. Kita bisa menggunakan fitur Text to Columns, Flash Fill dan Formula. Lebih lanjut dalam catatan pelajaran excel kali ini akan dibahas berbagai alternative cara memisahkan konten sel sehingga kita bisa memilih tehnik terbaik dan cocok sesuai kebutuhan serta pola data yang dihadapi.
- Split Cell Menggunakan Fitur Text To Columns
- Memisahkan Konten Sel Menggunakan Fill Flash (Excel 2013 Atau Lebih Baru)
- Split Data menggunakan Formula
Pada umumnya, pemisahan konten sel excel diperlukan pada dua kasus.
Yang paling sering adalah ketika kita meng-import data dari sumber external dimana semua informasi berada dalam satu kolom. Mau tidak mau data tersebut perlu dipecah atau dipisahkan menjadi beberapa kolom supaya bisa diolah lebih lanjut.
Atau mungkin kita memiliki sebuah tabel, tetapi ada satu kolom dalam tabel tersebut yang berisi gabungan beberapa informasi. Nah, supaya tabel lebih mudah dianalisa, (misalnya menggunakan filtering, sorting dan pivot table) maka kolom yang berisi informasi gabungan ini perlu di pecah menjadi beberapa kolom yang berisi informasi tunggal.
Cara Memisahkan Konten Sel Menggunakan Text To Columns.
Fitur Text to Columns ini sepertinya tersedia pada semua versi excel, setidaknya pada versi excel yang sudah saya cek yaitu versi excel 2003, 2007, 2010, 2013 dan 2016.
Fitur ini memungkinkan kita untuk memisahkan data text berdasarkan pemisah text (delimiter) yang digunakan seperti koma, titik koma, spasi atau gabungan beberapa karakter yang berperan sebagai delimiter.
Mari kita lihat bagaimana fitur ini bekerja.
Memisahkan Cells Content berdasarkan delimiter
Anggaplah kita memliki data yang di-import dari program external. Data tersebut berisi list nama product, ID product, konsumen dan sebagainya. Satu baris data tersimpan dalam satu sel. Ada beberapa baris data dengan sruktur yang sama, semuanya terletak dalam kolom yang sama
Perhatikan contoh tabel di atas. Semua data terletak pada satu sel dalam kolom A. Tiap item data dipisahkan oleh delimiter berupa tanda koma (,).
Kemudian kita ingin memidahkan data tersebut menjadi beberapa kolom sehingga memudahkan dalam proses olah data selanjutnya.
Untuk memisahkan data menjadi beberapa kolom, maka lakukan langkah berikut.
- Seleksi kolom A (atau tergantung lokasi kolom data)
- Kemudian dari tab Data, grup Data Tools, klik Text To Columns
- Langkah selanjutnya: pilih opsi Delimited, kemudian klik tombol NEXT
- Centang checkbox Comma, kemudian perhatikan pada kotak Data Preview untuk memastikan bahwa pemisahan konten sel sudah sesuai dengan yang diharapkan.
- Jika yakin sudah benar, maka selanjutnya klik tombol Next.
Setelah itu maka kita akan dibawa pada kotak dialog berikutnya yang berguna untuk pengaturan format dan destinasi sel untuk menyimpan hasil pemisahan konten.
Format Data:
Secara default, excel membaca semua format sel sebagai general. Namun kita bisa memberitahu excel jika ada beberapa fragmen data yang harus diperlakukan sebagai format lainnya. Misalnya sebagai format tanggal. Ini diperlukan jika ada bagian kontent sel yang berupa tanggal.
Misalnya pada contoh di atas, tanggal terletak pada bagian akhir atau kolom paling kanan. Pada Data preview, seleksi kolom tersebut, kemudian pada opsi date, pilih format tanggal yang sesuai. Dalam contoh diatas adalah YMD.
Destination:
Secara default, range destinasi akan menimpa kolom original dengan penambahan kolom berikutnya sesuai jumlah kolom hasil pemisahan. Namun destinasi ini dapat kita rubah dengan cara klik collapse dialog icon yang terletak disebelah kanan kotak destination. Kemudian dengan menggunakan mouse, pilih cell pada range distinasi. Kita bisa juga mengetik referensi sel secara langsung dalam box.
Catatan:
Jika beberapa kolom tidak kita perlukan, maka kita dapat mencegah kolom tertentu supaya tidak di-import sebagai hasil pemisahan. Caranya seleksi kolom tersebut pada data preview, kemudian tick opsi Do Not Import Column (skip) pada bagian data format.
Langka terakhir: klik tombol Finish. Sampai pada tahap ini, kita sudah berhasil memisahkan konten sel menjadi beberapa bagian dalam kolom terpisah, seperti dapat dilihat pada screenshot di bawah ini.
Untuk memudahkan analisa dan olah data lebih lanjut, kita bisa menambahkan header kolom yang sesuai terhadap tabel di atas.
Bagaimana memisahkan text berdasarkan lebar (fixed width)
Bagian ini menjelaskan bagaimana untuk membagi konten sel berdasarkan jumlah karakter yang ditentukan. Untuk memudahkan pemahaman, silahkan perhatikan contoh berikut:
Anggaplah kita memiliki data Nomor Induk Karyawan (NIK) dan nama karyawan yang terletak dalam satu kolom. NIK dan nama karyawan dipisahkan oleh spasi.
Kemudian kita ditugaskan untuk memisahkan NIK dan Nama Karyawan pada dua kolom yang terpisah. Karena semua NIK terdiri dari jumlah angka yang konstan yaitu 6 karakter, maka salah satu cara yang efektif untuk mengerjakan tugas ini adalah menggunakan Text to Columns Fixed width.
Mulai Convert text to columns sebagaimana dijelaskan pada contoh pertama. Kemudian pilih Fixed width and click Next.
Set lebar masing – masihg kolom dengan menggunakan Data Preview, sebagaimana diperlihakan dalam screenshot di bawah.
Perhatikan garis vertikal pada gambar di atas. Garis tersebut mewakili pemisah kolom. Untuk menciptakan sebuah garis pemisah baru maka anda cukup klik posisi yang diinginkan.
Untuk menghilangkan garis pemisah, cukup lakukan double klik garis yang akan dihapus tersebut. Untuk memindahkan garis ke posisi lainnya tinggal geser atau drag menggunakan mouse.
Langkah selanjutnya, pilih data format dan destination untuk memisahkan sel secara tepat sebagaimana dilakukan pada contoh sebelumnya, dan klik tombol Finish untuk menyelesaikannya.
Memisahkan konten sel menggunakan Fill Flash
Jika anda menggunakan excel 2013 atau versi yang lebih baru, maka anda akan sangat terbantu dengan adanya fitur Flash Fill. Fitur ini tidak hanya bisa mengisi sel dengan data, tetapi juga bisa memecah konten sel menjadi beberapa bagian. Dan hebatnya lagi, kita bisa menyisipkan text tertentu.
Fitur Flash Fill bekerja dengan cara menganalisa pola data yang anda input pada sebuah sel, kemudian meneruskan fill sel lainnya mengikuti pola tersebut.
Dengan menggunakan contoh sebelumnya berupa data Nomor Induk + Nama Karyawan.
- Ketik pada salah satu sel disamping kolom data tersebut, misalnya ketik nama depan.
- Seleksi range yang ingin diisi sampai baris akhir tabel.
- Dari tab Data, klik Flash Fill
Perhatikan screenshot berikut:
Maka baris lainnya akan terisi data mengikuti pola data yang sudah diketik duluan.
Cobalah untuk berexperimen mengetikan pola text tertentu. Misalnya menambahkan text tambahan seperti Sdr pada salah satu sel dilanjutkan dengan proses Flash Fill. Maka hasilnya sangat mengagumkan: semua sel akan mendapatkan tambahan text Sdr.
Untuk mengambil bagian data lainnya maka dapat melakukan flash fill lagi pada kolom selanjutnya. Misalnya untuk mengambil Nomor Induk Karyawan (NIK)
Ketik Nomor induk karyawan pada satu sel yang sesuai atau sebaris dengan data originalnya. Kemudian seleksi range yang akan diisi, dan klik tombol Flash Fill seperti diilustrasikan dalam gambar berikut:
Shortcut Flash Fill.
Selain dengan cara klik tombol Flash Fill, untuk menjalan kan fitur ini, kita juga bisa menggunakan shortcut keyboard CTR + E. Cara ini tentu saja sangat menghemat waktu anda.
Alternative kedua adalah dengan menggunakan kursor mouse. Caranya posisikan kursor pada bagian pojok bawah sel yang sudah diisi sampai kursor berubah menjadi tanda plus (+), kemudkan klik kanan dan drag ke arah bawah. Selanjutnya lepaskan mouse, makan akan muncul contextual menu. Lalu klik Flash Fill pada contextual menu.
Untuk lebih jelasnya perhatikan sreenshot berikut:
Flash Fill juga dapat bekerja secara otomatis. Kita cukup menyeleksi range disamping kolom data original, kemudian ketikan 2 atau 3 sel, maka otomatis excel akan memperlihatkan list pada sel berikutnya. Jika anda merasa cocok dengan pola data tersebut, langsung saja tekan tombol Enter.
Catatan:
Jika fitur Flash Fill otomatis tidak aktif, maka kita bisa mengatifkannya melalui Tab File > Options, kemudian klik Advanced. Selanjutnya pada bagian Opsi Editing, centang checkbox Automatically Flash Fill
Memisahkan Konten Sel Menggunakan Formula.
Cara ketiga untuk memisahkan konten sel adalah menggunakan rumus atau formula. Tentu saja fungsi yang digunakan dalam formula adalah fungsi-fungsi yang terkait untuk mengolah atau manipulasi text seperti LEFT, RIGHT, MID, LEN, SEARCH dan FIND. Pembahasan mengenai fungsi - fungsi tersebut dapat dilihat pada artikel Fungsi Pengolah Text
Formula untuk memisahkan konten sel jika jumlah karakter diketahui.
Dengan menggunakan contoh data dari pembahasan sebelumnya yaitu berupa NIK dan Nama karyawan, maka rumus yang dapat digunakan untuk memisahkan atau mengambil fragmen data NIK dan nama karyawan adalah sebagai berikut:
Data original (gabungan) terletak pada kolom A
Maka rumus untuk mendapatkan data NIK pada baris pertama adalah:
=LEFT(A1,6)
Sedangkan rumus untuk mendapatkan data nama adalah:
=MID(A1,8,100)
Atu seperti dapat dilihat pada gambar dibawah ini.
Contoh di atas merupakan yang paling sederhana dari cara menggunakan formula untuk memisahkan konten sel. Hal ini karena pola data originalnya sederhana dan relatif teratur, dimulai dengan data NIK dengan jumlah karakter yang sama sehingga dapat ditentukan jumlahnya secara explicit yaitu 6 karakter sebagai parameter num_chars dalam fungsi LEFT.
Demikian pula fungsi MID dapat menggunakan angka 8 sebagai parameter start_num. Hal ini karena posisi huruf pertama dari Nama karyawan semuanya sama yaitu karakter ke-8 dalam text konten sel original. Sedangkan bilangan 100 sebagai parameter num_chars dalam fungsi MID diasumsikan bahwa nama karyawan tidak ada yang lebih dari 100 karakter.
Formula untuk memisahkan konten sel jika jumlah karakter tidak diketahui.
Misalnya kita memiliki data awal berupa nama Perusahaan dan Kota. Data terletak dalam satu kolom, dipisahkan oleh koma, seperti gambah di bawah ini.
Selanjutnya kita ingin memisahkan data nama perusahaan dan lokasi kotanya pada dua kolom yang berbeda.
Karena jumlah karakter penyusun nama perusahaan berbeda-beda, maka kita tidak bisa menentukan angka konstan sebagai parameter num_chars dalam fungsi LEFT.
Untungnya jumlah karakter penyusun nama perusahaan dapat dicari dengan memanfaatkan keberadaan tanda koma yang posisinya tepat satu karakter setelah nama perusahaan. Untuk tugas ini, kita bisa memanfaat kan fungsi FIND atau fungsi SEARCH.
Misalnya:
Rumus =FIND(",",A1)-1 atau =SEARCH(",",A1)-1 akan menghasilkan bilangan 8 yang merupakan jumlah karakter nama perusahaan yang terletak pada sel A1 yaitu “CV Setia”
Rumus =FIND(",",B1)-1 atau =SEARCH(",",B1)-1 akan menghasilkan bilangan 10 yang merupakan jumlah karakter nama perusahaan yang terletak pada sel B1 yaitu “CV Mandiri”
Perbedaan fungsi FIND dan SEARCH adalah bagaimana kedua fungsi tersebut mendeteksi huruf kapital dan huruf kecil. FIND menganggap berbeda antara huruf kapital dan huruf kecil. Sebaliknya SEARCH menganggap huruf kapital dan kecil sama saja. Dikarenakan tidak ada jenis kapital dan kecil untuk tanda koma, maka dalam contoh kasus ini penggunaan fungsi FIND dan SEARCH sama saja dan bisa saling menggantikan.
Selanjutnya rumus FIND atau SEARCH ini bisa dimasukan dalam fungsi LEFT untuk menempati argumen num_chars. Sehingga rumus untuk memisahkan nama perusahaan dapat dituliskan sebagai berikut:
=LEFT(A1,FIND(",",A1)-1)
Pertanyaan berikutnya: rumus macam mana yang bisa dipake untuk memisahkan nama Kota? Dikarenakan nama Kota terletak di sebelah kanan, dan tidak ada data lain di sebelah kanan nama kota, maka untuk tugas ini kita bisa menggunakan fungsi RIGHT
Fungsi RIGHT berguna untuk mengambil beberapa karakter dari sebelah kanan. Jumlah karakter penyusun nama Kota dapat dihitung dari jumlah karakter peyusun sel original dikurangi nomor urut posisi tanda koma, dan dikurangi 1 lagi yaitu tanda spasi.
Rumus untuk mendapatkan jumlah karakter penyusun nama kota dapat dituliskan sebagai berikut
LEN(A1)-FIND(",",A1)-1
Rumus di atas akan menghasilkan bilangan 7 yang merupakan jumlah karakter penyusun nama kota Bandung yang terletak pada Sel A1. Adapun cara kerja rumus tersebut dapat dijelaskan sebagai berikut.
LEN(A1) digunakan untuk menghitung jumlah karakter pada sel A1
FIND(",",A1) digunakan untuk mencari posisi tanda koma pertama pada sel A1
Angka 1 digunakan sebagai pengurang karena adanya tanda spasi yang bukan bagian dari nama kota.
Sehingga jika fragmen-fragmen rumus tersebut diganti menjadi value yang merupakan hasil kalkulasinya maka dapat diuraikan sebagai berikut:
= LEN(A1)-FIND(",",A1)-1
= 17 – 9 – 1
= 7
Selanjutnya, rumus LEN(A1)-FIND(",",A1)-1 dapat ditempatkan sebagai argumen num_chars dalam fungsi RIGHT untuk mendapatkan nama kota. Sehingga rumusnya dapat dituliskan seperti dibawah ini:
=RIGHT(A1,LEN(A1)-FIND(",",A1)-1)
Dalam menggunakan rumus untuk memisahkan konten sel, kita bisa mencoba dengan berbagai variasi gabungan fungsi. Misalnya bisa juga menggunakan gabungan fungsi MID, LEN dan FIND untuk mendapatkan nama kota. Disitulah logika excel kita bermain.
Sampai disini pembahasan cara memisahkan konten sel pada excel. Anda bisa memilih mana yang sesuai dan mana yang paling efektif apakah menggunakan fitur Text To Columns, Flash Fill, ataupun dengan Formula / rumus. Tentu saja anda yang lebih tahu mana yang paling efektif sesuai kebutuhan dan kasus data yang dihadapi.
Demikian semoga bermanfaat.
Catatan Pelajaran Excel Yang Terkait:
