Parsing teks dengan ekspresi reguler (RegExp) di Excel

Salah satu tugas yang paling memakan waktu dan membuat frustrasi saat bekerja dengan teks di Excel adalah menguraikan – menguraikan "bubur" alfanumerik ke dalam komponen dan mengekstraksi fragmen yang kita butuhkan darinya. Sebagai contoh:

• mengekstrak kode pos dari alamat (ada baiknya jika kode pos selalu di awal, tetapi bagaimana jika tidak?)
• menemukan nomor dan tanggal faktur dari deskripsi pembayaran di laporan bank
• ekstraksi NPWP dari deskripsi beraneka ragam perusahaan dalam daftar rekanan
• cari nomor mobil atau nomor artikel di deskripsi, dll.
  

Biasanya dalam kasus seperti itu, setelah setengah jam memilah teks secara manual, pikiran mulai muncul di benak untuk mengotomatisasi proses ini (terutama jika ada banyak data). Ada beberapa solusi dan dengan berbagai tingkat kompleksitas-efisiensi:

• penggunaan fungsi teks Excel bawaan untuk mencari-potong-lem teks: LEVSIMV (KIRI), KANAN (BAIK), PSTR (pertengahan), STEPIT (MENGGABUNGKAN) dan analognya, MENGGABUNGKAN (TEKS BERSAMA), TEPAT (AKURAT) dll. Metode ini bagus jika ada logika yang jelas dalam teks (misalnya, indeks selalu di awal alamat). Jika tidak, rumus menjadi jauh lebih rumit dan, kadang-kadang, bahkan sampai ke rumus array, yang sangat memperlambat tabel besar.
• Menggunakan seperti operator kesamaan teks dari Visual Basic dibungkus dalam fungsi makro kustom. Ini memungkinkan Anda untuk menerapkan pencarian yang lebih fleksibel menggunakan karakter wildcard (*, #,?, dll.) Sayangnya, alat ini tidak dapat mengekstrak substring yang diinginkan dari teks – hanya periksa apakah ada di dalamnya.

Selain di atas, ada pendekatan lain yang sangat terkenal di kalangan sempit programmer profesional, pengembang web dan teknisi lainnya – ini adalah ekspresi reguler (Ekspresi Reguler = RegExp = “regexps” = “reguler”). Sederhananya, RegExp adalah bahasa di mana karakter dan aturan khusus digunakan untuk mencari substring yang diperlukan dalam teks, mengekstraknya, atau menggantinya dengan teks lain. Ekspresi reguler adalah alat yang sangat kuat dan indah yang melampaui semua cara lain untuk bekerja dengan teks dengan urutan besarnya. Banyak bahasa pemrograman (C#, PHP, Perl, JavaScript…) dan editor teks (Word, Notepad++…) mendukung ekspresi reguler.

Microsoft Excel sayangnya tidak memiliki dukungan RegExp di luar kotak, tetapi ini dapat dengan mudah diperbaiki dengan VBA. Buka Editor Visual Basic dari tab pembangun (Pengembang) atau pintasan keyboard lain+F11. Kemudian masukkan modul baru melalui menu Sisipkan – Modul dan salin teks fungsi makro berikut di sana:

Fungsi Publik RegExpExtract(Teks Sebagai String, Pola Sebagai String, Item Opsional Sebagai Integer = 1) Sebagai String Pada Kesalahan GoTo ErrHandl Set regex = CreateObject("VBScript.RegExp") regex.Pattern = Pola regex.Global = True If regex.Test (Teks) Kemudian Tetapkan kecocokan = regex.Execute(Teks) RegExpExtract = cocok.Item(Item - 1) Keluar dari Fungsi Selesai Jika ErrHandl: RegExpExtract = CVErr(xlErrValue) Fungsi Akhir  

Kami sekarang dapat menutup Editor Visual Basic dan kembali ke Excel untuk mencoba fitur baru kami. Sintaksnya adalah sebagai berikut:

=RegExpExtract( Txt ; Pola ; Item )

dimana

• txt – sel dengan teks yang kita periksa dan dari mana kita ingin mengekstrak substring yang kita butuhkan
• belt hold – topeng (pola) untuk pencarian substring
• Barang – nomor urut dari substring yang akan diekstraksi, jika ada beberapa (jika tidak ditentukan, maka kemunculan pertama ditampilkan)

Hal yang paling menarik di sini, tentu saja, adalah Pola – string templat karakter khusus “dalam bahasa” RegExp, yang menentukan apa sebenarnya dan di mana kita ingin menemukan. Berikut adalah yang paling dasar untuk Anda mulai:

Jika kami mencari sejumlah karakter tertentu, misalnya, kode pos enam digit atau semua kode produk tiga huruf, maka kami datang untuk menyelamatkan pengukur or pengukur adalah ekspresi khusus yang menentukan jumlah karakter yang akan dicari. Quantifier diterapkan pada karakter yang mendahuluinya:

Sekarang mari kita beralih ke bagian yang paling menarik – analisis penerapan fungsi yang dibuat dan apa yang kita pelajari tentang pola pada contoh praktis dari kehidupan.

Mengekstrak angka dari teks

Untuk memulai, mari kita analisis kasus sederhana – Anda perlu mengekstrak nomor pertama dari bubur alfanumerik, misalnya, kekuatan catu daya tak terputus dari daftar harga:

Logika di balik ekspresi reguler sederhana: d berarti setiap digit, dan quantifier + mengatakan bahwa jumlah mereka harus satu atau lebih. Tanda minus ganda di depan fungsi diperlukan untuk "on the fly" mengubah karakter yang diekstraksi menjadi angka lengkap dari angka-sebagai-teks.

Kode pos

Sekilas, semuanya sederhana di sini – kami mencari tepat enam digit berturut-turut. Kami menggunakan karakter khusus d untuk digit dan quantifier 6 {} untuk jumlah karakter:

Namun, situasi mungkin terjadi ketika, di sebelah kiri indeks di baris, ada set angka besar lainnya berturut-turut (nomor telepon, NPWP, rekening bank, dll.) Kemudian musim reguler kami akan mengeluarkan 6 yang pertama digit darinya, yaitu tidak akan berfungsi dengan benar:

Untuk mencegah hal ini terjadi, kita perlu menambahkan pengubah di sekitar tepi ekspresi reguler kita b menandakan akhir dari sebuah kata. Ini akan menjelaskan kepada Excel bahwa fragmen (indeks) yang kita butuhkan harus berupa kata yang terpisah, dan bukan bagian dari fragmen lain (nomor telepon):

Nomor Hp / Telephone

Masalah mencari nomor telepon di teks adalah begitu banyak pilihan untuk menulis nomor – dengan dan tanpa tanda hubung, spasi, dengan atau tanpa kode wilayah dalam tanda kurung, dll. Oleh karena itu, menurut saya, lebih mudah untuk pertama-tama bersihkan semua karakter ini dari teks sumber menggunakan beberapa fungsi bersarang PENGGANTI (PENGGANTI)sehingga menempel bersama menjadi satu kesatuan, dan kemudian dengan reguler primitif d{11} tarik 11 digit berturut-turut:

ITN

Sedikit lebih rumit di sini, karena NPWP (di Negara Kita) bisa 10 digit (untuk badan hukum) atau 12 digit (untuk perorangan). Jika Anda tidak menemukan kesalahan terutama, maka sangat mungkin untuk puas dengan yang biasa d{10,12}, tetapi, sebenarnya, itu akan mengeluarkan semua angka dari 10 hingga 12 karakter, yaitu dan salah memasukkan 11 digit. Akan lebih tepat untuk menggunakan dua pola yang dihubungkan oleh operator logika OR | (batang vertikal):

Harap dicatat bahwa dalam kueri pertama-tama kita mencari angka 12-bit, dan baru kemudian untuk angka 10-bit. Jika kita menulis ekspresi reguler kita sebaliknya, maka itu akan ditarik keluar untuk semua orang, bahkan TIN 12-bit yang panjang, hanya 10 karakter pertama. Artinya, setelah kondisi pertama dipicu, verifikasi lebih lanjut tidak lagi dilakukan:

Ini adalah perbedaan mendasar antara operator | dari fungsi logika excel standar OR (OR), di mana menata ulang argumen tidak mengubah hasilnya.

SKU Produk

Di banyak perusahaan, pengidentifikasi unik ditugaskan untuk barang dan jasa – artikel, kode SAP, SKU, dll. Jika ada logika dalam notasinya, maka mereka dapat dengan mudah ditarik keluar dari teks apa pun menggunakan ekspresi reguler. Misalnya, jika kita tahu bahwa artikel kita selalu terdiri dari tiga huruf kapital bahasa Inggris, tanda hubung, dan angka tiga digit berikutnya, maka:

Logika di balik template itu sederhana. [AZ] – berarti huruf kapital apa pun dari alfabet Latin. Kuantifier berikutnya 3 {} mengatakan bahwa penting bagi kita bahwa ada tepat tiga huruf seperti itu. Setelah tanda hubung, kami menunggu tiga digit, jadi kami menambahkan di akhir d{3}

Jumlah uang tunai

Dengan cara yang mirip dengan paragraf sebelumnya, Anda juga dapat menarik harga (biaya, PPN ...) dari deskripsi barang. Jika jumlah moneter, misalnya, ditunjukkan dengan tanda hubung, maka:

pola d dengan quantifier + mencari nomor apa pun hingga tanda hubung, dan d{2} akan mencari uang receh (dua digit) setelahnya.

Jika Anda perlu mengekstrak bukan harga, tetapi PPN, maka Anda dapat menggunakan argumen opsional ketiga dari fungsi RegExpExtract kami, yang menentukan nomor urut elemen yang akan diekstraksi. Dan, tentu saja, Anda dapat mengganti fungsinya PENGGANTI (PENGGANTI) dalam hasil, tanda hubung ke pemisah desimal standar dan tambahkan minus ganda di awal sehingga Excel menafsirkan PPN yang ditemukan sebagai angka normal:

Nomor plat mobil

Jika tidak membawa kendaraan khusus, trailer dan sepeda motor lainnya, maka standar nomor mobil diurai sesuai prinsip “huruf – tiga angka – dua huruf – kode wilayah”. Selain itu, kode wilayah dapat terdiri dari 2 atau 3 digit, dan hanya kode yang mirip dengan alfabet Latin yang digunakan sebagai huruf. Jadi, ekspresi reguler berikut akan membantu kita mengekstrak angka dari teks:

Waktu

Untuk mengekstrak waktu dalam format HH:MM, ekspresi reguler berikut ini cocok:

Setelah fragmen usus besar [0-5]d, karena mudah diketahui, tetapkan angka apa pun dalam kisaran 00-59. Sebelum titik dua dalam tanda kurung, dua pola bekerja, dipisahkan oleh logika OR (pipa):

• [0-1]d – angka apa saja dalam kisaran 00-19
• 2[0-3] – angka apa saja dalam kisaran 20-23

Untuk hasil yang diperoleh, Anda juga dapat menerapkan fungsi Excel standar WAKTU (TIM)untuk mengubahnya menjadi format waktu yang dapat dimengerti oleh program dan cocok untuk perhitungan lebih lanjut.

Pemeriksaan kata sandi

Misalkan kita perlu memeriksa daftar kata sandi yang ditemukan oleh pengguna untuk kebenarannya. Menurut aturan kami, kata sandi hanya boleh berisi huruf Inggris (huruf kecil atau besar) dan angka. Spasi, garis bawah, dan tanda baca lainnya tidak diperbolehkan.

Pemeriksaan dapat diatur menggunakan ekspresi reguler sederhana berikut:

Padahal, dengan pola seperti itu kita mengharuskan antara awal (^) dan akhir ($) dalam teks kami hanya ada karakter dari himpunan yang diberikan dalam tanda kurung siku. Jika Anda juga perlu memeriksa panjang kata sandi (misalnya, setidaknya 6 karakter), maka pengukur + dapat diganti dengan interval "enam atau lebih" dalam bentuk {6,}:

Kota dari alamat

Katakanlah kita perlu menarik kota dari bilah alamat. Program reguler akan membantu, mengekstraksi teks dari "g." ke koma berikutnya:

Mari kita lihat lebih dekat pola ini.

Jika Anda telah membaca teks di atas, maka Anda telah memahami bahwa beberapa karakter dalam ekspresi reguler (titik, tanda bintang, tanda dolar, dll.) memiliki arti khusus. Jika Anda perlu mencari sendiri karakter ini, maka karakter tersebut didahului dengan garis miring terbalik (kadang-kadang disebut perisai). Oleh karena itu, ketika mencari fragmen "g." kita harus menulis dalam ekspresi reguler Bapak. jika kita mencari nilai plus, maka + dan sebagainya

Dua karakter berikutnya dalam template kita, titik dan tanda bintang quantifier, mewakili sejumlah karakter apa pun, yaitu nama kota apa pun.

Ada koma di akhir template, karena kita mencari teks dari “g.” ke koma. Tapi bisa ada beberapa koma dalam teks, kan? Tidak hanya setelah kota, tetapi juga setelah jalan, rumah, dll. Di mana dari mereka permintaan kami akan berhenti? Itulah gunanya tanda tanya. Tanpa itu, ekspresi reguler kami akan mengeluarkan string terpanjang:

Dalam hal ekspresi reguler, pola seperti itu "serakah". Untuk memperbaiki situasi, tanda tanya diperlukan – itu membuat quantifier setelah itu berdiri “pelit” – dan kueri kami mengambil teks hanya sampai koma counter pertama setelah “g.”:

Nama file dari path lengkap

Situasi lain yang sangat umum adalah mengekstrak nama file dari path lengkap. Ekspresi reguler sederhana dari formulir akan membantu di sini:

Triknya di sini adalah bahwa pencarian, pada kenyataannya, terjadi dalam arah yang berlawanan – dari akhir ke awal, karena di akhir template kita adalah $, dan kami mencari semuanya sebelum itu hingga garis miring terbalik pertama dari kanan. Garis miring terbalik diloloskan, seperti titik pada contoh sebelumnya.

PS

“Menjelang akhir” Saya ingin mengklarifikasi bahwa semua hal di atas adalah sebagian kecil dari semua kemungkinan yang disediakan oleh ekspresi reguler. Ada banyak karakter dan aturan khusus untuk penggunaannya, dan seluruh buku telah ditulis tentang topik ini (saya sarankan setidaknya yang ini sebagai permulaan). Di satu sisi, menulis ekspresi reguler hampir merupakan seni. Hampir selalu, ekspresi reguler yang ditemukan dapat ditingkatkan atau ditambahkan, membuatnya lebih elegan atau dapat bekerja dengan rentang data input yang lebih luas.

Untuk menganalisis dan mengurai ekspresi reguler orang lain atau men-debug ekspresi reguler Anda sendiri, ada beberapa layanan online yang nyaman: RegEx101, RegExr dan lebih

Sayangnya, tidak semua fitur ekspresi reguler klasik didukung di VBA (misalnya, pencarian terbalik atau kelas POSIX) dan dapat bekerja dengan Cyrillic, tetapi saya pikir apa yang ada cukup untuk pertama kali menyenangkan Anda.

Jika Anda tidak baru dalam topik ini, dan Anda memiliki sesuatu untuk dibagikan, biarkan ekspresi reguler berguna saat bekerja di Excel di komentar di bawah. Satu pikiran itu bagus, tapi dua sepatu bot adalah sepasang!

• Mengganti dan membersihkan teks dengan fungsi SUBSTITUTE
• Cari dan sorot karakter Latin dalam teks
• Cari teks serupa terdekat (Ivanov = Ivonov = Ivanof, dll.)