Satu skenario adalah di mana Anda perlu menggabungkan beberapa string teks menjadi satu string teks. Baliknya adalah membagi satu string teks menjadi beberapa string teks. Jika data yang ada disalin dari suatu tempat atau dibuat oleh seseorang yang benar-benar melewatkan titik kolom Excel, Anda akan menemukan bahwa Anda perlu membagi blok teks untuk mengkategorikannya.
Memisahkan teks sangat bergantung pada pembatas dalam string teks. Pembatas adalah karakter atau simbol yang menandai awal atau akhir string karakter. Contoh pembatas adalah karakter spasi, tanda hubung, titik, koma
Contoh kasus kami untuk panduan ini melibatkan pemisahan detail buku menjadi judul buku, penulis, dan genre. Pembatas yang kami gunakan adalah koma
Tutorial ini akan mengajarkan Anda cara membagi teks di Excel dengan fitur Text to Columns dan Flash Fill, formula, dan VBA. Metode rumus mencakup pemisahan teks dengan karakter tertentu. Itulah menu hari ini
Mari kita berpisah
Daftar isi
Menggunakan Teks ke Kolom
Fitur ini sesuai dengan namanya. Teks ke Kolom membagi kolom teks menjadi beberapa kolom dengan kontrol tertentu. Teks ke Kolom dapat memisahkan teks dengan pembatas dan karena data kami hanya berisi koma sebagai pembatas, menggunakan fitur ini menjadi sangat mudah. Lihat langkah-langkah berikut untuk memisahkan teks menggunakan Text to Columns
- Pilih data yang ingin Anda bagi
- Buka tab Data dan pilih ikon Teks ke Kolom dari Alat Data
- Pilih tombol radio Delimited dan kemudian klik Next
- Di bagian Pembatas, pilih Koma
- Kemudian pilih tombol Berikutnya
- Sekarang Anda harus memilih di mana Anda ingin memisahkan teks. Klik bidang Tujuan, lalu pilih sel tujuan pada lembar kerja di latar belakang tempat Anda ingin memulai teks terpisah
- Pilih tombol Selesai untuk menutup Teks ke Kolom
Menggunakan pembatas koma untuk memisahkan string teks, Text to Columns telah membagi teks dari kasus contoh kita menjadi tiga kolom
Untungnya, data kami tidak berisi buku dengan koma di nama buku. Jika kita memiliki buku Cows, Pigs, Wars and Witches oleh Marvin Harris dalam kumpulan data, teks akan dibagi menjadi 5 kolom, bukan 3 seperti kolom lainnya. Jika pembatas dalam data Anda muncul di string teks lebih dari pembatas, Anda akan lebih beruntung membagi teks dengan metode lain. Sekarang ke pengamatan lain
Menggunakan Fungsi TRIM untuk Memangkas Spasi Ekstra
Mari kita lihat lebih dekat hasil dari Text to Columns. Perhatikan bagaimana dua kolom terakhir membawa satu spasi terdepan?
Dua kolom terakhir membawa spasi terdepan karena Text to Columns hanya mengambil pembatas sebagai tanda dari mana memisahkan teks. Karakter spasi setelah koma dibawa dengan unit teks berikutnya dan yang memiliki data kita dengan spasi terdepan
Perbaikan cepat untuk ini adalah menggunakan fungsi TRIM untuk membersihkan ruang ekstra. Berikut adalah rumus yang kami gunakan untuk menghapus spasi awal dari kolom D dan E
=TRIM(C4)_
Fungsi TRIM menghapus semua spasi dari string teks selain karakter spasi tunggal di antara dua kata. Spasi awal, akhir, atau tambahan apa pun akan dihapus dari nilai sel. Kami hanya menggunakan rumus dengan referensi sel yang berisi ruang terdepan
Dan itu membersihkan ruang terdepan bagi kami. Data – bagus untuk digunakan
Menggunakan Formula Untuk Memisahkan Teks di Excel
Kita dapat menggunakan fungsi Excel untuk membuat formula yang dapat membantu kita dalam membagi string teks menjadi beberapa
Pisahkan String dengan Pembatas
Menggunakan rumus juga dapat membagi satu string teks menjadi beberapa string, tetapi akan memerlukan kombo fungsi. Mari kita uraikan sedikit tentang fungsi yang menjadi bagian dari rumus ini yang akan kita gunakan untuk memisahkan teks
Fungsi SUBSTITUTE menggantikan teks lama dalam string teks dengan teks baru
Fungsi REPT mengulangi teks beberapa kali
Fungsi LEN mengembalikan jumlah karakter dalam string teks
Fungsi MID mengembalikan sejumlah karakter tertentu dari tengah string teks dengan posisi awal yang ditentukan
Fungsi TRIM menghapus semua spasi, selain spasi tunggal di antara kata, dari string teks
Sekarang mari kita lihat bagaimana gabungan fungsi ini dapat digunakan untuk memisahkan teks dengan satu rumus
=TRIM(MID(SUBSTITUTE($B5,",",REPT(" ",LEN($B5))),(C$4-1)*LEN($B5)+1,LEN($B5)))
Dalam contoh kita, sel pertama tempat kita menggunakan rumus ini adalah sel B5. Jumlah karakter dalam B5 yang dihitung oleh fungsi LEN adalah 49. Fungsi REPT mengulangi spasi (dilambangkan dengan “ “ dalam rumus) di B5 untuk jumlah karakter yang disediakan oleh fungsi LEN i. e. 49
Fungsi SUBSTITUTE menggantikan koma "," dalam B5 dengan 49 karakter spasi yang disediakan oleh fungsi REPT. Karena ada dua koma di B5, satu setelah nama buku dan satu setelah penulis, 49 spasi akan dimasukkan setelah nama buku dan 49 spasi setelah penulis, menciptakan jarak yang layak antara teks yang ingin kita pisahkan
Sekarang mari kita lihat perhitungan untuk fungsi MID. Bit pertama adalah (C$4-1). Di baris 4, kami menambahkan nomor seri untuk setiap kolom untuk kategori kami. Baris telah dikunci dalam rumus dengan tanda $ sehingga baris tidak berubah saat rumus disalin. Tapi kami membiarkan kolom kosong sehingga nomor seri berubah untuk masing-masing kolom yang digunakan dalam rumus
Dalam rumusnya, 1 dikurangi dari C4 (1-1=0) dan hasilnya dikalikan dengan jumlah karakter di B5 i. e. LEN($B5) dan kemudian 1 ditambahkan ke ekspresi. Perhitungan posisi awal pada fungsi MID, i. e. (C$4-1)*LEN($B5)+1, menjadi (1-1)*49+1 yang sama dengan 1
Fungsi MID mengembalikan teks dari tengah B5, posisi awal adalah 1 (artinya teks yang akan dikembalikan harus dimulai dari karakter pertama) dan jumlah karakter yang akan dikembalikan adalah LEN($B5) i. e 49 karakter. Karena kami telah menambahkan 49 spasi di tempat masing-masing koma, itu memberi kami banyak area untuk mengembalikan hanya satu potongan teks dengan aman bersama dengan beberapa spasi ekstra. Hasil hingga fungsi MID adalah A Song of Ice and Fire dengan banyak spasi tambahan
Ruang ekstra tidak masalah. Fungsi TRIM membersihkan setiap ruang tambahan yang meninggalkan ruang tunggal di antara kata-kata dan akhirnya kami mengembalikan nama buku sebagai A Song of Ice and Fire
Sekarang untuk kolom selanjutnya dan kategori berikutnya, perhitungan untuk posisi awal di fungsi MID akan berubah seperti ini (D$4-1)*LEN($B5)+1. Ekspresi turun ke (2-1)*49+1 yang sama dengan 50. Jika fungsi MID mengembalikan karakter mulai dari karakter ke-50, dengan semua spasi tambahan yang ditambahkan oleh fungsi REPT, apa yang dikembalikan fungsi MID akan mengikuti pola ini. spasi penulis spasi
Ruang depan dan belakang akan dipangkas oleh fungsi TRIM dan hasilnya adalah George RR Martin
"+1" di argumen posisi awal fungsi MID tidak memiliki relevansi untuk kolom berikutnya, hanya untuk yang pertama. Itu karena, tanpa "+1" pada perhitungan kolom pertama, akan menjadi 0*49 yang akan berakhir dengan #VALUE. kesalahan
Rumus yang disalin di sepanjang kolom E memberi kita genre dari teks gabungan di kolom B dan itu melengkapi set kita
Pisahkan String pada Karakter Tertentu
Jika hanya ada satu pembatas yang merupakan karakter tertentu, rumus panjang seperti di atas tidak diperlukan untuk memisahkan teks. Katakanlah, seperti contoh kasus kita di bawah ini, kita membagi kode produk dan jenis produk yang digabungkan dengan tanda hubung
Sekarang akan lebih mudah jika kode produk memiliki jumlah karakter yang tetap; . Tapi apa asyiknya itu?
Kita akan membiarkan fungsi FIND melakukan sedikit pencarian untuk kita dan menemukan tanda hubung dalam teks sehingga fungsi LEFT dan fungsi RIGHT dapat mengembalikan teks di sekitarnya. Ini adalah rumus dengan fungsi LEFT untuk mengembalikan ekstrak pertama
=LEFT(B3,FIND("-",B3)-1)
Fungsi FIND mencari B3 untuk posisi tanda hubung “-“ dalam string teks, yaitu 6. Fungsi LEFT kemudian mengembalikan karakter mulai dari kiri teks dan jumlah karakter yang dikembalikan adalah 6-1. "-1" di bagian akhir memastikan bahwa karakter yang dikembalikan tidak menyertakan tanda hubung itu sendiri. Berikut adalah hasil rumus ini untuk mengembalikan segmen pertama dari teks terpisah
Sekarang untuk segmen teks kedua, fungsi RIGHT berperan dengan rumus ini
=RIGHT(B3,LEN(B3)-FIND("-",B3))_
Fungsi FIND kembali digunakan untuk mencari letak tanda hubung pada B3 yang kita ketahui adalah karakter ke-6. Fungsi LEN mengembalikan jumlah karakter dalam B3 sebagai 23. Fungsi RIGHT mengekstrak 23-6 karakter dari B3 dan mengembalikan tipe produk “Bluetooth Speaker”. Ini adalah cara kerjanya untuk contoh kita
Menggunakan Flash Fill
Fitur Isi Cepat di Excel secara otomatis mengisi nilai berdasarkan beberapa contoh yang diberikan secara manual. Kemudahan Flash Fill adalah Anda tidak perlu mengingat formula apa pun, menggunakan wizard apa pun, atau mengutak-atik pengaturan apa pun. Jika data Anda konsisten, Isi Cepat akan menjadi yang tercepat untuk memahami apa yang ingin Anda selesaikan. Mari kita lihat langkah-langkah menggunakan Flash Fill untuk memisahkan teks dan cara kerjanya untuk contoh kasus kita
- Ketik teks pertama sebagai contoh Flash Fill untuk mengambil pola dan tekan Enter
- Dari grup Pengeditan tab Beranda, klik ikon Isi dan pilih Isi Cepat dari menu
- Atau, gunakan tombol pintas Ctrl + E
- Mengambil contoh yang diberikan, Flash Fill akan membagi teks dan mengisi kolom sesuai dengan pola yang sama
- Ulangi langkah yang sama untuk setiap kolom yang akan Diisi Flash
Isi Cepat akan menghemat masalah karena harus memotong ruang depan dan belakang tetapi seperti yang disebutkan, jika ada anomali atau ketidakkonsistenan dalam data (mis. g spasi sebelum dan sesudah koma), Flash Fill tidak akan menjadi metode pemisahan teks yang andal dan karena banyaknya data, masalah mungkin diabaikan. Jika Anda meragukan data memiliki ketidakkonsistenan, gunakan metode lain untuk memisahkan teks
Menggunakan Fungsi VBA
Metode terakhir yang akan kita diskusikan hari ini untuk memisahkan teks adalah fungsi VBA. Untuk mengotomatiskan tugas dalam aplikasi MS Office, makro dapat dibuat dan digunakan dengan VBA. Tugas kita adalah membagi teks di Excel dan di bawah ini adalah langkah-langkah untuk melakukan ini menggunakan VBA
- Jika Anda menambahkan tab Pengembang ke Pita bilah alat, klik tab Pengembang, lalu pilih ikon Visual Basic di grup Kode untuk meluncurkan Visual Basic
- Anda juga dapat menggunakan tombol Alt + F11
- Editor Visual Basic akan terbuka
- Buka tab Sisipkan dan pilih Modul dari daftar. Jendela Modul akan terbuka
- Di jendela Modul, salin-tempel kode berikut untuk membuat makro berjudul SplitText
Sub SplitText()
Dim MyArray() As String, Count As Long, i As Variant
For n = 4 To 16
MyArray = Split(Cells(n, 2), ",")
Count = 3
For Each i In MyArray
Cells(n, Count) = i
Count = Count + 1
Next i
Next n
End Sub
Edit bagian kode berikut sesuai data Anda
- 'For n = 4 To 16' – 4 dan 16 mewakili baris pertama dan terakhir dari kumpulan data
- 'MyArray = Split(Cells(n, 2), ",")' – Koma yang dilampirkan dengan tanda kutip ganda adalah pembatas
- 'Count = 3' – 3 adalah nomor kolom dari kolom pertama tempat data yang dihasilkan akan dikembalikan
- Untuk menjalankan kode, tekan F5
Data akan dibagi sesuai nilai yang disediakan
- Bersihkan spasi terdepan di kolom D dan E menggunakan fungsi TRIM
Sekarang mari kita pisahkan panduan aktif dari kesimpulannya. Hari ini Anda mempelajari beberapa cara tentang cara membagi teks di Excel. Jika Anda menemukan diri Anda membelah rambut pada kemampuan Anda untuk membagi teks lain kali, kantongi yang ini dan bersiaplah untuk mencobanya. Kami akan kembali dengan keunggulan lebih untuk mengisi kantong Anda. Beri sedikit ruang