Cara memisahkan data di excel berdasarkan word

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] )

Anda sekarang dapat memecah string menjadi beberapa bagian menggunakan rumus FILTERXML di atas

Ini akan mengembalikan bagian pertama Tabbie O'Hallagan

= RIGHT ( Text, [Number] )

Untuk 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

1. Pilih seluruh rentang data sampel (B2. B12)
2. Klik pada tab Data di pita Excel
3. 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
4. Pilih Dibatasi pada tombol opsi
5. Tekan tombol Berikutnya

1. Pilih Koma sebagai pembatas, dan hapus centang pembatas lainnya
2. Tekan tombol Berikutnya
3. Jendela Pratinjau Data akan menampilkan bagaimana data Anda akan dipisahkan. Pilih lokasi untuk menempatkan output
4. 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] )

Klik 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] )

Di 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] )

Masukkan 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

Bagaimana cara memisahkan data di Excel berdasarkan kriteria?

Apakah ada cara untuk memisahkan data di Excel?