Tutorial Regex di Excel: Kuasai Pencocokan Pola dengan Regular Expressions

Menggunakan RegEx di Excel

Meskipun Excel tidak mendukung RegEx secara native, Anda dapat mengaktifkannya dengan menulis definisi khusus menggunakan skrip VBA (untuk Windows) atau mengaktifkan add-in (untuk Mac). Mari kita bahas cara mengaktifkan fungsi regex untuk mengekstrak teks dari sebuah kolom di Excel.

Untuk Windows

Untuk memulai, tekan Alt+F11 untuk membuka Visual Basic Editor. Anda juga dapat membukanya melalui Tools > Macro > Visual Basic Editor. Lalu, klik Insert > Module.

Tempelkan fungsi berikut ke dalam modul:

Public Function CustomRegExpExtract(inputText As String, regexPattern As String, Optional instanceNumber As Integer = 0, Optional matchCase As Boolean = True) As Variant
    Dim textMatches() As String
    Dim matchesIndex As Integer
    Dim regex As Object
    Dim matches As Object
    
    On Error GoTo ErrHandler
    
    CustomRegExpExtract = ""
    
    ' Create a regular expression object
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = regexPattern
    regex.Global = True
    regex.MultiLine = True
    
    ' Set case sensitivity
    If matchCase Then
        regex.IgnoreCase = False
    Else
        regex.IgnoreCase = True
    End If
    
    ' Execute the regex pattern on the input text
    Set matches = regex.Execute(inputText)
    
    If matches.Count > 0 Then
        If instanceNumber = 0 Then
            ' If instanceNumber is 0, return all matches in an array
            ReDim textMatches(matches.Count - 1, 0)
            For matchesIndex = 0 To matches.Count - 1
                textMatches(matchesIndex, 0) = matches.Item(matchesIndex)
            Next matchesIndex
            CustomRegExpExtract = textMatches
        Else
            ' If instanceNumber is specified, return the match at that instance
            CustomRegExpExtract = matches.Item(instanceNumber - 1)
        End If
    End If
    
    Exit Function

ErrHandler:
    ' Handle errors by returning an error value
    CustomRegExpExtract = CVErr(xlErrValue)
End Function

' Modified from an original script by Patrick Matthews

Simpan fungsinya dan kembali ke lembar kerja. Sekarang, simpan workbook sebagai berkemampuan makro, dengan ekstensi file .xlsm.

Menggunakan Fungsi di Lembar Kerja Excel

Dengan mengaktifkan makro di workbook Excel Anda, kini Anda dapat menggunakan fungsi CustomRegExpExtract seperti fungsi biasa di Excel untuk mengurai pola string yang kompleks, di mana:

• inputText adalah string asli yang akan diurai;

• regexPattern adalah pola regular expression yang digunakan untuk mengurai inputText;

• instanceNumber secara bawaan mengembalikan semua kemunculan pola, tetapi Anda dapat memasukkan bilangan bulat untuk menentukan jumlah kemunculan yang akan dikembalikan; dan

• matchCase menentukan apakah mempertimbangkan huruf besar/kecil saat pencocokan (TRUE atau dikosongkan) atau mengabaikannya (FALSE).

Misalkan kita tertarik mengekstrak nomor telepon dari sebuah kolom. Kita dapat mengetikkan pola regex di sel mana pun (pada contoh di bawah, polanya ada di sel A2). Di sel A6 terdapat contoh string bebas, "Sophia, 1111111111". Di sel B6, kita ketik =CustomRegExpExtract(A6,$A$2). Fungsi mengekstrak nomor telepon dengan mencocokkan string terhadap pola regex di sel A2 dan mengembalikan "1111111111", sesuai harapan.

Untuk Mac OS

Sayangnya, Mac OS tidak mengenali pustaka MS VBScript Regular Expressions 5.5. Untuk mengaktifkan pencocokan pola yang kompleks di Excel pada Mac, ada dua opsi: menggunakan advanced filter atau memasang paket/add-in.

Menggunakan advanced filter

