Di Excel, ada berbagai alasan mengapa Anda mungkin perlu membagi data sel. Misalnya, data mentah mungkin berisi beberapa bagian informasi yang digabungkan ke dalam satu sel, seperti nama lengkap atau alamat. Memisahkan sel-sel ini memungkinkan Anda memisahkan berbagai jenis informasi, membuat data lebih mudah dibersihkan dan dianalisis. Artikel ini akan berfungsi sebagai panduan komprehensif Anda, menunjukkan berbagai cara untuk membagi sel menjadi baris atau kolom berdasarkan pemisah tertentu.

Pisahkan sel di Excel menjadi beberapa kolom

• Dengan panduan Teks ke Kolom
• Mudah dengan Kutools
• Dengan Flash Fill (Excel 2013 dan yang lebih baru)
• Dengan formula

Pisahkan sel di Excel menjadi beberapa baris

• Dengan fungsi TEXTSPLIT (Excel 365)
• Mudah dengan Kutools
• Dengan kode VBA
• Dengan Power Query (Excel 2016 dan lebih baru)

Video

Pisahkan sel di Excel menjadi beberapa kolom

Seperti yang ditunjukkan pada tangkapan layar berikut, misalkan Anda memiliki daftar nama lengkap, dan Anda ingin membagi setiap nama lengkap menjadi nama depan dan belakang yang terpisah dan menempatkan data yang dipisahkan di kolom terpisah. Bagian ini akan menunjukkan empat cara untuk membantu Anda menyelesaikan tugas ini.

Pisahkan sel menjadi beberapa kolom dengan panduan Teks ke Kolom

Untuk membagi sel menjadi beberapa kolom berdasarkan pemisah tertentu, salah satu metode yang umum digunakan adalah Teks ke Kolom penyihir di Excel. Di sini, saya akan menunjukkan kepada Anda langkah demi langkah cara menggunakan wizard ini untuk mencapai hasil yang diinginkan.

Langkah 1: Pilih sel yang ingin Anda pisahkan dan buka panduan Teks ke Kolom

Dalam hal ini, saya memilih rentang A2: A8, yang berisi nama lengkap. Lalu pergi ke Data tab, klik Teks ke Kolom untuk membuka Teks ke Kolom wizard.

Langkah 2: Konfigurasikan langkah-langkahnya satu per satu di wizard

1. Dalam majalah Langkah 1 dari 3 wizard, pilih Terbatas pilihan dan kemudian klik Selanjutnya .
   

   

2. Dalam majalah Langkah 2 dari 3 wizard, pilih pembatas untuk data Anda dan kemudian klik Selanjutnya tombol untuk melanjutkan.
   
   Dalam hal ini, karena saya perlu membagi nama lengkap menjadi nama depan dan belakang berdasarkan spasi, saya hanya memilih Space kotak centang di Pembatas bagian.

   

   Catatan:

   • Jika pembatas yang Anda butuhkan tidak ditampilkan di bagian ini, Anda dapat memilih Lainnya kotak centang dan masukkan pembatas Anda sendiri ke dalam kotak teks.
   • Untuk memisahkan sel dengan jeda baris, Anda dapat memilih Lainnya kotak centang dan tekan Ctrl + J kunci bersama
     
     
3. Di wisaya terakhir, Anda perlu mengonfigurasi sebagai berikut:
   
   1) Di Tujuan kotak, pilih sel untuk menempatkan data split. Disini saya memilih sel C2.

   2) Klik Finish .

   

Hasil

Nama lengkap dalam sel yang dipilih dipisahkan menjadi nama depan dan belakang dan terletak di kolom yang berbeda.

Pisahkan sel menjadi beberapa kolom dengan mudah menggunakan Kutools

Seperti yang Anda lihat, Teks ke Kolom wizard membutuhkan beberapa langkah untuk menyelesaikan tugas. Jika Anda membutuhkan metode yang lebih sederhana, the Pisahkan Sel fitur dari Kutools untuk Excel sangat dianjurkan. Dengan fitur ini, Anda dapat dengan mudah membagi sel menjadi beberapa kolom atau baris berdasarkan pembatas tertentu, dengan menyelesaikan pengaturan dalam satu kotak dialog.

Setelah menginstal Kutools untuk Excel, pilih Kutools > Gabungkan & Pisahkan > Pisahkan Sel untuk membuka Pisahkan Sel kotak dialog.

