Parsáil téacs le nathanna rialta (RegExp) in Excel

Is é ceann de na tascanna is mó ama agus frustrachas nuair a bhíonn tú ag obair le téacs in Excel parsáil – an “leite” alfa-uimhriúil a pharsáil ina chomhpháirteanna agus na blúirí a theastaíonn uainn a bhaint aisti. Mar shampla:

• an cód zip a bhaint as an seoladh (tá sé go maith má tá an cód zip i gcónaí ag an tús, ach cad más rud é nach bhfuil sé?)
• uimhir agus dáta an tsonraisc a aimsiú ón gcur síos ar an íocaíocht sa ráiteas bainc
• eastóscadh stáin ó thuairiscí motley ar chuideachtaí i liosta na gcontrapháirtithe
• uimhir cairr nó uimhir ailt a chuardach sa tuairisc, etc.
  

De ghnáth i gcásanna den sórt sin, tar éis leath uair an chloig de piocadh dreary sa téacs de láimh, tosaíonn smaointe ag teacht chun cuimhne ar bhealach éigin chun an próiseas seo a uathoibriú (go háirithe má tá go leor sonraí ann). Tá go leor réitigh ann agus a bhfuil céimeanna éagsúla castachta-éifeachtúlachta acu:

• Úsáid feidhmeanna téacs Excel ionsuite chun téacs cuardaigh-gearrtha-gliú: LEVSIMV (LEFT), CEART (CEART), PSTR (lár), STsEPIT (CONCATENATE) agus a analógacha, COMHDHÁIL (JOINTEXT), EXACT (EXACT) srl. Tá an modh seo go maith má tá loighic shoiléir sa téacs (mar shampla, tá an t-innéacs i gcónaí ag tús an seoladh). Seachas sin, éiríonn na foirmlí i bhfad níos casta agus, uaireanta, tagann sé fiú le foirmlí eagar, rud a mhoillíonn go mór ar tháblaí móra.
• Ag baint úsáide as cosúil le téacs oibreoir cosúlachta ó Visual Basic fillte i bhfeidhm macra saincheaptha. Ligeann sé seo duit cuardach níos solúbtha a chur i bhfeidhm ag baint úsáide as carachtair saoróg (*, #, ?, srl.) Ar an drochuair, ní féidir leis an uirlis seo an tsubstráil atá uait a bhaint as an téacs - seiceáil an bhfuil sé istigh ann.

Chomh maith leis an méid thuas, tá cur chuige eile ar eolas go maith i gciorcail cúng de ríomhchláraitheoirí gairmiúla, forbróirí gréasáin agus teicneoirí eile - is é seo. nathanna rialta (Sloinn Rialta = RegExp = "regexps" = "rialta"). Go simplí, Is teanga é RegExp ina n-úsáidtear carachtair agus rialacha speisialta chun na fotheaghráin riachtanacha a chuardach sa téacs, iad a bhaint amach nó téacs eile a chur ina ionad. Uirlis thar a bheith cumhachtach agus álainn is ea nathanna rialta a sháraíonn gach bealach eile chun oibriú le téacs de réir ord méide. Tacaíonn go leor teangacha ríomhchlárúcháin (C#, PHP, Perl, JavaScript…) agus eagarthóirí téacs (Word, Notepad ++…) le habairtí rialta.

Ar an drochuair níl tacaíocht RegExp ag Microsoft Excel as an mbosca, ach is féidir é seo a shocrú go héasca le VBA. Oscail an Eagarthóir Visual Basic ón gcluaisín forbróir (Forbróir) nó aicearra méarchláir Eile+F11. Ansin cuir isteach an modúl nua tríd an roghchlár Ionsáigh – Modúl agus cóipeáil téacs na macra feidhme seo a leanas ansin:

Feidhm Phoiblí RegExpExtract(Téacs Mar Theaghrán, Patrún Mar Theaghrán, Mír Roghnach Mar Shlánuimhir = 1) Mar Theaghrán Ar Earráid GoTo ErrHandl Set regex = CreateObject("VBScript.RegExp") regex.Pattern = Patrún regex.Global = Fíor Má regex.Test (Téacs) Ansin Socraigh meaitseanna = regex.Execute(Text) RegExpExtract = matches.Item(Mír - 1) Deireadh Feidhm Scoir Má ErrHandl: RegExpExtract = Feidhm Deiridh CVErr(xlErrValue)  

Is féidir linn an Eagarthóir Visual Basic a dhúnadh anois agus filleadh ar Excel chun triail a bhaint as ár ngné nua. Seo a leanas a chomhréir:

=RegExpExtract( Txt; Patrún; Mír )

Sa chás go

• txt – cill leis an téacs atá á sheiceáil againn agus a dteastaíonn uainn an fotheaghrán a theastaíonn uainn a bhaint as
• patrún – masc (patrún) le haghaidh cuardach fotheaghrán
• Mír – seicheamh uimhir an fhotheaghráin atá le baint, má tá roinnt díobh ann (mura bhfuil sé sonraithe, taispeántar an chéad teagmhas)

Is é an rud is suimiúla anseo, ar ndóigh, ná Patrún – teaghrán teimpléad de charachtair speisialta “sa teanga” de RegExp, a shonraíonn cad go díreach agus cén áit ar mhaith linn a fháil. Seo iad na cinn is bunúsaí chun tú a chur ar bun:

Má táimid ag lorg líon áirithe de charachtair, mar shampla, cód poist sé dhigit nó gach cód táirge trí litir, ansin a thagann muid chun an tarrthála cainníochtóirí or cainníochtóirí is sloinn speisialta iad a shonraíonn líon na gcarachtar atá le cuardach. Cuirtear cainníochtóirí i bhfeidhm ar an gcarachtar a thagann roimhe:

Anois bogadh ar aghaidh go dtí an chuid is suimiúla – anailís ar chur i bhfeidhm na feidhme cruthaithe agus an méid a d’fhoghlaimíomar faoi phatrúin ar shamplaí praiticiúla ón saol.

Uimhreacha a bhaint as téacs

Ar dtús, déanaimis anailís ar chás simplí - ní mór duit an chéad uimhir a bhaint as leite alfa-uimhriúil, mar shampla, cumhacht soláthairtí cumhachta do-bhriste ón liosta praghsanna:

Tá an loighic taobh thiar den slonn rialta simplí: d ciallaíonn sé aon dhigit, agus an cainníochtaí + deir gur chóir go mbeadh a n-uimhir amháin nó níos mó. Teastaíonn an lúide dúbailte os comhair na feidhme chun na carachtair asbhainte a thiontú ina n-uimhir iomlán as an uimhir mar théacs “ar an eitilt”.

Cód poist

Ar an gcéad amharc, tá gach rud simplí anseo - táimid ag lorg sé dhigit díreach i ndiaidh a chéile. Bainimid úsáid as carachtar speisialta d le haghaidh digit agus cainníochtaí 6 {} le haghaidh líon na gcarachtar:

Mar sin féin, is féidir cás a dhéanamh nuair a bhíonn sraith mór uimhreacha eile i ndiaidh a chéile ar an taobh clé den innéacs sa líne (uimhir theileafóin, TIN, cuntas bainc, etc.) Ansin tarraingeoidh ár séasúr rialta amach an chéad 6. digití uaidh, .i. ní oibreoidh siad i gceart:

Chun é seo a chosc, ní mór dúinn modhnóir a chur timpeall ar imeall ár ngnáthshloinne b rud a chiallaíonn deireadh focal. Déanfaidh sé seo soiléir do Excel gur cheart go mbeadh an blúire (innéacs) a theastaíonn uainn ina fhocal ar leith, agus nach mar chuid de bhlúire eile (uimhir theileafóin):

Fón

Is í an fhadhb a bhaineann le huimhir theileafóin a aimsiú sa téacs ná go bhfuil an oiread sin roghanna ann maidir le huimhreacha a scríobh – le agus gan fleiscíní, trí spásanna, le nó gan cód réigiúin idir lúibíní, etc. Dá bhrí sin, i mo thuairim, tá sé níos éasca glan amach ar dtús na carachtair seo go léir ón mbuntéacs ag baint úsáide as roinnt feidhmeanna neadaithe SUBSTAITIÚIL (SUBSTITUTE)ionas go gcloíonn sé le chéile ina n-iomláine amháin, agus ansin le rialta primitive d{11} tarraing amach 11 dhigit i ndiaidh a chéile:

ITN

Tá sé beagán níos casta anseo, mar is féidir le TIN (inár dTír) a bheith 10 ndigit (le haghaidh eintiteas dlíthiúil) nó 12-dhigit (do dhaoine aonair). Mura bhfaighidh tú an locht go háirithe, ansin is féidir go leor a bheith sásta leis an rialta d{10,12}, ach, i ndáiríre, tarraingeoidh sé amach na huimhreacha go léir ó 10 go 12 carachtar, ie agus iontráil 11 ndigit go hearráideach. Bheadh ​​sé níos ceart dhá phatrún a úsáid a nascann oibreoir loighciúil NÓ leo | (barra ingearach):

Tabhair faoi deara le do thoil go gcuirfimid uimhreacha 12 ghiotán sa cheist ar dtús, agus go n-aimsímid uimhreacha 10-ghiotán ansin amháin. Má scríobhaimid ár slonn rialta an bealach eile thart, ansin tarraingeoidh sé amach do gach duine, fiú TINanna fada 12-giotán, gan ach na chéad 10 gcarachtar. Is é sin, tar éis an chéad choinníoll a spreagadh, ní dhéantar tuilleadh fíoraithe a thuilleadh:

Is é seo an difríocht bhunúsach idir an t-oibreoir | ó fheidhm loighic chaighdeánach excel OR (NÓ), nuair nach n-athraíonn atheagrú na n-argóintí an toradh.

SKUanna Táirgí

I go leor cuideachtaí, sanntar aitheantóirí uathúla d'earraí agus do sheirbhísí - earraí, cóid SAP, SKUanna, etc. Má tá loighic ina nodaireacht, is féidir iad a bhaint go héasca as aon téacs trí úsáid a bhaint as nathanna rialta. Mar shampla, má tá a fhios againn go mbíonn trí phríomhlitir Bhéarla i gcónaí inár n-alt, fleiscín agus uimhir trí dhigit ina dhiaidh sin, ansin:

Tá an loighic taobh thiar den teimpléad simplí. [AZ] – ciallaíonn sé ceannlitreacha ar bith den aibítir Laidineach. An chéad chainníochtóir eile 3 {} a deir go bhfuil sé tábhachtach dúinne go bhfuil trí litir dá leithéid go beacht. Tar éis an fleiscín, táimid ag fanacht le trí dhigit, mar sin cuirimid ag an deireadh d{3}

Suimeanna airgid

Ar bhealach cosúil leis an alt roimhe seo, is féidir leat freisin praghsanna (costais, CBL ...) a tharraingt amach as an gcur síos ar earraí. Má léirítear méideanna airgeadaíochta, mar shampla, le fleiscín, ansin:

Patrún d le cainníochtaí + cuardach le haghaidh aon uimhir suas go fleiscín, agus d{2} lorgóidh pinginí (dhá dhigit) tar éis.

Más gá duit ní praghsanna a bhaint as, ach CBL, ansin is féidir leat an tríú argóint roghnach dár bhfeidhm RegExpExtract a úsáid, a shonraíonn orduimhir na heiliminte atá le baint. Agus, ar ndóigh, is féidir leat an fheidhm a athsholáthar SUBSTAITIÚIL (SUBSTITUTE) sna torthaí, fleiscín go dtí an deighilteoir caighdeánach deachúlach agus cuir lúide dúbailte leis ag an tús ionas go léirmhíníonn Excel an CBL aimsithe mar ghnáthuimhir:

Uimhreacha plátaí gluaisteáin

Mura nglacann tú feithiclí speisialta, leantóirí agus gluaisrothair eile, déantar uimhir chaighdeánach an ghluaisteáin a pharsáil de réir an phrionsabail “litir – trí uimhir – dhá litir – cód réigiúin”. Ina theannta sin, is féidir le cód an réigiúin a bheith 2- nó 3-dhigit, agus ní úsáidtear ach iad siúd atá cosúil le haibítir na Laidine mar litreacha. Mar sin, cabhróidh an slonn rialta seo a leanas linn uimhreacha a bhaint as an téacs:

Am

Chun an t-am a bhaint as san fhormáid HH:MM, tá an slonn rialta seo a leanas oiriúnach:

Tar éis blúirí colon [0-5]d, toisc go bhfuil sé éasca a dhéanamh amach, leagann sé uimhir ar bith sa raon 00-59. Roimh an idirstad i lúibíní, oibríonn dhá phatrún, scartha le NÓ (píopa) loighciúil:

• [0-1]d – uimhir ar bith sa raon 00-19
• 2[0-3] – uimhir ar bith sa raon 20-23

Chun an toradh a fhaightear, is féidir leat feidhm chaighdeánach Excel a chur i bhfeidhm freisin AMA (FOIREANN)é a thiontú go formáid ama atá intuigthe don chlár agus atá oiriúnach le haghaidh ríomhanna breise.

Seiceáil pasfhocal

Cuir i gcás go gcaithfimid liosta na bhfocal faire a chruthaigh úsáideoirí a sheiceáil le bheith cruinn. De réir ár rialacha, ní féidir le pasfhocail ach litreacha Béarla (cás íochtair nó cás uachtair) agus uimhreacha a bheith iontu. Ní cheadaítear spásanna, foscóir ná marcanna poncaíochta eile.

Is féidir seiceáil a eagrú leis an slonn simplí rialta seo a leanas:

Go deimhin, le patrún den sórt sin éilíonn muid go bhfuil idir an tús (^) agus deireadh ($) inár dtéacs ní raibh ann ach carachtair ón tacair a thugtar idir lúibíní cearnacha. Más gá duit freisin fad an fhocail faire a sheiceáil (mar shampla, 6 charachtar ar a laghad), ansin an cainníochtóir + is féidir an t-eatramh “sé nó níos mó” san fhoirm a chur in ionad {6,}:

Cathair ón seoladh

Ligean le rá go gcaithfimid an chathair a tharraingt ón mbarra seoltaí. Cabhróidh an clár rialta leis an téacs a bhaint as “g.” go dtí an chéad camóg eile:

Déanaimis breathnú níos dlúithe ar an bpatrún seo.

Má tá an téacs thuas léite agat, ansin thuig tú cheana féin go bhfuil brí speisialta ag roinnt carachtair i nathanna rialta (tréimhsí, réiltíní, comharthaí dollar, etc.). Más gá duit na carachtair seo a chuardach iad féin, ansin bíonn cúlslais rompu (ar a dtugtar uaireanta shielding). Mar sin, nuair a chuardach le haghaidh an blúire "g." ní mór dúinn a scríobh i abairt rialta An tUasal. má táimid ag lorg móide, ansin + etc.

Seasann an chéad dá charachtar eile inár dteimpléad, an ponc agus an réiltín cainníochtaithe, d’aon líon carachtair ar bith, ie aon ainm cathrach.

Tá camóg ag deireadh an teimpléid, mar tá muid ag lorg téacs ó “g.” le camóg. Ach is féidir go mbeadh roinnt camóga sa téacs, ceart? Ní hamháin tar éis na cathrach, ach freisin tar éis na sráide, tithe, srl. Cé acu díobh a stopfaidh ár n-iarratas? Sin atá an comhartha ceiste. Gan é, tharraingeodh ár slonn rialta amach an teaghrán is faide agus is féidir:

I dtéarmaí nathanna rialta, tá patrún den sórt sin "greedy". Chun an scéal a chur ina cheart, tá gá le comhartha ceiste – déanann sé an cainníochtaí a seasann sé “stingy” ina dhiaidh sin – agus ní thógann ár bhfiosrúchán an téacs ach suas go dtí an chéad chamóg i ndiaidh “g.”:

Ainm comhaid ón cosán iomlán

Staid an-choitianta eile is ea ainm an chomhaid a bhaint as an gcosán iomlán. Cabhróidh léiriú simplí rialta den fhoirm anseo:

Is é an cleas anseo ná go dtarlaíonn an cuardach, i ndáiríre, sa treo eile - ó thús deireadh, toisc go bhfuil ag deireadh ár teimpléad $, agus táimid ag lorg gach rud roimh sé suas go dtí an chéad backslash ó thaobh na láimhe deise. Éalaítear an cúlslais, cosúil leis an bpointe sa sampla roimhe seo.

PS

“I dtreo an deireadh” ba mhaith liom a shoiléiriú gur cuid bheag de na féidearthachtaí go léir a chuireann nathanna cainte rialta ar fáil iad na rudaí thuas. Tá go leor carachtair agus rialacha speisialta ann maidir lena n-úsáid, agus scríobhadh leabhair iomlána ar an ábhar seo (molaim an ceann seo ar a laghad mar thús). Ar bhealach, is ealaín beagnach é nathanna rialta a scríobh. Beagnach i gcónaí, is féidir slonn rialta invented a fheabhsú nó a fhorlíonadh, rud a fhágann go bhfuil sé níos galánta nó in ann oibriú le raon níos leithne de shonraí ionchuir.

Chun anailís agus parsáil a dhéanamh ar ghnáthshlabhra daoine eile nó do chuid féin a dhífhabhtú, tá roinnt seirbhísí áisiúla ar líne: Regex101, RegExr agus níos mó

Ar an drochuair, ní thacaítear le gach gné de na habairtí rialta clasaiceacha i VBA (mar shampla, cuardach droim ar ais nó ranganna POSIX) agus is féidir leo oibriú le Coireallach, ach is dóigh liom go bhfuil an méid atá ann go leor don chéad uair chun tú a shásamh.

Mura bhfuil tú nua ar an ábhar, agus go bhfuil rud éigin agat le roinnt, fág abairtí rialta úsáideach nuair a bhíonn tú ag obair in Excel sna tuairimí thíos. Tá aigne amháin go maith, ach is péire iad dhá bhuatais!

• An fheidhm SUBSTITUTE a chur in ionad téacs agus é a ghlanadh
• Cuardach agus béim a chur ar charachtair Laidine i dtéacs
• Cuardaigh an téacs dá samhail is gaire (Ivanov = Ivonov = Ivanof, etc.)