Nilalaman
Ang isa sa mga pinaka-nakakaubos ng oras at nakakabigo na mga gawain kapag nagtatrabaho sa teksto sa Excel ay pag-parse – pag-parse ng alphanumeric na "sinigang" sa mga bahagi at pagkuha ng mga fragment na kailangan namin mula dito. Halimbawa:
- pag-extract ng zip code mula sa address (mabuti kung ang zip code ay palaging nasa simula, ngunit paano kung hindi?)
- paghahanap ng numero at petsa ng invoice mula sa paglalarawan ng pagbabayad sa bank statement
- pagkuha ng TIN mula sa mga motley na paglalarawan ng mga kumpanya sa listahan ng mga katapat
- maghanap ng numero ng kotse o numero ng artikulo sa paglalarawan, atbp.
Kadalasan sa ganitong mga kaso, pagkatapos ng kalahating oras ng nakakapagod na pagpili sa teksto nang manu-mano, ang mga saloobin ay nagsisimulang pumasok sa isip kahit papaano upang i-automate ang prosesong ito (lalo na kung mayroong maraming data). Mayroong ilang mga solusyon at may iba't ibang antas ng pagiging kumplikado-kahusayan:
- paggamit built-in na Excel text function para maghanap-cut-glue text: LEVSIMV (KALIWA), KARAPATAN (TAMA), PSTR (kalagitnaan), STsEPIT (CONCATENATE) at ang mga analogue nito, Pagsamahin (JOINTEXT), Eksakto (EXACT) atbp. Ang pamamaraang ito ay mabuti kung mayroong malinaw na lohika sa teksto (halimbawa, ang index ay palaging nasa simula ng address). Kung hindi, ang mga formula ay nagiging mas kumplikado at, kung minsan, ito ay dumating pa sa array formula, na lubhang nagpapabagal sa malalaking talahanayan.
- paggamit tulad ng operator ng pagkakatulad ng teksto mula sa Visual Basic na nakabalot sa isang custom na macro function. Binibigyang-daan ka nitong magpatupad ng mas flexible na paghahanap gamit ang mga wildcard na character (*, #,?, atbp.) Sa kasamaang-palad, hindi ma-extract ng tool na ito ang gustong substring mula sa text – tingnan lamang kung ito ay nakapaloob dito.
Bilang karagdagan sa itaas, mayroong isa pang diskarte na kilala sa makitid na mga bilog ng mga propesyonal na programmer, web developer at iba pang mga teknolohiya - ito ay regular na mga expression (Regular Expressions = RegExp = "regexps" = "regulars"). Sa madaling salita, Ang RegExp ay isang wika kung saan ginagamit ang mga espesyal na character at panuntunan upang hanapin ang mga kinakailangang substring sa text, i-extract ang mga ito o palitan ng ibang text.. Ang mga regular na expression ay isang napakalakas at magandang tool na lumalampas sa lahat ng iba pang paraan ng pagtatrabaho sa teksto sa pamamagitan ng isang order ng magnitude. Maraming mga programming language (C#, PHP, Perl, JavaScript…) at mga text editor (Word, Notepad++…) ang sumusuporta sa mga regular na expression.
Sa kasamaang-palad, walang suporta sa RegExp ang Microsoft Excel, ngunit madali itong maayos sa VBA. Buksan ang Visual Basic Editor mula sa tab developer (Developer) o keyboard shortcut Alt+F11. Pagkatapos ay ipasok ang bagong module sa pamamagitan ng menu Ipasok – Module at kopyahin ang teksto ng sumusunod na macro function doon:
Pampublikong Function RegExpExtract(Text Bilang String, Pattern Bilang String, Opsyonal na Item Bilang Integer = 1) Bilang String Sa Error GoTo ErrHandl Set regex = CreateObject("VBScript.RegExp") regex.Pattern = Pattern regex.Global = True If regex.Test (Text) Pagkatapos Itakda ang mga tugma = regex.Execute(Text) RegExpExtract = matches.Item(Item - 1) Exit Function End If ErrHandl: RegExpExtract = CVErr(xlErrValue) End Function Maaari na naming isara ang Visual Basic Editor at bumalik sa Excel para subukan ang aming bagong feature. Ang syntax nito ay ang sumusunod:
=RegExpExtract( Txt ; Pattern ; Item )
saan
- txt – isang cell na may text na aming sinusuri at kung saan gusto naming kunin ang substring na kailangan namin
- huwaran – mask (pattern) para sa paghahanap ng substring
- Bagay – ang sequence number ng substring na kukunin, kung marami sa kanila (kung hindi tinukoy, ang unang pangyayari ay ipapakita)
Ang pinaka-kagiliw-giliw na bagay dito, siyempre, ay Pattern - isang template na string ng mga espesyal na character "sa wika" ng RegExp, na tumutukoy kung ano ang eksaktong at kung saan namin gustong hanapin. Narito ang mga pinakapangunahing mga para makapagsimula ka:
| huwaran | paglalarawan |
| . | Ang pinakasimpleng ay isang tuldok. Tumutugma ito sa anumang karakter sa pattern sa tinukoy na posisyon. |
| s | Anumang character na mukhang isang espasyo (space, tab, o line break). |
| S | Isang anti-variant ng nakaraang pattern, ibig sabihin, anumang hindi whitespace na character. |
| d | Kahit anong numero |
| D | Isang anti-variant ng nauna, ibig sabihin, anumang HINDI digit |
| w | Anumang Latin na character (AZ), digit, o underscore |
| W | Isang anti-variant ng nauna, ibig sabihin, hindi Latin, hindi isang numero at hindi isang underscore. |
| [character] | Sa mga square bracket, maaari mong tukuyin ang isa o higit pang mga character na pinapayagan sa tinukoy na posisyon sa teksto. Halimbawa Sining ay tutugma sa alinman sa mga salita: mesa or upuan. Hindi ka rin maaaring magbilang ng mga character, ngunit itakda ang mga ito bilang isang hanay na pinaghihiwalay ng isang gitling, ibig sabihin, sa halip na [ABDCDEF] magsulat [AF]. o sa halip [4567] Ipakilala [-4 7]. Halimbawa, upang italaga ang lahat ng mga character na Cyrillic, maaari mong gamitin ang template [a-yaA-YayoYo]. |
| [^character] | Kung pagkatapos ng pambungad na square bracket ay idagdag ang simbolo na "lid" ^, pagkatapos ay makukuha ng set ang kabaligtaran na kahulugan - sa tinukoy na posisyon sa teksto, lahat ng mga character ay papayagan, maliban sa mga nakalista. Oo, template [^ЖМ]ut hahanapin Landas or Sangkap or Kalimutan, Ngunit hindi Katakot-takot or tapang, hal. |
| | | Boolean operator OR (OR) upang suriin ang alinman sa mga tinukoy na pamantayan. Halimbawa (kasamaHuwebes|skahit|invoice) hahanapin ang teksto para sa alinman sa mga tinukoy na salita. Karaniwan, ang isang hanay ng mga pagpipilian ay nakapaloob sa mga panaklong. |
| ^ | Simula ng linya |
| $ | Dulo ng linya |
| b | Katapusan ng salita |
Kung naghahanap kami ng isang tiyak na bilang ng mga character, halimbawa, isang anim na digit na postal code o lahat ng tatlong-titik na code ng produkto, pagkatapos ay sumagip kami dami or dami ay mga espesyal na expression na tumutukoy sa bilang ng mga character na hahanapin. Inilapat ang mga quantifier sa karakter na nauuna rito:
| Quantor | paglalarawan |
| ? | Zero o isang pangyayari. Halimbawa .? ay mangangahulugan ng alinmang karakter o kawalan nito. |
| + | Isa o higit pang mga entry. Halimbawa d+ nangangahulugang anumang bilang ng mga digit (ibig sabihin, anumang numero sa pagitan ng 0 at infinity). |
| * | Zero o higit pang mga pangyayari, ibig sabihin, anumang dami. Kaya s* nangangahulugang anumang bilang ng mga puwang o walang mga puwang. |
| {numero} or {bilang1,bilang2} | Kung kailangan mong tukuyin ang isang mahigpit na tinukoy na bilang ng mga paglitaw, pagkatapos ay tinukoy ito sa mga kulot na brace. Halimbawa d{6} nangangahulugang mahigpit na anim na numero, at ang pattern s{2,5} – dalawa hanggang limang puwang |
Ngayon ay lumipat tayo sa pinaka-kagiliw-giliw na bahagi - isang pagsusuri ng aplikasyon ng nilikha na function at kung ano ang natutunan natin tungkol sa mga pattern sa mga praktikal na halimbawa mula sa buhay.
Pagkuha ng mga numero mula sa teksto
Upang magsimula, pag-aralan natin ang isang simpleng kaso – kailangan mong kunin ang unang numero mula sa alphanumeric na sinigang, halimbawa, ang kapangyarihan ng mga hindi maaabala na power supply mula sa listahan ng presyo:
Ang lohika sa likod ng regular na expression ay simple: d nangangahulugang anumang digit, at ang quantifier + nagsasabing ang kanilang numero ay dapat isa o higit pa. Ang double minus sa harap ng function ay kailangan upang "on the fly" i-convert ang mga na-extract na character sa isang buong numero mula sa number-as-text.
Postcode
Sa unang tingin, ang lahat ay simple dito - naghahanap kami ng eksaktong anim na numero sa isang hilera. Gumagamit kami ng isang espesyal na karakter d para sa digit at quantifier 6 {} para sa bilang ng mga character:
Gayunpaman, posible ang isang sitwasyon kapag, sa kaliwa ng index sa linya, mayroong isa pang malaking hanay ng mga numero sa isang hilera (numero ng telepono, TIN, bank account, atbp.) Pagkatapos ay bubunutin ng aming regular na season ang unang 6 mga digit mula dito, ibig sabihin, hindi gagana nang tama:
Upang maiwasang mangyari ito, kailangan naming magdagdag ng modifier sa paligid ng mga gilid ng aming regular na expression b nagpapahiwatig ng pagtatapos ng isang salita. Ito ay magiging malinaw sa Excel na ang fragment (index) na kailangan namin ay dapat na isang hiwalay na salita, at hindi bahagi ng isa pang fragment (numero ng telepono):
telepono
Ang problema sa paghahanap ng numero ng telepono sa text ay napakaraming opsyon para sa pagsusulat ng mga numero – may at walang gitling, sa pamamagitan ng mga puwang, mayroon o walang code ng rehiyon sa mga bracket, atbp. Samakatuwid, sa aking opinyon, mas madaling linisin muna ang lahat ng mga character na ito mula sa pinagmulang teksto gamit ang ilang mga nested function SUBSTITUTE (PALIT)upang ito ay magkadikit sa isang solong kabuuan, at pagkatapos ay may isang primitive na regular d{11} bunutin ang 11 digit sa isang hilera:
ITN
Ito ay medyo mas kumplikado dito, dahil ang TIN (sa Ating Bansa) ay maaaring maging 10-digit (para sa mga legal na entity) o 12-digit (para sa mga indibidwal). Kung hindi ka nakakahanap ng kasalanan lalo na, kung gayon posible na masiyahan sa regular d{10,12}, ngunit, sa mahigpit na pagsasalita, bubunutin nito ang lahat ng numero mula 10 hanggang 12 character, ibig sabihin, at maling naipasok ang 11 digit. Mas tama na gumamit ng dalawang pattern na konektado ng isang lohikal na O operator | (vertical bar):
Pakitandaan na sa query, hinahanap muna namin ang mga 12-bit na numero, at pagkatapos ay para lamang sa 10-bit na mga numero. Kung isusulat namin ang aming regular na expression sa kabaligtaran, pagkatapos ay lalabas ito para sa lahat, kahit na mahahabang 12-bit na TIN, ang unang 10 character lamang. Ibig sabihin, pagkatapos ma-trigger ang unang kundisyon, hindi na isasagawa ang karagdagang pag-verify:
Ito ang pangunahing pagkakaiba sa pagitan ng operator | mula sa isang karaniwang excel logic function OR (OR), kung saan ang muling pagsasaayos ng mga argumento ay hindi nagbabago sa resulta.
Mga SKU ng Produkto
Sa maraming kumpanya, ang mga natatanging identifier ay itinalaga sa mga produkto at serbisyo – mga artikulo, SAP code, SKU, atbp. Kung may lohika sa kanilang notasyon, madali silang ma-pull out sa anumang text gamit ang mga regular na expression. Halimbawa, kung alam namin na ang aming mga artikulo ay palaging binubuo ng tatlong malalaking titik sa Ingles, isang gitling at isang kasunod na tatlong-digit na numero, kung gayon:
Ang lohika sa likod ng template ay simple. [AZ] – nangangahulugang anumang malalaking titik ng alpabetong Latin. Ang susunod na quantifier 3 {} ay nagsasabi na ito ay mahalaga para sa amin na mayroong eksaktong tatlong ganoong mga titik. Pagkatapos ng hyphen, naghihintay kami ng tatlong digit, kaya idinagdag namin sa dulo d{3}
Mga halaga ng pera
Sa katulad na paraan sa nakaraang talata, maaari mo ring ilabas ang mga presyo (mga gastos, VAT ...) mula sa paglalarawan ng mga kalakal. Kung ang mga halaga ng pera, halimbawa, ay ipinahiwatig ng isang gitling, kung gayon:
huwaran d may quantifier + naghahanap ng anumang numero hanggang sa isang gitling, at d{2} maghahanap ng mga pennies (dalawang digit) pagkatapos.
Kung kailangan mong kunin ang hindi mga presyo, ngunit VAT, maaari mong gamitin ang ikatlong opsyonal na argumento ng aming RegExpExtract function, na tumutukoy sa ordinal na numero ng elementong kukunin. At, siyempre, maaari mong palitan ang function SUBSTITUTE (PALIT) sa mga resulta, gitling sa karaniwang decimal separator at magdagdag ng double minus sa simula upang mabigyang-kahulugan ng Excel ang nahanap na VAT bilang isang normal na numero:
Mga plate number ng sasakyan
Kung hindi ka kukuha ng mga espesyal na sasakyan, trailer at iba pang mga motorsiklo, kung gayon ang karaniwang numero ng kotse ay na-parse ayon sa prinsipyong "titik - tatlong numero - dalawang titik - code ng rehiyon". Bukod dito, ang code ng rehiyon ay maaaring 2- o 3-digit, at tanging ang mga katulad ng hitsura sa alpabetong Latin ang ginagamit bilang mga titik. Kaya, ang sumusunod na regular na expression ay makakatulong sa amin na kunin ang mga numero mula sa teksto:
oras
Upang kunin ang oras sa HH:MM na format, ang sumusunod na regular na expression ay angkop:
Pagkatapos ng colon fragment [0-5]d, dahil madaling malaman, nagtatakda ng anumang numero sa hanay na 00-59. Bago ang colon sa panaklong, gumagana ang dalawang pattern, na pinaghihiwalay ng isang lohikal na OR (pipe):
- [0-1]d – anumang numero sa hanay na 00-19
- 2[0-3] – anumang numero sa hanay na 20-23
Sa resultang nakuha, maaari mong ilapat ang karaniwang Excel function TIME (TEAM)upang i-convert ito sa isang format ng oras na naiintindihan ng programa at angkop para sa karagdagang mga kalkulasyon.
Pagsusuri ng password
Ipagpalagay na kailangan nating suriin ang listahan ng mga password na naimbento ng mga gumagamit para sa kawastuhan. Ayon sa aming mga panuntunan, ang mga password ay maaari lamang maglaman ng mga letrang Ingles (maliit na titik o malaki) at mga numero. Hindi pinapayagan ang mga puwang, underscore at iba pang mga bantas.
Maaaring ayusin ang pagsusuri gamit ang sumusunod na simpleng regular na expression:
Sa katunayan, sa gayong pattern ay hinihiling namin na sa pagitan ng simula (^) at wakas ($) sa aming teksto mayroon lamang mga character mula sa set na ibinigay sa mga square bracket. Kung kailangan mo ring suriin ang haba ng password (halimbawa, hindi bababa sa 6 na character), pagkatapos ay ang quantifier + maaaring mapalitan ng pagitan ng "anim o higit pa" sa anyo {6,}:
Lungsod mula sa address
Sabihin nating kailangan nating hilahin ang lungsod mula sa address bar. Ang regular na programa ay makakatulong, i-extract ang teksto mula sa "g." sa susunod na kuwit:
Tingnan natin ang pattern na ito nang mas malapitan.
Kung nabasa mo na ang teksto sa itaas, naunawaan mo na na ang ilang mga character sa mga regular na expression (mga panahon, asterisk, dollar sign, atbp.) ay may espesyal na kahulugan. Kung kailangan mong hanapin mismo ang mga character na ito, pagkatapos ay mauunahan sila ng backslash (minsan ay tinatawag na shielding). Samakatuwid, kapag naghahanap para sa fragment na "g." kailangan nating magsulat sa regular na ekspresyon G. kung naghahanap tayo ng dagdag, kung gayon + at iba pa
Ang susunod na dalawang character sa aming template, ang tuldok at ang quantifier asterisk, ay kumakatawan sa anumang bilang ng anumang mga character, ibig sabihin, anumang pangalan ng lungsod.
May kuwit sa dulo ng template, dahil naghahanap kami ng text mula sa “g.” sa isang kuwit. Ngunit maaaring mayroong ilang mga kuwit sa teksto, tama ba? Hindi lamang pagkatapos ng lungsod, kundi pati na rin pagkatapos ng kalye, mga bahay, atbp. Alin sa mga ito ang aming kahilingan? Iyan ang gamit ng tandang pananong. Kung wala ito, bubunutin ng aming regular na expression ang pinakamahabang string na posible:
Sa mga tuntunin ng mga regular na expression, ang gayong pattern ay "matakaw". Upang itama ang sitwasyon, kailangan ng tandang pananong – ginagawa nitong “kuripot” ang quantifier pagkatapos nito – at ang aming query ay tumatagal lamang ng text hanggang sa unang counter comma pagkatapos ng “g.”:
Filename mula sa buong path
Ang isa pang pangkaraniwang sitwasyon ay ang pagkuha ng pangalan ng file mula sa buong landas. Ang isang simpleng regular na pagpapahayag ng form ay makakatulong dito:
Ang trick dito ay ang paghahanap, sa katunayan, ay nangyayari sa kabaligtaran na direksyon - mula sa dulo hanggang sa simula, dahil sa dulo ng aming template ay $, at hinahanap namin ang lahat bago ito hanggang sa unang backslash mula sa kanan. Ang backslash ay nakatakas, tulad ng tuldok sa nakaraang halimbawa.
PS
"Sa dulo" Gusto kong linawin na ang lahat ng nasa itaas ay isang maliit na bahagi ng lahat ng mga posibilidad na ibinibigay ng mga regular na expression. Mayroong maraming mga espesyal na character at mga patakaran para sa kanilang paggamit, at ang buong mga libro ay naisulat sa paksang ito (Inirerekumenda ko ang hindi bababa sa isang ito para sa isang panimula). Sa isang paraan, ang pagsulat ng mga regular na expression ay halos isang sining. Halos palaging, ang isang naimbentong regular na expression ay maaaring mapabuti o madagdagan, na ginagawa itong mas elegante o magagawang gumana sa isang mas malawak na hanay ng data ng input.
Upang suriin at i-parse ang mga regular na expression ng ibang tao o i-debug ang iyong sarili, mayroong ilang maginhawang serbisyo sa online: RegEx101, RegExr at iba pa
Sa kasamaang palad, hindi lahat ng mga tampok ng mga klasikong regular na expression ay sinusuportahan sa VBA (halimbawa, reverse search o POSIX na mga klase) at maaaring gumana sa Cyrillic, ngunit sa palagay ko ay sapat na ang mayroon sa unang pagkakataon upang mapasaya ka.
Kung hindi ka bago sa paksa, at mayroon kang ibabahagi, mag-iwan ng mga regular na expression na kapaki-pakinabang kapag nagtatrabaho sa Excel sa mga komento sa ibaba. Ang isang isip ay mabuti, ngunit ang dalawang bota ay isang pares!
- Pagpapalit at paglilinis ng text gamit ang SUBSTITUTE function
- Maghanap at pag-highlight ng mga Latin na character sa teksto
- Maghanap para sa pinakamalapit na katulad na teksto (Ivanov = Ivonov = Ivanof, atbp.)