Parse téks nganggo éksprési biasa (RegExp) dina Excel

Salah sahiji tugas anu paling nyéépkeun waktos sareng frustrasi nalika damel sareng téks dina Excel nyaéta parsing - parsing "bubur" alfanumerik kana komponén sareng nimba fragmen anu urang peryogikeun. Salaku conto:

• extracting kode pos ti alamatna (alus lamun kode pos sok di awal, tapi kumaha lamun henteu?)
• Pananjung nomer sareng tanggal invoice tina pedaran pembayaran dina pernyataan bank
• ékstraksi TIN tina déskripsi motley pausahaan dina daptar counterparties
• milarian nomer mobil atanapi nomer artikel dina katerangan, jsb.
  

Biasana dina kasus sapertos kitu, saatos satengah jam dreary picking dina téks sacara manual, pikiran mimiti datang ka pikiran kumaha carana ngajadikeun otomatis proses ieu (utamana lamun aya loba data). Aya sababaraha solusi sareng tingkat pajeulitna-efisiensi anu béda-béda:

• make diwangun-di fungsi téks Excel pikeun milarian-cut-lem téks: LEVSIMV (Kénca), bener (KANAN), PSTR (tengah), STsEPIT (CONCATENATE) sareng analogna, GABUNGAN (JOINTEXT), ATAS (TEPAT) jsb Metoda ieu alus lamun aya logika jelas dina téks (contona, indéks sok di awal alamat). Upami teu kitu, rumus jadi leuwih pajeulit jeung, sakapeung, malah datang ka Asép Sunandar Sunarya rumus, nu greatly slows turun dina tabel badag.
• maké kawas operator kasaruaan téks ti Visual Basic dibungkus dina fungsi makro custom. Ieu ngidinan Anjeun pikeun nerapkeun pilarian leuwih fleksibel ngagunakeun karakter wildcard (*, #,?, jsb) Hanjakal, alat ieu teu bisa nimba substring nu dipikahoyong tina téks - ngan pariksa lamun eta dikandung dina eta.

Salian di luhur, aya pendekatan sejen anu kacida alusna dipikawanoh di kalangan sempit programer profésional, pamekar web na techies lianna - ieu téh ungkapan biasa (Ucapan Biasa = RegExp = "regexps" = "biasa"). Kantun nempatkeun, RegExp mangrupikeun basa dimana karakter sareng aturan khusus dianggo pikeun milarian substrings anu diperyogikeun dina téks, nimba atanapi ngagentos ku téks anu sanés.. Babasan biasa mangrupikeun alat anu kuat pisan sareng éndah anu ngalangkungan sagala cara anu sanés pikeun damel sareng téks ku urutan gedéna. Seueur basa pamrograman (C#, PHP, Perl, JavaScript…) sareng éditor téks (Word, Notepad++…) ngadukung éksprési biasa.

Microsoft Excel hanjakalna teu boga rojongan RegExp out of the box, tapi ieu bisa gampang dibereskeun ku VBA. Buka Visual Basic Editor tina tab pamekar (Pamekar) atawa potong kompas keyboard Alt+F11. Lajeng selapkeun modul anyar ngaliwatan menu nu Selapkeun - Modul sareng salin téks fungsi makro ieu di dinya:

Fungsi Umum RegExpExtract(Téks Salaku String, Pola Salaku String, Pilihan Item Salaku Integer = 1) Salaku String Dina Kasalahan GoTo ErrHandl Set regex = CreateObject ("VBScript.RegExp") regex.Pattern = Pola regex.Global = Leres Upami regex.Test (Téks) Lajeng Atur patandingan = regex.Execute(Text) RegExpExtract = matches.Item(Item - 1) Kaluar Fungsi Tungtung Lamun ErrHandl: RegExpExtract = CVErr(xlErrValue) Fungsi Tungtung  

Urang ayeuna tiasa nutup Visual Basic Editor sareng uih deui ka Excel pikeun nyobian fitur anyar urang. Sintaksisna nyaéta kieu:

=RegExpExtract( Txt ; Pola ; Barang )

di mana

• txt - sél sareng téks anu urang parios sareng ti mana urang hoyong nimba substring anu urang peryogikeun
• corak – topéng (pola) pikeun milarian substring
• barang - nomer urutan substring anu bakal diekstrak, upami aya sababaraha di antarana (upami henteu ditangtukeun, maka kajadian anu munggaran ditampilkeun)

Hal anu paling narik di dieu, tangtosna, nyaéta Pola - senar témplat tina karakter khusus "dina basa" RegExp, anu netepkeun naon persisna sareng dimana urang hoyong mendakan. Ieu mangrupikeun anu paling dasar pikeun ngamimitian anjeun:

Upami urang milarian sababaraha karakter, contona, kode pos genep digit atanapi sadaya kode produk tilu huruf, maka urang sumping ka nyalametkeun. jumlahna or jumlahna mangrupa éksprési husus nu nangtukeun jumlah karakter nu bakal searched. Quantifiers diterapkeun kana karakter anu sateuacanna:

Ayeuna hayu urang ngaléngkah ka bagian anu paling narik - analisa aplikasi fungsi anu diciptakeun sareng naon anu urang diajar ngeunaan pola dina conto praktis tina kahirupan.

Extracting angka tina téks

Pikeun mimitian, hayu urang nganalisis kasus anu sederhana - anjeun kedah nimba nomer kahiji tina bubur alfanumerik, contona, kakuatan suplai listrik anu teu tiasa diganggu tina daptar harga:

Logika balik éksprési biasa basajan: d hartina digit wae, sarta quantifier + nyebutkeun yen jumlah maranéhanana kudu hiji atawa leuwih. The dikurangan ganda di hareup fungsi diperlukeun pikeun "dina laleur" ngarobah karakter sasari kana angka pinuh tina angka-sakumaha-téks.

kode pos

Dina glance kahiji, sagalana geus basajan di dieu - urang pilari persis genep digit dina urutan. Urang ngagunakeun karakter husus d pikeun digit jeung quantifier 6 {} pikeun jumlah karakter:

Sanajan kitu, hiji situasi mungkin lamun, ka kénca ti indéks dina garis, aya set badag sejen tina angka sakaligus (nomer telepon, TIN, rekening bank, jsb) Lajeng usum biasa urang bakal narik kaluar 6 munggaran. digit ti dinya, ie moal jalan bener:

Pikeun nyegah ieu lumangsung, urang kudu nambahkeun modifier sabudeureun edges of ekspresi biasa urang b nandakeun tungtung kecap. Ieu bakal ngajelaskeun ka Excel yén sempalan (indéks) anu urang peryogikeun kedah janten kecap anu misah, sareng sanés bagian tina sempalan (nomer telepon):

Phone

Masalah sareng milarian nomer telepon dina téks nyaéta yén aya seueur pilihan pikeun nyerat nomer - nganggo sareng tanpa tanda hubung, ngalangkungan rohangan, nganggo atanapi henteu nganggo kode wilayah dina kurung, jsb. kahiji ngabersihan kaluar kabeh karakter ieu tina téks sumber ngagunakeun sababaraha fungsi nested SUBSTITUT (GANTIKAN)supaya lengket ngahiji jadi hiji gembleng, lajeng kalawan biasa primitif d{11} tarik kaluar 11 digit sakaligus:

ITN

Ieu sakedik langkung rumit, sabab TIN (di Nagara Urang) tiasa 10-angka (pikeun badan hukum) atanapi 12-angka (pikeun individu). Upami anjeun henteu mendakan kalepatan khususna, maka cukup mungkin pikeun puas ku anu biasa d{10,12}, tapi, tegesna, eta bakal narik kaluar kabeh angka ti 10 nepi ka 12 karakter, ie na erroneously diasupkeun 11 digit. Eta bakal leuwih bener ngagunakeun dua pola disambungkeun ku operator OR logis | (bar nangtung):

Punten dicatet yén dina pamundut urang mimiti milarian nomer 12-bit, teras ngan ukur nomer 10-bit. Upami urang nyerat ekspresi biasa urang sabalikna, maka éta bakal kaluar pikeun sadayana, bahkan TIN 12-bit anu panjang, ngan ukur 10 karakter anu munggaran. Nyaéta, saatos kaayaan munggaran dipicu, verifikasi salajengna henteu dilaksanakeun deui:

Ieu bédana fundamental antara operator | tina fungsi logika excel standar OR (ATAWA), dimana nyusun ulang argumen henteu ngarobih hasilna.

SKUs produk

Dina loba pausahaan, identifiers unik ditugaskeun ka barang sarta jasa - artikel, kode SAP, SKUs, jsb Mun aya logika dina notasi maranéhanana, maranéhna bisa gampang ditarik kaluar tina sagala téks ngagunakeun ungkapan biasa. Salaku conto, upami urang terang yén tulisan urang biasana diwangun ku tilu hurup Inggris ageung, hyphen sareng nomer tilu-angka salajengna, maka:

Logika balik template anu basajan. [AZ] - hartina sagala hurup kapital tina alfabét Latin. The quantifier salajengna 3 {} nyebutkeun yen hal anu penting pikeun urang nu aya persis tilu hurup sapertos. Saatos hyphen, urang ngantosan tilu digit, janten urang tambahkeun dina tungtungna d{3}

Jumlah tunai

Dina cara anu sami sareng paragraf sateuacana, anjeun ogé tiasa narik harga (biaya, PPN ...) tina katerangan barang. Lamun jumlah moneter, contona, dituduhkeun ku hyphen, teras:

corak d kalawan quantifier + maluruh angka nu mana wae nu nepi ka hyphen a, jeung d{2} bakal néangan pennies (dua digit) sanggeus.

Upami anjeun kedah nimba sanés harga, tapi PPN, teras anjeun tiasa nganggo argumen pilihan katilu tina fungsi RegExpExtract kami, anu nangtukeun jumlah ordinal unsur anu bakal diekstrak. Sareng, tangtosna, anjeun tiasa ngagentos fungsina SUBSTITUT (GANTIKAN) dina hasil, hyphen kana pemisah perpuluhan standar sareng tambahkeun minus ganda di awal supados Excel napsirkeun PPN anu kapendak salaku jumlah normal:

Nomer plat mobil

Upami anjeun henteu nyandak kandaraan khusus, gandeng sareng motor sanés, maka nomer mobil standar dirobih dumasar kana prinsip "hurup - tilu angka - dua hurup - kode wilayah". Leuwih ti éta, kode wewengkon bisa jadi 2- atawa 3-angka, sarta ngan maranéhanana anu sarupa dina penampilan alfabét Latin dipaké salaku hurup. Ku kituna, éksprési biasa di handap ieu bakal nulungan urang nimba angka tina téks:

waktu

Pikeun nimba waktos dina format HH:MM, ekspresi biasa di handap ieu cocog:

Saatos sempalan titik [0-5]d, sakumaha anu kasebut gampang angka kaluar, susunan sagala angka dina rentang 00-59. Sateuacan titik dua dina kurung, dua pola dianggo, dipisahkeun ku OR logis (pipa):

• [0-1]d - angka naon waé dina rentang 00-19
• 2[0-3] - angka naon waé dina rentang 20-23

Pikeun hasil anu dicandak, anjeun ogé tiasa nerapkeun fungsi Excel standar TIME (TIM)pikeun ngarobah kana format waktos anu kaharti ku program tur cocog pikeun itungan salajengna.

Pariksa sandi

Anggap urang kedah pariksa daptar kecap akses invented by pamaké pikeun correctness. Numutkeun aturan urang, kecap akses ngan bisa ngandung hurup Inggris (hurup leutik atawa uppercase) jeung angka. Spasi, garis handap sareng tanda baca sanés teu diidinan.

Mariksa tiasa diatur nganggo éksprési biasa anu sederhana di handap ieu:

Nyatana, kalayan pola sapertos kitu kami meryogikeun yén antara awal (^) jeung tungtung ($) dina téks urang ngan aya karakter ti set dibikeun dina kurung kuadrat. Upami anjeun ogé kedah pariksa panjang kecap konci (contona, sahenteuna 6 karakter), maka kuantifier + bisa diganti ku interval "genep atawa leuwih" dina formulir {6,}:

Kota ti alamatna

Anggap urang kedah narik kota tina bar alamat. Program biasa bakal ngabantosan, ékstrak téks tina "g." kana koma salajengna:

Hayu urang nempo leuwih deukeut pola ieu.

Upami anjeun parantos maca téks di luhur, maka anjeun parantos ngartos yén sababaraha karakter dina ekspresi biasa (periode, tanda bintang, tanda dolar, jsb) ngagaduhan harti khusus. Upami anjeun kedah milarian karakter-karakter ieu nyalira, maka aranjeunna diawalan ku backslash (kadangkala disebut taméng). Ku alatan éta, nalika néangan fragmen "g". urang kudu nulis dina ekspresi biasa Bapa. lamun urang néangan hiji tambah, lajeng + jsb

Dua karakter salajengna dina citakan urang, titik jeung tanda asterisk quantifier, nangtung pikeun angka nu mana wae nu karakter, ie naon ngaran kota.

Aya koma di tungtung citakan, sabab urang milarian téks tina "g". kana koma. Tapi tiasa aya sababaraha koma dina téks, leres? Henteu ngan sanggeus kota, tapi ogé sanggeus jalan, imah, jeung saterusna. Éta pikeun tanda tanya. Tanpa éta, éksprési biasa urang bakal narik kaluar string pangpanjangna mungkin:

Dina watesan éksprési biasa, pola sapertos "rakus". Pikeun ngabenerkeun kaayaan, hiji tanda tanya diperlukeun - ngajadikeun quantifier nu sanggeus éta nangtung "pelit" - sarta query kami nyokot téks ngan nepi ka counter koma munggaran sanggeus "g.":

Ngaran koropak tina jalur lengkep

Kaayaan anu umum pisan nyaéta nimba nami file tina jalur lengkep. Ekspresi biasa anu sederhana tina bentuk bakal ngabantosan di dieu:

Trik di dieu nyaeta yén pilarian, kanyataanna, lumangsung dina arah nu lalawanan - ti tungtung nepi ka awal, sabab dina tungtung template urang téh. $, sareng urang milarian sadayana sateuacanna dugi ka backslash munggaran ti katuhu. The backslash kabur, kawas titik dina conto saméméhna.

PS

"Nuju tungtungna" Abdi hoyong netelakeun yén sakabéh di luhur mangrupakeun bagian leutik tina sagala kemungkinan nu ungkapan biasa nyadiakeun. Aya loba karakter husus jeung aturan pikeun pamakéan maranéhanana, sarta sakabéh buku geus ditulis dina topik ieu (Kuring nyarankeun sahanteuna hiji ieu pikeun ngamimitian). Dina hiji cara, nulis ekspresi biasa ampir hiji seni. Ampir sok, ekspresi biasa invented bisa ningkat atawa supplemented, sahingga leuwih elegan atawa bisa digawekeun ku rentang lega data input.

Pikeun nganalisa sareng nga-parse éksprési biasa batur atanapi debug anjeun nyalira, aya sababaraha jasa online anu merenah: RegEx101, RegExr sarta beuki

Hanjakalna, henteu sadayana fitur éksprési biasa klasik dirojong dina VBA (contona, milarian sabalikna atanapi kelas POSIX) sareng tiasa dianggo sareng Cyrillic, tapi kuring nyangka yén naon anu aya cukup pikeun pertama kalina pikeun anjeun.

Upami anjeun henteu énggal dina topik éta, sareng anjeun gaduh hal anu dibagikeun, tinggalkeun ungkapan biasa anu mangpaat nalika damel di Excel dina koméntar di handap ieu. Hiji pikiran téh alus, tapi dua sapatu téh sapasang!

• Ngaganti sareng ngabersihan téks nganggo fungsi SUBSTITUTE
• Milarian sareng nyorot karakter Latin dina téks
• Milarian téks anu pangcaketna (Ivanov = Ivonov = Ivanof, jsb.)