Saat data diimpor ke Excel, itu bisa dalam banyak format tergantung pada aplikasi sumber yang menyediakannya
Misalnya, itu bisa berisi nama dan alamat pelanggan atau karyawan, tetapi ini semua berakhir sebagai string teks berkelanjutan dalam satu kolom lembar kerja, alih-alih dipisahkan menjadi kolom individual e. g. nama, jalan, kota
Anda dapat membagi data dengan menggunakan karakter pembatas umum. Karakter pembatas biasanya berupa koma, tab, spasi, atau titik koma. Karakter ini memisahkan setiap potongan data dalam string teks
Keuntungan besar menggunakan karakter pembatas adalah tidak bergantung pada lebar tetap dalam teks. Pembatas menunjukkan dengan tepat di mana harus membagi teks
Anda mungkin perlu membagi data karena Anda mungkin ingin mengurutkan data menggunakan bagian tertentu dari alamat atau agar dapat memfilter pada komponen tertentu. Jika data digunakan dalam tabel pivot, Anda mungkin perlu memiliki nama dan alamat sebagai bidang yang berbeda di dalamnya
Artikel ini menunjukkan kepada Anda delapan cara untuk membagi teks menjadi bagian-bagian komponen yang diperlukan dengan menggunakan karakter pembatas untuk menunjukkan titik pisah
Contoh data
Data sampel di atas akan digunakan dalam semua contoh berikut. Unduh file contoh untuk mendapatkan data sampel plus berbagai solusi untuk mengekstraksi data berdasarkan pembatas
Fungsi Excel untuk Membagi Teks
Ada beberapa fungsi Excel yang dapat digunakan untuk membagi dan memanipulasi teks di dalam sel
Fungsi KIRI
Fungsi LEFT mengembalikan jumlah karakter dari kiri teks
Sintaksis
= LEFT [ Text, [Number] ]
- Teks – Ini adalah string teks yang ingin Anda ekstrak. Itu juga bisa menjadi referensi sel yang valid dalam buku kerja
- Angka [Opsional] – Ini adalah jumlah karakter yang ingin Anda ekstrak dari string teks. Nilainya harus lebih besar dari atau sama dengan nol. Jika nilainya lebih besar dari panjang string teks, maka semua karakter akan dikembalikan. Jika nilainya dihilangkan, maka nilainya dianggap satu
Fungsi KANAN
Fungsi RIGHT mengembalikan jumlah karakter dari kanan teks
Sintaksis
= RIGHT [ Text, [Number] ]
_Parameter bekerja dengan cara yang sama seperti fungsi LEFT yang dijelaskan di atas
Fungsi TEMUKAN
Fungsi FIND mengembalikan posisi teks yang ditentukan dalam string teks. Ini dapat digunakan untuk menemukan karakter pembatas. Perhatikan bahwa pencarian peka huruf besar-kecil
Sintaksis
= FIND [SubText, Text, [Start]]
- SubTeks – Ini adalah string teks yang ingin Anda cari
- Teks – Ini adalah string teks yang akan dicari
- Mulai [Opsional] – Posisi awal untuk pencarian
Fungsi LEN
Fungsi LEN akan memberikan panjang dengan jumlah karakter string teks
Sintaksis
= LEN [ Text ]
- Teks – Ini adalah string teks yang ingin Anda tentukan jumlah karakternya
Mengekstrak Data dengan Fungsi KIRI, KANAN, TEMUKAN dan LEN
Dengan menggunakan baris pertama [B3] dari data sampel, fungsi ini dapat digabungkan untuk membagi string teks menjadi beberapa bagian menggunakan karakter pembatas
= FIND [ ",", B3 ]
_Anda menggunakan fungsi FIND untuk mendapatkan posisi karakter pembatas pertama. Ini akan mengembalikan nilai 18
= LEFT [ B3, FIND[ ",", B3 ] - 1 ]
Anda kemudian dapat menggunakan fungsi LEFT untuk mengekstrak komponen pertama string teks
Perhatikan bahwa FIND mendapatkan posisi pembatas pertama, tetapi Anda harus menguranginya dengan 1 agar tidak menyertakan karakter pembatas
Ini akan mengembalikan Tabbie O'Hallagan
= RIGHT [ B3, LEN [ B3 ] - FIND [ ",", B3 ] ]
_Lebih rumit untuk mendapatkan komponen berikutnya dari string teks. Anda harus menghapus komponen pertama dari teks dengan menggunakan rumus di atas
Rumus ini mengambil panjang teks asli, menemukan posisi pembatas pertama, yang kemudian menghitung berapa banyak karakter yang tersisa di string teks setelah pembatas itu
Fungsi RIGHT kemudian memotong semua karakter hingga dan termasuk pembatas pertama sehingga string teks menjadi lebih pendek dan lebih pendek karena setiap karakter pembatas ditemukan
Ini akan mengembalikan 056 Dennis Park, Greda, Kroasia, 44273
Anda sekarang dapat menggunakan FIND untuk menemukan pembatas berikutnya dan fungsi LEFT untuk mengekstrak komponen berikutnya, menggunakan metodologi yang sama seperti di atas
Ulangi untuk semua pembatas, dan ini akan membagi string teks menjadi bagian-bagian komponen
FILTERXML Berfungsi sebagai Array Dinamis
Jika Anda menggunakan Excel untuk Microsoft 365, maka Anda bisa menggunakan fungsi FILTERXML untuk memisahkan teks dengan output sebagai larik dinamis
Anda dapat memisahkan string teks dengan mengubahnya menjadi string XML dengan mengubah karakter pembatas menjadi tag XML. Dengan cara ini Anda dapat menggunakan fungsi FILTERXML untuk mengekstrak data
Tag XML ditentukan pengguna, tetapi dalam contoh ini, s akan mewakili sub-simpul dan t akan mewakili simpul utama
= "" & SUBSTITUTE [ B2, ",", "" ] & ""
Gunakan rumus di atas untuk memasukkan tag XML ke dalam string teks Anda
NameStreetCityCountryPost Code
_Ini akan mengembalikan rumus di atas dalam contoh
Perhatikan bahwa setiap simpul yang ditentukan diikuti oleh simpul penutup dengan garis miring terbalik. Tag XML ini menentukan awal dan akhir setiap bagian teks, dan secara efektif bertindak dengan cara yang sama seperti pembatas
=TRANSPOSE[
FILTERXML[
"" &
SUBSTITUTE[
B3,
",",
""
] & "",
"//s"
]
]
_Rumus di atas akan menyisipkan tag XML ke dalam string asli dan kemudian menggunakannya untuk memisahkan item ke dalam array
Seperti yang terlihat di atas, array akan menumpahkan setiap item ke dalam sel terpisah. Menggunakan fungsi TRANSPOSE menyebabkan larik tumpah secara horizontal, bukan vertikal
Fungsi FILTERXML untuk Membagi Teks
Jika versi Excel Anda tidak memiliki array dinamis, Anda masih dapat menggunakan fungsi FILTERXML untuk mengekstrak item individual
= RIGHT [ Text, [Number] ]
_0Anda sekarang dapat memecah string menjadi beberapa bagian menggunakan rumus FILTERXML di atas
Ini akan mengembalikan bagian pertama Tabbie O'Hallagan
= RIGHT [ Text, [Number] ]
_1Untuk mengembalikan bagian berikutnya, gunakan rumus di atas
Ini akan mengembalikan bagian kedua dari string teks 056 Dennis Park
Anda dapat menggunakan pola yang sama ini untuk mengembalikan bagian mana pun dari teks sampel, cukup ubah [2] yang ditemukan dalam rumus sesuai dengan itu
Isian Cepat untuk Membagi Teks
Isi Cepat memungkinkan Anda memasukkan contoh bagaimana Anda ingin membagi data Anda
Anda dapat melihat panduan ini tentang penggunaan flash fill untuk membersihkan data Anda untuk lebih jelasnya
Anda kemudian memilih sel pertama tempat Anda ingin membagi data dan mengklik Isi Cepat. Excel akan mengisi baris yang tersisa dari contoh Anda
Menggunakan data sampel, masukkan Nama ke dalam sel C2, lalu Tabbie O'Hallagan ke dalam sel C3
Isi cepat harus secara otomatis mengisi nama data yang tersisa dari data sampel. Jika tidak, Anda dapat memilih sel C4, dan klik ikon Flash Fill di grup Data Tools pada tab Data di pita Excel
Demikian pula, Anda dapat menambahkan Jalan ke sel D2, Kota ke sel E2, Negara ke sel F2, dan Kode Pos ke sel G2
Pilih sel berikutnya [D2 ke G2] satu per satu, dan klik ikon Flash Fill. Komponen teks lainnya akan diisi ke dalam kolom ini
Teks ke Kolom Perintah untuk Membagi Teks
Fungsionalitas Excel ini dapat digunakan untuk membagi teks dalam sel menjadi beberapa bagian berdasarkan karakter pembatas
- Pilih seluruh rentang data sampel [B2. B12]
- Klik pada tab Data di pita Excel
- Klik pada ikon Text to Columns di grup Data Tools pada pita Excel dan wizard akan muncul untuk membantu Anda mengatur bagaimana teks akan dipisahkan
- Pilih Dibatasi pada tombol opsi
- Tekan tombol Berikutnya
- Pilih Koma sebagai pembatas, dan hapus centang pembatas lainnya
- Tekan tombol Berikutnya
- Jendela Pratinjau Data akan menampilkan bagaimana data Anda akan dipisahkan. Pilih lokasi untuk menempatkan output
- Klik tombol Selesai
Data Anda sekarang akan ditampilkan dalam kolom di lembar kerja Anda
Ubah Data menjadi File CSV
Ini hanya akan berfungsi dengan koma sebagai pembatas, karena file CSV [nilai yang dipisahkan koma] bergantung pada koma untuk memisahkan nilai
Buka Notepad dan salin dan tempel data sampel ke dalamnya. Anda dapat membuka Notepad dengan mengetikkan Notepad ke dalam kotak pencarian di sebelah kiri bilah tugas Windows atau mencarinya di daftar aplikasi
Setelah Anda menyalin data ke Notepad, simpan dengan menggunakan File ➜ Save As dari menu. Masukkan nama file dengan a. akhiran csv e. g. Pisahkan Data. csv
Anda kemudian dapat membuka file ini di Excel. Pilih file csv di tarik-turun jenis file browser dan klik OK. Data Anda akan secara otomatis muncul dengan setiap komponen di kolom terpisah
VBA untuk Membagi Teks
VBA adalah bahasa pemrograman yang berada di belakang Excel dan memungkinkan Anda menulis kode Anda sendiri untuk memanipulasi data, atau bahkan membuat fungsi Anda sendiri
Untuk mengakses Editor Visual Basic [VBE], Anda menggunakan Alt + F11
= RIGHT [ Text, [Number] ]
_2Klik Sisipkan di bilah menu, dan klik Modul. Panel baru akan muncul untuk modul. Rekatkan kode di atas
Kode ini membuat array dimensi tunggal yang disebut MyArray. Ini kemudian beralih melalui data sampel [baris 2 hingga 12] dan menggunakan fungsi VBA Split untuk mengisi MyArray
Fungsi split menggunakan pembatas koma, sehingga setiap bagian teks menjadi elemen array
Variabel penghitung diatur ke 3 yang mewakili kolom C, yang akan menjadi kolom pertama untuk data split yang akan ditampilkan
Kode kemudian beralih melalui setiap elemen dalam array dan mengisi setiap sel dengan elemen tersebut. Referensi sel didasarkan pada n untuk baris, dan Hitung untuk kolom
Jumlah variabel bertambah di setiap loop sehingga data mengisi seluruh baris, lalu ke bawah
Power Query untuk Membagi Teks
Power Query di Excel memungkinkan kolom untuk dimanipulasi menjadi beberapa bagian menggunakan karakter pembatas
Pos terkait
Hal pertama yang harus dilakukan adalah menentukan sumber data Anda, yaitu data sampel yang Anda masukkan ke lembar kerja Excel Anda
Klik tab Data di pita Excel, lalu klik Dapatkan Data di grup Dapatkan & Transformasi Data di pita
Klik From File di drop down pertama, lalu klik From Workbook di drop down kedua
Ini akan menampilkan file browser. Temukan file data sampel Anda [file yang telah Anda buka] dan klik OK
Pop-up navigasi akan ditampilkan menampilkan semua lembar kerja dalam buku kerja Anda. Klik pada lembar kerja yang memiliki data sampel dan ini akan menampilkan pratinjau data
Perluas pohon data di panel sebelah kiri untuk menampilkan pratinjau data yang ada
Klik Transform Data dan ini akan menampilkan Power Query Editor
Pastikan satu kolom dengan data di dalamnya disorot. Klik ikon Split Column di grup Transform pada pita. Klik By Delimiter di drop down yang muncul
Ini akan menampilkan jendela pop-up yang memungkinkan Anda untuk memilih pembatas Anda. Standarnya adalah koma
Klik OK dan data akan diubah menjadi kolom terpisah
Klik Tutup dan Muat di grup Tutup pita, dan lembar kerja baru akan ditambahkan ke buku kerja Anda dengan tabel data dalam format baru
Kolom Terhitung Power Pivot untuk Membagi Teks
Anda dapat menggunakan Power Pivot untuk memisahkan teks menggunakan kolom terhitung
Klik tab Power Pivot di pita Excel, lalu klik ikon Tambahkan ke Model Data di grup Tabel
Data Anda akan terdeteksi secara otomatis dan pop-up akan menampilkan lokasi. Jika ini bukan lokasi yang benar, maka dapat disetel ulang di sini
Biarkan kotak centang My table has headers tidak dicentang di pop-up, karena kami juga ingin memisahkan header
Klik OK dan layar pratinjau akan ditampilkan
Klik kanan pada tajuk untuk kolom data Anda [Kolom1] dan klik Sisipkan Kolom di menu pop-up. Ini akan menyisipkan kolom terhitung di mana rumus dapat dimasukkan
= RIGHT [ Text, [Number] ]
_3Di bilah rumus, masukkan rumus di atas
Ini bekerja dengan cara yang mirip dengan fungsi yang dijelaskan dalam metode 1 artikel ini
Rumus ini akan menyediakan komponen Nama di dalam string teks
Sisipkan kolom terhitung lainnya menggunakan metodologi yang sama dengan kolom terhitung pertama
= RIGHT [ Text, [Number] ]
_4Masukkan rumus di atas ke dalam bilah rumus
Ini adalah rumus yang rumit, dan Anda mungkin ingin membaginya menjadi beberapa bagian menggunakan beberapa kolom terhitung
Ini akan menyediakan komponen Street dalam string teks
Anda dapat melanjutkan memodifikasi rumus untuk membuat kolom terhitung untuk semua komponen string teks lainnya
Masalah dengan tabel pivot adalah membutuhkan nilai numerik dan juga nilai teks. Karena data sampel hanya berupa teks, nilai numerik perlu ditambahkan
Klik sel pertama di kolom Tambahkan Kolom dan masukkan rumus =1 di bilah rumus
Ini akan menambahkan nilai 1 ke bawah kolom itu. Klik ikon Tabel Pivot di tab Beranda pada pita
Klik Tabel Pivot di menu pop-up. Tentukan lokasi tabel pivot Anda di jendela pop-up pertama dan klik OK. Jika panel Pivot Table Fields tidak ditampilkan secara otomatis, klik kanan pada kerangka tabel pivot dan pilih Show Field List
Klik pada Kolom Terhitung di Daftar Bidang dan letakkan ini di jendela Baris
tabel pivot kami sekarang akan menampilkan komponen individual dari string teks
Kesimpulan
Berurusan dengan koma atau data yang dipisahkan pembatas lainnya bisa sangat menyusahkan jika Anda tidak tahu cara mengekstrak setiap item ke dalam selnya sendiri