Advanced filter di Excel membantu melakukan operasi penyaringan data yang kompleks berdasarkan sekumpulan kriteria. Fitur ini sangat berguna saat menangani dataset besar. Berikut cara menggunakan advanced filter di Excel:

• Siapkan data Anda: Pastikan data Anda berbentuk tabel dengan header yang jelas.

• Buat Criteria Range: Siapkan area terpisah di lembar kerja untuk mendefinisikan kriteria penyaringan. Area ini harus memiliki header yang sama dengan kolom yang ingin Anda saring. Di bawah header tersebut, tentukan kriterianya. Anda dapat memasukkan beberapa kriteria di bawah header yang sama untuk logika "OR", atau pada baris terpisah untuk logika "AND".

• Pilih Data: Klik sel dalam dataset Anda, atau pilih seluruh rentang yang ingin disaring.

• Atur Filter: Klik tab Data pada pita Excel dan buka opsi Advanced Filter. Klik Advanced di grup Sort & Filter. Di kotak dialog Advanced Filter, pilih apakah Anda ingin memfilter daftar di tempat atau menyalin hasilnya ke lokasi lain. Tentukan List range (data Anda). Tentukan Criteria range (tempat Anda menetapkan kondisi penyaringan). Klik OK untuk menerapkan filter.

Misalkan Anda memiliki dataset dengan kolom Name, Age, City. Anda ingin menyaring untuk menampilkan orang berusia di atas 30 tahun di New York atau siapa pun di Los Angeles. Criteria range Anda mungkin terlihat seperti ini:

Memasang add-in

• Kutools for Excel: Kutools adalah add-in komprehensif yang mencakup banyak fitur, salah satunya kemampuan untuk menemukan dan mengganti data menggunakan regex. Add-in ini menambahkan tab baru pada pita Excel dengan beragam alat, termasuk fungsi regex.

• RegEx Find/Replace: Ini adalah add-in untuk Excel yang memungkinkan Anda mencari dan mengganti teks menggunakan regular expressions. Add-in ini terintegrasi ke dalam Excel dan menyediakan fungsi cari/ganti yang lebih canggih dibandingkan opsi bawaan.

• PowerGREP: Meskipun bukan add-in Excel langsung, PowerGREP dapat bekerja dengan file Excel. Ini adalah alat grep bertenaga untuk Windows yang memungkinkan operasi regex kompleks pada file teks dan biner. Ini lebih merupakan aplikasi mandiri namun dapat digunakan bersama Excel untuk pemrosesan regex tingkat lanjut. Harganya tidak murah. Lisensi pengguna tunggal berharga $159 dan harganya naik hingga $5.300 untuk lisensi 100 pengguna.

Beberapa add-in dapat ditemukan langsung di Excel melalui tab Insert > Get Add-ins.

Yang lain perlu diunduh dari sumber eksternal dan ikuti petunjuk pemasangannya.

Beberapa pertimbangan:

• Kompatibilitas: Pastikan add-in kompatibel dengan versi Excel dan sistem operasi Anda.
• Keamanan: Hanya unduh dan pasang add-in dari sumber tepercaya.
• Kebutuhan Fungsional: Pertimbangkan tugas regex spesifik yang perlu Anda lakukan dan pilih add-in yang paling sesuai.

Kesimpulan

Dalam banyak kasus, fungsi bawaan seperti FIND, SEARCH, dan REPLACE akan membantu 90% kebutuhan manipulasi teks Anda di Excel. Namun, untuk 10% sisanya, pada kasus yang melibatkan pola string yang lebih kompleks, regex dapat sangat membantu. Untuk mempelajari lebih lanjut penerapan regular expressions menggunakan bahasa pemrograman populer, lihat kursus kami tentang Regular Expressions in Python dan Intermediate Regular Expressions in R.

Untuk mempercepat penguasaan semua hal tentang Excel, lihat jalur keterampilan Excel Fundamentals kami, yang membimbing Anda melalui keterampilan esensial agar mahir menggunakan alat ini.