the cheapest car insurance cost of car insurance best car insurance quotes insurance auto insurance quotes car get car insurance quotes online car insurance agent instant car insurance quote car insurance websites direct car insurance insure a car motor car insurance quote cheap car insurance online quotes for car insurance car insurers commercial car insurance motor insurance quote car insurance quotes online get a quote online international health insurance compare car insurance quotes insurance for car compare car insurance car insurance buy life insurance online compare auto insurance international medical insurance general car insurance cheap motor insurance low car insurance car insurance comparison company car insurance budget car insurance private health insurance online quote car insurance cheap motor car insurance car insurance for new drivers cheap car insurance quotes online online car insurance quotes get car insurance quotes car insurance quotes comparison motor car insurance car insurance quotes car insurance cheap comprehensive car insurance best auto insurance short term health insurance motor insurance cheap online car insurance online motor insurance quotes car insurance california buycarinsurance online vehicle insurance free car insurance quotes landlord insurance health insurance for children motorbike insurance monthly car insurance car insurance usa car insurance reviews car insurance renewal quotes vehicle insurance health insurance rates term life insurance quotes vehicle insurance quotes car insurance compare motor vehicle insurance best car insurance motor insurance online quote car insurance online quote life insurance online supplemental health insurance best car insurance rates car insurance discounts motor vehicle insurance quotes auto insurance cheap travel insurance bike insurance travel medical insurance buy car insurance online online motor insurance travel insurance quote get a quote for car insurance health insurance agent car insurance companies cheap car insurance companies travel insurance annual travel insurance commercial vehicle insurance permanent life insurance medical insurance quotes online car insurance cheap car insurance quotes car insurance online quote insurance online level term life insurance health insurance comparison how much is car insurance travel health insurance pet health insurance quotes on cars health ins travel insurance uk quote for car insurance get insurance quote online disability insurance quote temporary health insurance temporary insurance rental car insurance cheap health insurance auto insurance compare third party car insurance buy health insurance auto insurance quotes cheap insurance companies best term life insurance personal health insurance online quote insurance car insurance premium health insurance policies quotes for cars whole life insurance credit insurance cheap van insurance cheap term life insurance cheap motorcycle insurance compare life insurance car insurance cover health insurance motorcycle insurance short term car insurance health insurance coverage insurance qoute cheap life insurance house insurance quote car insurance uk health insurance companies cigna health insurance cheap insurance cheap car insurance uk compare health insurance car insurance for young drivers short term insurance online health insurance caravan insurance life insurance comparison direct insurance health insurance plans motor insurance online car insurance estimator accident insurance home insurance quotes nj car insurance new car insurance private medical insurance home insurance car insurance brokers car insurance rates commercial insurance health insurance quotes motor insurance companies which car insurance car quotes online online auto insurance flood insurance cheap auto insurance quotes car insurance agencies business health insurance term life insurance truck insurance professional indemnity insurance life insurance car insurance policy long term care insurance drive insurance buy insurance car ins motorcycle insurance quote critical illness insurance term insurance student health insurance home insurance comparison insurance rates insurance cover holiday insurance insurance sites online vehicle insurance quotes company website home insurance rates compare insurance buy insurance online affordable medical insurance car insurance coverage product liability insurance home contents insurance buy life insurance renters insurance group health insurance family health insurance motor insurance quote online mortgage insurance best home insurance personal insurance medical insurance property insurance life insurance agent group insurance aetna health insurance cheap medical insurance liability insurance rental insurance life insurance policy group life insurance general liability insurance insurance quotes online all car insurance companies best health insurance insurance plans life insurance for seniors van insurance website quote universal life insurance building insurance co insurance insurance car car insurance prices disability insurance insurance vehicle cheap vehicle insurance senior life insurance medical insurance plans insurance health house insurance life insurance quotes car quotes cheap home insurance business insurance com bank credit card lowest credit card eligibility no fee credit cards best credit card compare how can i apply for credit card business credit cards cheapest credit card company credit card corporate credit card apply for a credit card online 0 apr credit cards how can i get credit card get a credit card open credit card usa credit card instant credit card approval store credit cards credit card shopping airline miles credit card frequent flyer credit cards hotel credit cards best credit card deals compare credit miles credit card credit card consolidation college student credit cards how do i apply for a credit card gold credit card how to open credit card apply for credit card eligibility for credit card credit card verification credit card offers online credit card application form which credit card to apply how can i get a credit card credit card online apply credit cards uk 0 credit cards looking for credit card offers credit card information bank credit card application travel credit cards what is a credit card credit card sign up master credit card cash back cards platinum credit card compare visa cards credit bank credit card no interest credit cards cheap credit cards 0 credit card offers online credit new credit cards application of credit card how to apply for credit card apply card credit online credit card deals premier credit card mastercard credit card how to get credit card apply credit card credit card application credit companies creditcard cash back credit cards airline credit cards cash credit card credit card rates student credit cards apply credit card online how to apply credit card interest free credit card secured credit card best travel credit card capital one credit card online credit card application credit cards with no annual fee new credit card offers apply online credit card credit card apply online best rewards credit card city credit card how to get a credit card credit card counseling rebate credit card mastercard debit card credit card debt best credit cards to apply for best credit cards apply for credit card online credit card apply how to apply for a credit card apply for new credit card credit card rebate best credit card comparison apply for visa card online cim credit card good credit cards credit card cash back best cash back credit cards mastercard credit card application credit card checker credit card mastercard instant credit card card compare credit card rewards programs compare the best credit cards apply visa card cash rebate credit card bankruptcy credit cards card offers instant approval credit cards credit card online application credit cards for students how to sign up for credit card uk credit cards credit card account about credit card visa card apply online credit card compare site secured mastercard visa card application credit card rewards credit rating how can we get credit card visa credit card rewards card online credit card processing capital credit card credit card visa consolidate credit cards credit card processing credit card reviews apply new credit card business credit card application credit cards with cash back credit card reader credit card instant approval online apply credit card bad credit credit cards credit card selection online visa card pay with credit card credit card charges visa or mastercard credit card machine apply a credit card small business credit cards prepaid debit cards visa debit card mbna credit card credit card terminal card credit application credit card generator credit card balance credit card numbers credit card transfer credit card interest rates credit card interest gold card credit card online best rate credit cards credit card low interest visa card online online credit card how to apply a credit card 0 interest credit cards debit card the cheapest car insurance cost of car insurance best car insurance quotes insurance auto insurance quotes car get car insurance quotes online car insurance agent instant car insurance quote car insurance websites direct car insurance insure a car motor car insurance quote cheap car insurance online quotes for car insurance car insurers commercial car insurance motor insurance quote car insurance quotes online get a quote online international health insurance compare car insurance quotes insurance for car compare car insurance car insurance buy life insurance online compare auto insurance international medical insurance general car insurance cheap motor insurance low car insurance car insurance comparison company car insurance budget car insurance private health insurance online quote car insurance cheap motor car insurance car insurance for new drivers cheap car insurance quotes online online car insurance quotes get car insurance quotes car insurance quotes comparison motor car insurance car insurance quotes car insurance cheap comprehensive car insurance best auto insurance short term health insurance motor insurance cheap online car insurance online motor insurance quotes car insurance california buycarinsurance online vehicle insurance free car insurance quotes landlord insurance health insurance for children motorbike insurance monthly car insurance car insurance usa car insurance reviews car insurance renewal quotes vehicle insurance health insurance rates term life insurance quotes vehicle insurance quotes car insurance compare motor vehicle insurance best car insurance motor insurance online quote car insurance online quote life insurance online supplemental health insurance best car insurance rates car insurance discounts motor vehicle insurance quotes auto insurance cheap travel insurance bike insurance travel medical insurance buy car insurance online online motor insurance travel insurance quote get a quote for car insurance health insurance agent car insurance companies cheap car insurance companies travel insurance annual travel insurance commercial vehicle insurance permanent life insurance medical insurance quotes online car insurance cheap car insurance quotes car insurance online quote insurance online level term life insurance health insurance comparison how much is car insurance travel health insurance pet health insurance quotes on cars health ins travel insurance uk quote for car insurance get insurance quote online disability insurance quote temporary health insurance temporary insurance rental car insurance cheap health insurance auto insurance compare third party car insurance buy health insurance auto insurance quotes cheap insurance companies best term life insurance personal health insurance online quote insurance car insurance premium health insurance policies quotes for cars whole life insurance credit insurance cheap van insurance cheap term life insurance cheap motorcycle insurance compare life insurance car insurance cover health insurance motorcycle insurance short term car insurance health insurance coverage insurance qoute cheap life insurance house insurance quote car insurance uk health insurance companies cigna health insurance cheap insurance cheap car insurance uk compare health insurance car insurance for young drivers short term insurance online health insurance caravan insurance life insurance comparison direct insurance health insurance plans motor insurance online car insurance estimator accident insurance home insurance quotes nj car insurance new car insurance private medical insurance home insurance car insurance brokers car insurance rates commercial insurance health insurance quotes motor insurance companies which car insurance car quotes online online auto insurance flood insurance cheap auto insurance quotes car insurance agencies business health insurance term life insurance truck insurance professional indemnity insurance life insurance car insurance policy long term care insurance drive insurance buy insurance car ins motorcycle insurance quote critical illness insurance term insurance student health insurance home insurance comparison insurance rates insurance cover holiday insurance insurance sites online vehicle insurance quotes company website home insurance rates compare insurance buy insurance online affordable medical insurance car insurance coverage product liability insurance home contents insurance buy life insurance renters insurance group health insurance family health insurance motor insurance quote online mortgage insurance best home insurance personal insurance medical insurance property insurance life insurance agent group insurance aetna health insurance cheap medical insurance liability insurance rental insurance life insurance policy group life insurance general liability insurance insurance quotes online all car insurance companies best health insurance insurance plans life insurance for seniors van insurance website quote universal life insurance building insurance co insurance insurance car car insurance prices disability insurance insurance vehicle cheap vehicle insurance senior life insurance medical insurance plans insurance health house insurance life insurance quotes car quotes cheap home insurance business insurance com bank credit card lowest credit card eligibility no fee credit cards best credit card compare how can i apply for credit card business credit cards cheapest credit card company credit card corporate credit card apply for a credit card online 0 apr credit cards how can i get credit card get a credit card open credit card usa credit card instant credit card approval store credit cards credit card shopping airline miles credit card frequent flyer credit cards hotel credit cards best credit card deals compare credit miles credit card credit card consolidation college student credit cards how do i apply for a credit card gold credit card how to open credit card apply for credit card eligibility for credit card credit card verification credit card offers online credit card application form which credit card to apply how can i get a credit card credit card online apply credit cards uk 0 credit cards looking for credit card offers credit card information bank credit card application travel credit cards what is a credit card credit card sign up master credit card cash back cards platinum credit card compare visa cards credit bank credit card no interest credit cards cheap credit cards 0 credit card offers online credit new credit cards application of credit card how to apply for credit card apply card credit online credit card deals premier credit card mastercard credit card how to get credit card apply credit card credit card application credit companies creditcard cash back credit cards airline credit cards cash credit card credit card rates student credit cards apply credit card online how to apply credit card interest free credit card secured credit card best travel credit card capital one credit card online credit card application credit cards with no annual fee new credit card offers apply online credit card credit card apply online best rewards credit card city credit card how to get a credit card credit card counseling rebate credit card mastercard debit card credit card debt best credit cards to apply for best credit cards apply for credit card online credit card apply how to apply for a credit card apply for new credit card credit card rebate best credit card comparison apply for visa card online cim credit card good credit cards credit card cash back best cash back credit cards mastercard credit card application credit card checker credit card mastercard instant credit card card compare credit card rewards programs compare the best credit cards apply visa card cash rebate credit card bankruptcy credit cards card offers instant approval credit cards credit card online application credit cards for students how to sign up for credit card uk credit cards credit card account about credit card visa card apply online credit card compare site secured mastercard visa card application credit card rewards credit rating how can we get credit card visa credit card rewards card online credit card processing capital credit card credit card visa consolidate credit cards credit card processing credit card reviews apply new credit card business credit card application credit cards with cash back credit card reader credit card instant approval online apply credit card bad credit credit cards credit card selection online visa card pay with credit card credit card charges visa or mastercard credit card machine apply a credit card small business credit cards prepaid debit cards visa debit card mbna credit card credit card terminal card credit application credit card generator credit card balance credit card numbers credit card transfer credit card interest rates credit card interest gold card credit card online best rate credit cards credit card low interest visa card online online credit card how to apply a credit card 0 interest credit cards debit card the cheapest car insurance cost of car insurance best car insurance quotes insurance auto insurance quotes car get car insurance quotes online car insurance agent instant car insurance quote car insurance websites direct car insurance insure a car motor car insurance quote cheap car insurance online quotes for car insurance car insurers commercial car insurance motor insurance quote car insurance quotes online get a quote online international health insurance compare car insurance quotes insurance for car compare car insurance car insurance buy life insurance online compare auto insurance international medical insurance general car insurance cheap motor insurance low car insurance car insurance comparison company car insurance budget car insurance private health insurance online quote car insurance cheap motor car insurance car insurance for new drivers cheap car insurance quotes online online car insurance quotes get car insurance quotes car insurance quotes comparison motor car insurance car insurance quotes car insurance cheap comprehensive car insurance best auto insurance short term health insurance motor insurance cheap online car insurance online motor insurance quotes car insurance california buycarinsurance online vehicle insurance free car insurance quotes landlord insurance health insurance for children motorbike insurance monthly car insurance car insurance usa car insurance reviews car insurance renewal quotes vehicle insurance health insurance rates term life insurance quotes vehicle insurance quotes car insurance compare motor vehicle insurance best car insurance motor insurance online quote car insurance online quote life insurance online supplemental health insurance best car insurance rates car insurance discounts motor vehicle insurance quotes auto insurance cheap travel insurance bike insurance travel medical insurance buy car insurance online online motor insurance travel insurance quote get a quote for car insurance health insurance agent car insurance companies cheap car insurance companies travel insurance annual travel insurance commercial vehicle insurance permanent life insurance medical insurance quotes online car insurance cheap car insurance quotes car insurance online quote insurance online level term life insurance health insurance comparison how much is car insurance travel health insurance pet health insurance quotes on cars health ins travel insurance uk quote for car insurance get insurance quote online disability insurance quote temporary health insurance temporary insurance rental car insurance cheap health insurance auto insurance compare third party car insurance buy health insurance auto insurance quotes cheap insurance companies best term life insurance personal health insurance online quote insurance car insurance premium health insurance policies quotes for cars whole life insurance credit insurance cheap van insurance cheap term life insurance cheap motorcycle insurance compare life insurance car insurance cover health insurance motorcycle insurance short term car insurance health insurance coverage insurance qoute cheap life insurance house insurance quote car insurance uk health insurance companies cigna health insurance cheap insurance cheap car insurance uk compare health insurance car insurance for young drivers short term insurance online health insurance caravan insurance life insurance comparison direct insurance health insurance plans motor insurance online car insurance estimator accident insurance home insurance quotes nj car insurance new car insurance private medical insurance home insurance car insurance brokers car insurance rates commercial insurance health insurance quotes motor insurance companies which car insurance car quotes online online auto insurance flood insurance cheap auto insurance quotes car insurance agencies business health insurance term life insurance truck insurance professional indemnity insurance life insurance car insurance policy long term care insurance drive insurance buy insurance car ins motorcycle insurance quote critical illness insurance term insurance student health insurance home insurance comparison insurance rates insurance cover holiday insurance insurance sites online vehicle insurance quotes company website home insurance rates compare insurance buy insurance online affordable medical insurance car insurance coverage product liability insurance home contents insurance buy life insurance renters insurance group health insurance family health insurance motor insurance quote online mortgage insurance best home insurance personal insurance medical insurance property insurance life insurance agent group insurance aetna health insurance cheap medical insurance liability insurance rental insurance life insurance policy group life insurance general liability insurance insurance quotes online all car insurance companies best health insurance insurance plans life insurance for seniors van insurance website quote universal life insurance building insurance co insurance insurance car car insurance prices disability insurance insurance vehicle cheap vehicle insurance senior life insurance medical insurance plans insurance health house insurance life insurance quotes car quotes cheap home insurance business insurance com bank credit card lowest credit card eligibility no fee credit cards best credit card compare how can i apply for credit card business credit cards cheapest credit card company credit card corporate credit card apply for a credit card online 0 apr credit cards how can i get credit card get a credit card open credit card usa credit card instant credit card approval store credit cards credit card shopping airline miles credit card frequent flyer credit cards hotel credit cards best credit card deals compare credit miles credit card credit card consolidation college student credit cards how do i apply for a credit card gold credit card how to open credit card apply for credit card eligibility for credit card credit card verification credit card offers online credit card application form which credit card to apply how can i get a credit card credit card online apply credit cards uk 0 credit cards looking for credit card offers credit card information bank credit card application travel credit cards what is a credit card credit card sign up master credit card cash back cards platinum credit card compare visa cards credit bank credit card no interest credit cards cheap credit cards 0 credit card offers online credit new credit cards application of credit card how to apply for credit card apply card credit online credit card deals premier credit card mastercard credit card how to get credit card apply credit card credit card application credit companies creditcard cash back credit cards airline credit cards cash credit card credit card rates student credit cards apply credit card online how to apply credit card interest free credit card secured credit card best travel credit card capital one credit card online credit card application credit cards with no annual fee new credit card offers apply online credit card credit card apply online best rewards credit card city credit card how to get a credit card credit card counseling rebate credit card mastercard debit card credit card debt best credit cards to apply for best credit cards apply for credit card online credit card apply how to apply for a credit card apply for new credit card credit card rebate best credit card comparison apply for visa card online cim credit card good credit cards credit card cash back best cash back credit cards mastercard credit card application credit card checker credit card mastercard instant credit card card compare credit card rewards programs compare the best credit cards apply visa card cash rebate credit card bankruptcy credit cards card offers instant approval credit cards credit card online application credit cards for students how to sign up for credit card uk credit cards credit card account about credit card visa card apply online credit card compare site secured mastercard visa card application credit card rewards credit rating how can we get credit card visa credit card rewards card online credit card processing capital credit card credit card visa consolidate credit cards credit card processing credit card reviews apply new credit card business credit card application credit cards with cash back credit card reader credit card instant approval online apply credit card bad credit credit cards credit card selection online visa card pay with credit card credit card charges visa or mastercard credit card machine apply a credit card small business credit cards prepaid debit cards visa debit card mbna credit card credit card terminal card credit application credit card generator credit card balance credit card numbers credit card transfer credit card interest rates credit card interest gold card credit card online best rate credit cards credit card low interest visa card online online credit card how to apply a credit card 0 interest credit cards debit card

