Pag-parse sa teksto nga adunay regular nga mga ekspresyon (RegExp) sa Excel

Usa sa labing makagugol sa oras ug makapahigawad nga mga buluhaton kung nagtrabaho kauban ang teksto sa Excel mao ang pag-parse - pag-parse sa alphanumeric nga "porridge" ngadto sa mga sangkap ug pagkuha sa mga tipik nga atong gikinahanglan gikan niini. Pananglitan:

• pagkuha sa zip code gikan sa adres (maayo kung ang zip code kanunay sa sinugdanan, apan unsa man kung dili?)
• pagpangita sa numero ug petsa sa invoice gikan sa paghulagway sa pagbayad sa bank statement
• pagkuha sa TIN gikan sa motley nga mga paghulagway sa mga kompanya sa lista sa mga katugbang
• pangitaa ang numero sa awto o numero sa artikulo sa paghulagway, ug uban pa.
  

Kasagaran sa ingon nga mga kaso, pagkahuman sa tunga sa oras nga masulub-on nga pagpili sa teksto nga mano-mano, ang mga hunahuna nagsugod sa pag-abut sa hunahuna sa usa ka paagi aron ma-automate kini nga proseso (labi na kung adunay daghang datos). Adunay daghang mga solusyon ug adunay lainlaing ang-ang sa pagkakomplikado-kaepektibo:

• Paggamit built-in nga Excel text functions sa pagpangita-cut-glue nga teksto: LEVSIMV (SA WALA), TUO (TINUOD), PSTR (tunga), STsEPIT (CONCATENATE) ug ang mga analogue niini, KOMBINTO (JOINTEXT), LABAW (EKSAKTO) ug uban pa Kini nga pamaagi maayo kung adunay klaro nga lohika sa teksto (pananglitan, ang indeks kanunay sa sinugdanan sa adres). Kung dili, ang mga pormula mahimong labi ka komplikado ug, usahay, moabut pa sa mga pormula sa array, nga labi nga nagpahinay sa dagkong mga lamesa.
• Pinaagi sa paggamit sa sama sa operator sa pagkaparehas sa teksto gikan sa Visual Basic nga giputos sa usa ka custom nga macro function. Kini nagtugot kanimo sa pagpatuman sa usa ka mas flexible nga pagpangita gamit ang wildcard nga mga karakter (*, #,?, ug uban pa) Ikasubo, kini nga himan dili makuha ang gitinguha nga substring gikan sa teksto - susiha lamang kung kini anaa niini.

Dugang pa sa ibabaw, adunay lain nga pamaagi nga nahibal-an kaayo sa pig-ot nga mga lingin sa mga propesyonal nga programmer, web developer ug uban pang mga teknolohiya - kini mao ang regular nga pagpahayag (Regular nga Ekspresyon = RegExp = “regexps” = “regular”). Sa yanong pagkasulti, Ang RegExp usa ka pinulongan diin ang mga espesyal nga karakter ug mga lagda gigamit sa pagpangita sa gikinahanglan nga mga substring sa teksto, pagkuha niini o pag-ilis niini sa ubang teksto.. Ang mga regular nga ekspresyon usa ka gamhanan kaayo ug matahum nga himan nga milabaw sa tanan nga mga paagi sa pagtrabaho sa teksto pinaagi sa usa ka han-ay sa kadako. Daghang mga programming language (C#, PHP, Perl, JavaScript…) ug mga text editor (Word, Notepad++…) nagsuporta sa regular nga mga ekspresyon.

Ang Microsoft Excel sa walay palad walay suporta sa RegExp gikan sa kahon, apan kini dali nga masulbad sa VBA. Ablihi ang Visual Basic Editor gikan sa tab developer (Developer) o keyboard shortcut alt+F11. Dayon isulod ang bag-ong module pinaagi sa menu Isulod – Module ug kopyaha ang teksto sa mosunod nga macro function didto:

Public Function RegExpExtract(Text As String, Pattern As String, Optional Item As Integer = 1) As String On Error GoTo ErrHandl Set regex = CreateObject("VBScript.RegExp") regex.Pattern = Pattern regex.Global = Tinuod Kung regex.Test (Text) Dayon Set matches = regex.Execute(Text) RegExpExtract = matches.Item(Item - 1) Exit Function End If ErrHandl: RegExpExtract = CVErr(xlErrValue) End Function  

Mahimo na namon nga tapuson ang Visual Basic Editor ug mobalik sa Excel aron sulayan ang among bag-ong bahin. Ang syntax niini mao ang mosunod:

=RegExpExtract( Txt ; Sumbanan ; Butang )

diin

• txt - usa ka cell nga adunay teksto nga among gisusi ug diin gusto namon makuha ang substring nga among gikinahanglan
• sumbanan – maskara (pattern) para sa pagpangita sa substring
• item – ang han-ay nga numero sa substring nga makuha, kung adunay daghan niini (kung wala gipiho, ang una nga panghitabo gipakita)

Ang labing makaiikag nga butang dinhi, siyempre, mao ang Pattern - usa ka template nga hugpong sa mga espesyal nga karakter "sa lengguwahe" sa RegExp, nga nagtino kung unsa gyud ug kung diin naton gusto pangitaon. Ania ang labing sukaranan aron makasugod ka:

Kung nangita kami usa ka piho nga gidaghanon sa mga karakter, pananglitan, usa ka unom ka digit nga postal code o tanan nga tulo-ka-letra nga mga code sa produkto, nan moabut kami sa pagluwas mga kwentador or mga kwentador mga espesyal nga ekspresyon nga nagtino sa gidaghanon sa mga karakter nga pangitaon. Ang mga quantifier gipadapat sa karakter nga nag-una niini:

Karon magpadayon kita sa labing makapaikag nga bahin - usa ka pagtuki sa aplikasyon sa gibuhat nga function ug kung unsa ang atong nakat-unan bahin sa mga sumbanan sa praktikal nga mga panig-ingnan gikan sa kinabuhi.

Pagkuha sa mga numero gikan sa teksto

Sa pagsugod, analisahon naton ang usa ka yano nga kaso - kinahanglan nimo nga makuha ang una nga numero gikan sa alphanumeric nga lugaw, pananglitan, ang gahum sa dili mabalda nga mga suplay sa kuryente gikan sa lista sa presyo:

Ang lohika sa luyo sa regular nga ekspresyon yano ra: d nagpasabot sa bisan unsa nga digit, ug ang quantifier + nag-ingon nga ang ilang gidaghanon kinahanglan nga usa o daghan pa. Ang doble nga minus sa atubangan sa function gikinahanglan aron "sa langaw" makombertir ang gikuha nga mga karakter sa usa ka tibuuk nga numero gikan sa numero-as-teksto.

Mga pagpasabot

Sa una nga pagtan-aw, ang tanan yano ra dinhi - nangita kami og eksakto nga unom ka numero sa usa ka laray. Gigamit namon ang usa ka espesyal nga karakter d para sa digit ug quantifier 6 {} alang sa gidaghanon sa mga karakter:

Bisan pa, posible ang usa ka sitwasyon kung, sa wala sa indeks sa linya, adunay lain nga dako nga hugpong sa mga numero sa usa ka laray (numero sa telepono, TIN, bank account, ug uban pa) Unya ang among regular nga panahon mobira sa una nga 6 mga digit gikan niini, ie dili molihok sa husto:

Aron mapugngan kini nga mahitabo, kinahanglan namon nga magdugang usa ka modifier sa palibot sa mga sulud sa among regular nga ekspresyon b nagpasabot sa katapusan sa usa ka pulong. Kini magpatin-aw sa Excel nga ang tipik (index) nga atong gikinahanglan kinahanglan nga usa ka bulag nga pulong, ug dili bahin sa laing tipik (numero sa telepono):

Phone

Ang problema sa pagpangita og numero sa telepono diha sa teksto mao nga adunay daghan kaayong mga kapilian sa pagsulat sa mga numero - nga adunay ug walay hyphen, pinaagi sa mga luna, adunay o walay code sa rehiyon sa mga bracket, ug uban pa. Busa, sa akong opinyon, mas sayon limpyo una kining tanan nga mga karakter gikan sa gigikanan nga teksto gamit ang daghang mga nested function SUBSTITUT ( SUBSTITUT )mao nga kini motapot sa tingub ngadto sa usa ka bug-os nga, ug unya uban sa usa ka primitive regular d{11} kuhaa ang 11 ka numero sa usa ka laray:

ITN

Kini usa ka gamay nga mas komplikado dinhi, tungod kay ang TIN (sa Atong Nasud) mahimong 10-digit (para sa legal nga mga entidad) o 12-digit (para sa mga indibidwal). Kung wala ka makit-an nga sayup labi na, nan posible nga matagbaw sa regular d{10,12}, apan, sa higpit nga pagsulti, kini mobira sa tanan nga mga numero gikan sa 10 ngadto sa 12 ka mga karakter, ie ug sayop nga pagsulod sa 11 ka numero. Mas husto ang paggamit sa duha ka mga pattern nga konektado sa usa ka lohikal O operator | (vertikal nga bar):

Palihug timan-i nga sa pangutana kita una nga mangita alang sa 12-bit nga mga numero, ug unya alang sa 10-bit nga mga numero. Kung atong isulat ang atong regular nga ekspresyon sa laing paagi, nan kini mobira alang sa tanan, bisan ang taas nga 12-bit nga mga TIN, ang unang 10 ka karakter lamang. Kana mao, human ma-trigger ang unang kondisyon, ang dugang nga pag-verify wala na ipahigayon:

Kini ang sukaranan nga kalainan tali sa operator | gikan sa usa ka standard excel logic function OR (O), diin ang paghan-ay pag-usab sa mga argumento dili makausab sa resulta.

Mga SKU sa Produkto

Sa daghang mga kompanya, ang mga talagsaon nga mga identifier gi-assign sa mga butang ug serbisyo - mga artikulo, SAP code, SKU, ug uban pa. Kung adunay lohika sa ilang notasyon, nan kini dali nga makuha gikan sa bisan unsang teksto gamit ang regular nga mga ekspresyon. Pananglitan, kung nahibal-an namon nga ang among mga artikulo kanunay adunay tulo nga dagkong mga letra sa English, usa ka hyphen ug usa ka sunod-sunod nga tulo-ka-digit nga numero, nan:

Ang lohika sa luyo sa template yano ra. [AZ] – nagpasabot sa bisan unsang dagkong mga letra sa Latin nga alpabeto. Ang sunod nga quantifier 3 {} nag-ingon nga importante alang kanato nga adunay eksaktong tulo ka mga sulat. Human sa hyphen, naghulat kami og tulo ka digit, mao nga among idugang sa katapusan d{3}

Mga kantidad sa salapi

Sa parehas nga paagi sa miaging parapo, mahimo usab nimo makuha ang mga presyo (gasto, VAT ...) gikan sa paghulagway sa mga butang. Kung ang kantidad sa kwarta, pananglitan, gipakita gamit ang hyphen, nan:

sumbanan d uban sa quantifier + nangita sa bisan unsang numero hangtod sa hyphen, ug d{2} mangita alang sa mga sentimos (duha ka numero) pagkahuman.

Kung kinahanglan nimo nga kuhaon dili mga presyo, apan VAT, nan mahimo nimong gamiton ang ikatulo nga opsyonal nga argumento sa among RegExpExtract function, nga nagtino sa ordinal nga numero sa elemento nga makuha. Ug, siyempre, mahimo nimong ilisan ang function SUBSTITUT ( SUBSTITUT ) sa mga resulta, hyphen sa standard decimal separator ug idugang ang double minus sa sinugdanan aron ang Excel maghubad sa nakit-an nga VAT isip normal nga numero:

Mga plate number sa sakyanan

Kung dili ka magdala og espesyal nga mga sakyanan, trailer ug uban pang mga motorsiklo, nan ang standard nga numero sa sakyanan gi-parse sumala sa prinsipyo nga "sulat - tulo ka numero - duha ka letra - code sa rehiyon". Dugang pa, ang kodigo sa rehiyon mahimong 2- o 3-digit, ug kadto lamang nga susama sa dagway sa Latin nga alpabeto ang gigamit ingong mga letra. Busa, ang mosunod nga regular nga ekspresyon makatabang kanato sa pagkuha sa mga numero gikan sa teksto:

Oras

Aron makuha ang oras sa HH:MM format, ang mosunod nga regular nga ekspresyon ang angay:

Human sa colon fragment [0-5]d, ingon nga kini sayon ​​nga mahibal-an, nagtakda sa bisan unsang numero sa han-ay nga 00-59. Sa wala pa ang colon sa parentheses, duha ka mga pattern ang nagtrabaho, gibulag sa usa ka lohikal nga O (pipe):

• [0-1]d – bisan unsang numero sa range 00-19
• 2[0-3] – bisan unsang numero sa range 20-23

Sa resulta nga nakuha, mahimo nimong i-apply ang standard nga function sa Excel PANAHON (TEAM)aron mabag-o kini sa usa ka format sa oras nga masabtan sa programa ug angay alang sa dugang nga mga kalkulasyon.

Pagsusi sa password

Ibutang ta nga kinahanglan natong susihon ang lista sa mga password nga giimbento sa mga tiggamit alang sa husto. Sumala sa among mga lagda, ang mga password mahimo ra nga adunay sulud nga English nga mga letra (gamay o uppercase) ug mga numero. Ang mga luna, underscore ug uban pang mga punctuation mark dili tugutan.

Ang pagsusi mahimong organisado gamit ang mosunod nga yano nga regular nga ekspresyon:

Sa tinuud, sa ingon nga sumbanan kinahanglan namon nga tali sa sinugdanan (^) ug katapusan ($) sa among teksto adunay mga karakter lamang gikan sa set nga gihatag sa square bracket. Kung kinahanglan nimo nga susihon ang gitas-on sa password (pananglitan, labing menos 6 nga mga karakter), unya ang quantifier + mahimong pulihan sa interval nga "unom o labaw pa" sa porma {6,}:

Lungsod gikan sa adres

Ingnon ta nga kinahanglan natong ibira ang siyudad gikan sa address bar. Ang regular nga programa makatabang, pagkuha sa teksto gikan sa "g." sa sunod nga comma:

Atong tan-awon pag-ayo kini nga sumbanan.

Kung nabasa na nimo ang teksto sa ibabaw, nan nasabtan na nimo nga ang pipila ka mga karakter sa regular nga mga ekspresyon (mga panahon, asterisk, mga timaan sa dolyar, ug uban pa) adunay espesyal nga kahulugan. Kung kinahanglan nimo pangitaon kini nga mga karakter sa ilang kaugalingon, nan kini giunhan sa usa ka backslash (usahay gitawag panagang). Busa, sa pagpangita sa tipik nga "g." kinahanglan natong isulat sa regular nga ekspresyon Mr. kung nangita ta ug plus, unya + ug uban pa

Ang sunod nga duha ka mga karakter sa among template, ang tulbok ug ang quantifier asterisk, nagbarug alang sa bisan unsang gidaghanon sa bisan unsang mga karakter, ie bisan unsang ngalan sa lungsod.

Adunay usa ka comma sa katapusan sa template, tungod kay nangita kami og teksto gikan sa "g." sa usa ka comma. Apan mahimong adunay daghang mga koma sa teksto, di ba? Dili lang pagkahuman sa syudad, apan pagkahuman usab sa kadalanan, mga balay, ug uban pa. Hain niini ang among hangyo mahunong? Mao kana ang marka sa pangutana. Kon wala kini, ang atong regular nga ekspresyon mobira sa pinakataas nga string nga posible:

Sa termino sa regular nga mga ekspresyon, ang ingon nga sumbanan mao ang "kahakog". Aron matul-id ang sitwasyon, gikinahanglan ang usa ka marka sa pangutana - kini naghimo sa quantifier nga human niana kini nagbarug nga "kuripot" - ug ang among pangutana magdala sa teksto lamang ngadto sa unang counter comma human sa "g.":

Filename gikan sa bug-os nga dalan

Ang laing komon kaayo nga sitwasyon mao ang pagkuha sa ngalan sa file gikan sa tibuok nga dalan. Ang usa ka yano nga regular nga ekspresyon sa porma makatabang dinhi:

Ang lansis dinhi mao nga ang pagpangita, sa tinuud, mahitabo sa atbang nga direksyon - gikan sa katapusan hangtod sa sinugdanan, tungod kay sa katapusan sa among template mao ang $, ug among gipangita ang tanan sa wala pa kini hangtod sa una nga backslash gikan sa tuo. Ang backslash nakalingkawas, sama sa tulbok sa miaging pananglitan.

PS

"Sa katapusan" Gusto nakong ipatin-aw nga ang tanan sa ibabaw usa ka gamay nga bahin sa tanan nga mga posibilidad nga gihatag sa mga regular nga ekspresyon. Adunay daghang mga espesyal nga karakter ug mga lagda alang sa ilang paggamit, ug ang tibuuk nga mga libro gisulat bahin niini nga hilisgutan (girekomenda ko labing menos kini nga usa alang sa pagsugod). Sa usa ka paagi, ang pagsulat sa regular nga mga ekspresyon halos usa ka arte. Halos kanunay, ang usa ka imbento nga regular nga ekspresyon mahimong mapauswag o madugangan, nga maghimo niini nga labi ka elegante o makahimo sa pagtrabaho sa usa ka mas lapad nga sakup sa data sa pag-input.

Aron ma-analisar ug ma-parse ang mga regular nga ekspresyon sa ubang mga tawo o i-debug ang imong kaugalingon, adunay daghang kombenyente nga serbisyo sa online: RegEx101, RegExr ug labaw pa

Ikasubo, dili tanan nga mga bahin sa klasiko nga regular nga mga ekspresyon gisuportahan sa VBA (pananglitan, reverse search o POSIX nga mga klase) ug mahimo nga magtrabaho kauban ang Cyrillic, apan sa akong hunahuna kung unsa ang naa sa una nga higayon aron mapahimut-an ka.

Kung dili ka bag-o sa hilisgutan, ug adunay ka ipaambit, ibilin ang mga regular nga ekspresyon nga mapuslanon kung nagtrabaho sa Excel sa mga komento sa ubos. Ang usa ka hunahuna maayo, apan ang duha ka botas usa ka parisan!

• Pag-ilis ug paglimpyo sa teksto gamit ang SUBSTITUTE function
• Pagpangita ug pag-highlight sa Latin nga mga karakter sa teksto
• Pangitaa ang labing duol nga susama nga teksto (Ivanov = Ivonov = Ivanof, ug uban pa)