1. Pilih rentang sel yang berisi teks yang ingin Anda pisahkan.
2. Pilih Pisahkan ke Kolom .
3. Pilih Space (atau pembatas apa pun yang Anda butuhkan) dan klik OK.
   
   
4. Pilih sel tujuan dan klik OK untuk mendapatkan semua data terpisah.
   
   

Pisahkan sel menjadi beberapa kolom dengan Flash Fill

Sekarang mari beralih ke metode ketiga, yang dikenal sebagai Isi Flash. Diperkenalkan di Excel 2013, Isi Flash dirancang untuk mengisi data Anda secara otomatis saat mendeteksi suatu pola. Pada bagian ini, saya akan mendemonstrasikan bagaimana menggunakan fitur Flash Fill untuk memisahkan nama depan dan belakang dari nama lengkap dalam satu kolom.

Langkah 1: Masukkan data split pertama secara manual di sel yang berdekatan dengan kolom asli

Dalam hal ini, saya akan membagi nama lengkap di kolom A menjadi nama depan dan belakang yang terpisah. Nama lengkap pertama ada di sel A2, jadi saya pilih sel B2 di sebelahnya dan ketik nama depan. Lihat tangkapan layar:

Langkah 2: Terapkan Flash Fill untuk mengisi semua nama depan secara otomatis

Mulailah mengetik nama depan kedua ke dalam sel di bawah B2 (yaitu B3), kemudian Excel akan mengenali polanya dan menghasilkan pratinjau dari nama depan lainnya, dan Anda perlu menekan Enter untuk menerima pratinjau.

Sekarang semua nama depan dari nama lengkap di kolom A dipisahkan di kolom B.

Langkah 3: Dapatkan nama belakang dari nama lengkap di kolom lain

Anda perlu mengulangi Langkah 1 dan 2 di atas untuk memisahkan nama belakang dari nama lengkap di Kolom A ke dalam kolom di sebelah kolom nama depan.

Hasil

Pisahkan sel menjadi beberapa kolom dengan rumus

Metode di atas tidak dinamis, artinya jika data sumber berubah, maka kita perlu menjalankan kembali proses yang sama. Ambil contoh yang sama seperti di atas, untuk membagi daftar nama lengkap di Kolom A menjadi nama depan dan belakang yang terpisah dan memperbarui data terpisah secara otomatis dengan perubahan apa pun pada data sumber, silakan coba salah satu rumus berikut

Gunakan fungsi TEXT untuk membagi sel menjadi kolom dengan pembatas tertentu

Rumus yang disediakan di bagian ini tersedia di semua versi Excel. Untuk menerapkan rumus, lakukan hal berikut.

Langkah 1: Ekstrak teks sebelum pembatas pertama (nama depan dalam kasus ini)

1. Pilih sel (C2 dalam hal ini) untuk menampilkan nama depan, masukkan rumus berikut dan tekan Enter untuk mendapatkan nama depan di A2.
   

   =LEFT(A2,SEARCH(" ",A2)-1)

   
2. Pilih sel hasil ini dan seret Pegangan IsiOtomatis ke bawah untuk mendapatkan nama depan lainnya.
   
   

Langkah 2: Ekstrak teks setelah pembatas pertama (dalam hal ini nama belakang)

1. Pilih sel (D2 dalam hal ini) untuk menampilkan nama belakang, masukkan rumus berikut dan tekan Enter untuk mendapatkan nama belakang di A2.
   

   =RIGHT(A2,LEN(A2)-SEARCH(" ",A2))

   
2. Pilih sel hasil ini dan seret Pegangan IsiOtomatis ke bawah untuk mendapatkan sisa nama belakang.
   
   

Gunakan fungsi TEXTSPLIT untuk membagi sel menjadi kolom dengan pemisah tertentu

Jika Anda menggunakan Excel untuk Microsoft 365, yang fungsi TEKSPLIT lebih direkomendasikan. Silakan lakukan sebagai berikut.

Langkah 1: Pilih sel untuk menampilkan hasilnya. Disini saya pilih sel C2

Langkah 2: Masukkan rumus di bawah ini dan tekan Enter

=TEXTSPLIT(A2," ")

Anda dapat melihat bahwa semua teks yang dipisahkan oleh spasi di A2 dibagi menjadi beberapa kolom.

Langkah 3: Seret rumus untuk mendapatkan semua hasil

Pilih sel hasil di baris yang sama, lalu seret Pegangan IsiOtomatis ke bawah untuk mendapatkan semua hasil.