Cara Efektif Memisahkan Konten Sel

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

Contoh Data Import Excel

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 1 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.

Langkah 2 Text To Columns


Setelah itu maka kita akan dibawa pada kotak dialog berikutnya yang berguna untuk pengaturan format dan destinasi sel untuk menyimpan hasil pemisahan konten.

Langkah 3 Excel To Columns


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.

Hasil Text To Columns


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.

Data Text To Columns Fixed Width

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.

Langkah 1 Text To Columns Fixed Width


Set lebar masing – masihg kolom dengan menggunakan Data Preview, sebagaimana diperlihakan dalam screenshot di bawah. 

Langkah 2: Text To Columns Fixed Width

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:

Tombol Fill Flash Excel


Maka baris lainnya akan terisi data mengikuti pola data yang sudah diketik duluan. 


contoh fill flash excel

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. 


Langkah-Langkah Fill Flash

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:

Cara Fill Flash


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:


Cara Drag Fill Flash Excel

Drag Fill Flash



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.


Cara Fill Flash Otomatis

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 FINDPembahasan 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.

Rumus untuk memisahkan konten sel

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.

contoh pemisahan data excel

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)


contoh rumus pemisahan data excel

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)

contoh rumus pemisahan konten sel

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.


BERITA LENGKAP DI HALAMAN BERIKUTNYA

Halaman Berikutnya

Subscribe to receive free email updates:

0 Response to "Cara Efektif Memisahkan Konten Sel "

Posting Komentar

close
==[ Klik disini 1X ] [ Close ]==