Excel membagi kolom dengan panjang karakter

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

Bagaimana cara membagi sel berdasarkan karakter di Excel?

Bagaimana cara mengekstrak teks dari sejumlah karakter tertentu di Excel?

Bagaimana cara mengekstrak teks sebelum dan sesudah karakter tertentu di Excel?

Bagaimana cara membagi teks yang dipilih menjadi kolom menggunakan karakter koma sebagai pembatas?