Pisahkan sel di Excel menjadi beberapa baris

Seperti yang ditunjukkan pada tangkapan layar di bawah, ada daftar detail pesanan dalam rentang A2:A4, dan data perlu dipisahkan menggunakan garis miring untuk mengekstrak berbagai jenis informasi seperti Barang, Kuantitas, Harga Satuan, dan Tanggal. Untuk menyelesaikan tugas ini, bagian ini menunjukkan 3 metode.

Pisahkan sel menjadi beberapa baris dengan fungsi TEXTSPLIT

Jika Anda menggunakan Excel untuk Microsoft 365, metode fungsi TEXTSPLIT dapat dengan mudah membantu. Silakan lakukan sebagai berikut.

Langkah 1: Pilih sel untuk menampilkan hasilnya. Disini saya pilih sel B6

Langkah 2: Ketik rumus di bawah ini dan tekan Enter

=TEXTSPLIT(A2,,"/")

Semua teks di A2 dibagi menjadi baris terpisah berdasarkan pemisah 'slash'.

Untuk membagi data dalam sel A3 dan A4 menjadi baris individual berdasarkan garis miring, cukup ulangi langkah 1 dan 2 dengan rumus yang sesuai di bawah ini.

Rumus dalam C6:

=TEXTSPLIT(A3,,"/")

Rumus di D6:

=TEXTSPLIT(A4,,"/")

Hasil

Pisahkan sel menjadi beberapa baris dengan mudah menggunakan Kutools

Meskipun fitur TEXTSPLIT Excel sangat berguna, fitur ini terbatas pada pengguna Excel untuk Microsoft 365. Selain itu, jika Anda memiliki banyak sel dalam satu kolom untuk dipisahkan, Anda perlu menerapkan rumus yang berbeda satu per satu ke setiap sel untuk mendapatkan hasilnya. Sebaliknya, Kutools untuk Excel's Pisahkan Sel fitur berfungsi di semua versi Excel. Ini memberikan solusi yang mudah dan efisien untuk membagi sel menjadi beberapa baris atau kolom sekaligus hanya dengan beberapa klik.

Setelah menginstal Kutools untuk Excelklik Kutools > Gabungkan & Pisahkan > Pisahkan Sel untuk membuka Pisahkan Sel kotak dialog.

1. Pilih rentang sel yang berisi teks yang ingin Anda pisahkan.
2. Pilih Pisahkan ke Baris .
3. Pilih pembatas yang Anda butuhkan (di sini saya pilih Lainnya dan masukkan garis miring), lalu klik OK.
   
   
4. Pilih sel tujuan dan klik OK untuk mendapatkan semua data terpisah
   
   

Pisahkan sel menjadi beberapa baris dengan kode VBA

Bagian ini menyediakan kode VBA agar Anda dapat membagi sel dengan mudah menjadi beberapa baris di Excel. Silakan lakukan sebagai berikut.

Langkah 1: Buka jendela Microsoft Visual Basic for Applications

Tekan lain + F11 kunci untuk membuka jendela ini.

Langkah 2: Masukkan modul dan masukkan kode VBA

Klik Menyisipkan > Modul, lalu salin dan tempel kode VBA berikut ke dalam Modul (Kode) jendela.

Kode VBA: Pisahkan sel menjadi beberapa baris di Excel

Option Explicit

Sub SplitCellsToRows()
'Updated by Extendoffice 20230727
    Dim inputRng As Range
    Dim outputRng As Range
    Dim cell As Range
    Dim splitValues() As String
    Dim delimiter As String
    Dim i As Long
    Dim columnOffset As Long
    On Error Resume Next
    
    Set inputRng = Application.InputBox("Please select the input range", "Kutools for Excel", Type:=8) ' Ask user to select input range
    If inputRng Is Nothing Then Exit Sub ' If the user clicked Cancel or entered nothing, exit the sub
    Set outputRng = Application.InputBox("Please select the output range", "Kutools for Excel", Type:=8) ' Ask user to select output range
    If outputRng Is Nothing Then Exit Sub ' If the user clicked Cancel or entered nothing, exit the sub
    delimiter = Application.InputBox("Please enter the delimiter to split the cell contents", "Kutools for Excel", Type:=2) ' Ask user for delimiter
    If delimiter = "" Then Exit Sub ' If the user clicked Cancel or entered nothing, exit the sub
    If delimiter = "" Or delimiter = "False" Then Exit Sub ' If the user clicked Cancel or entered nothing, exit the sub
    
    Application.ScreenUpdating = False
    
    columnOffset = 0
    For Each cell In inputRng
        If InStr(cell.Value, delimiter) > 0 Then
            splitValues = Split(cell.Value, delimiter)
            For i = LBound(splitValues) To UBound(splitValues)
                outputRng.Offset(i, columnOffset).Value = splitValues(i)
            Next i
            columnOffset = columnOffset + 1
        Else
            outputRng.Offset(0, columnOffset).Value = cell.Value
            columnOffset = columnOffset + 1
        End If
    Next cell
    
    Application.ScreenUpdating = True
End Sub

Langkah 3: Jalankan kode VBA

Tekan F5 kunci untuk menjalankan kode. Maka Anda perlu melakukan konfigurasi berikut.

1. Sebuah kotak dialog akan muncul meminta Anda untuk memilih sel dengan data yang ingin Anda bagi (di sini saya memilih rentang A2:A4). Setelah menentukan pilihan, klik OK.
   
   
2. Di kotak dialog yang muncul kedua, Anda harus memilih rentang keluaran (di sini saya memilih sel B6), lalu klik OK.
   
   
3. Pada kotak dialog terakhir, masukkan pembatas yang digunakan untuk membagi isi sel (di sini saya memasukkan garis miring) lalu klik tombol OK .
   
   

Hasil

Sel dalam rentang yang dipilih dibagi menjadi beberapa baris sekaligus.

Pisahkan sel menjadi beberapa baris dengan Power Query

Metode lain untuk memisahkan sel menjadi beberapa baris dengan pembatas tertentu adalah dengan menggunakan Power Query, yang juga dapat membuat data yang dipisahkan berubah secara dinamis dengan data sumber. Kelemahan dari metode ini adalah dibutuhkan beberapa langkah untuk menyelesaikannya. Mari selami untuk melihat cara kerjanya.

Langkah 1: Pilih sel yang ingin Anda pisahkan menjadi beberapa baris, pilih Data > From Table / Range

Langkah 2: Ubah sel yang dipilih menjadi tabel

Jika sel yang dipilih bukan format tabel Excel, a Buat tabel kotak dialog akan muncul. Di kotak dialog ini, Anda hanya perlu memverifikasi apakah Excel telah memilih rentang sel yang Anda pilih dengan benar, tandai jika tabel Anda memiliki tajuk, lalu klik tombol OK .
Jika sel yang dipilih adalah tabel Excel, lompat ke Langkah 3.

Langkah 3: Pilih Pisahkan Kolom Dengan Pembatas

A Meja - Power Query editor jendela muncul, klik Pisahkan Kolom > Oleh Pembatas bawah Beranda Tab.

Langkah 4: Konfigurasikan kotak dialog Split Column by Delimiter

1. Dalam majalah Pilih atau masukkan pembatas bagian, tentukan pembatas untuk memisahkan teks (Di sini saya pilih Kustom dan masukkan garis miring / dalam kotak teks).
2. Pilih Opsi Lanjutan bagian (yang dilipat secara default) dan pilih Baris .
3. Dalam majalah Karakter Kutipan bagian, pilih None dari daftar drop-down;
4. Klik OK.
   
   

Langkah 5: Simpan dan muat data split

1. Dalam hal ini, karena saya perlu menentukan tujuan khusus untuk data terpisah saya, saya mengklik Tutup & Muat > Tutup & Muat Ke.
   
   
   jenis: Untuk memuat data terpisah di lembar kerja baru, pilih Tutup & Muat .
2. Dalam majalah impor data kotak dialog, pilih Lembar kerja yang sudah ada , pilih sel untuk menemukan data yang dipisahkan, lalu klik OK.
   
   

Hasil

Kemudian semua sel dalam rentang yang dipilih dibagi menjadi beberapa baris berbeda dalam kolom yang sama dengan pembatas yang ditentukan.

Sebagai kesimpulan, artikel ini telah mengeksplorasi berbagai metode untuk membagi sel menjadi beberapa kolom atau baris di Excel. Apa pun pendekatan yang Anda pilih, menguasai teknik ini dapat sangat meningkatkan efisiensi Anda saat menangani data di Excel. Teruslah menjelajah, dan Anda akan menemukan metode yang paling cocok untuk Anda.

Artikel terkait