Analize tèks ak ekspresyon regilye (RegExp) nan Excel

Youn nan travay ki pi konsome tan ak fwistre lè w ap travay ak tèks nan Excel se analiz – analize "labouyl" alfanumerik la nan eleman ak ekstrè fragman nou bezwen nan li. Pa egzanp:

• ekstrè kòd postal la nan adrès la (li bon si kòd postal la toujou nan kòmansman an, men e si li pa?)
• jwenn nimewo a ak dat fakti a nan deskripsyon peman an nan deklarasyon labank la
• ekstraksyon nan TIN soti nan deskripsyon motley nan konpayi nan lis la nan kontrepati
• chèche yon nimewo machin oswa nimewo atik nan deskripsyon an, elatriye.
  

Anjeneral nan ka sa yo, apre yon demi èdtan nan chwazi tèks la manyèlman, panse kòmanse vin nan tèt ou yon jan kanmenm otomatize pwosesis sa a (sitou si gen anpil done). Gen plizyè solisyon ak divès degre konpleksite-efikasite:

• Sèvi ak bati nan fonksyon tèks Excel pou rechèch-koupe-lakòl tèks: LEVSIMV (GFTCH), DWA (DWA), PSTR (mitan), STsEPIT (Konkatenasyon) ak analogue li yo, KONBINE (Tèks ansanm), EXACT (egzakte) elatriye Metòd sa a bon si gen yon lojik klè nan tèks la (pa egzanp, endèks la toujou nan kòmansman adrès la). Sinon, fòmil yo vin pi konplike, epi pafwa, li menm rive nan fòmil etalaj, ki ralanti anpil sou gwo tab.
• Lè l sèvi avèk tankou operatè resanblans tèks soti nan Visual Basic vlope nan yon fonksyon makro koutim. Sa a pèmèt ou aplike yon rechèch ki pi fleksib lè l sèvi avèk karaktè wildcard (*, #,?, elatriye) Malerezman, zouti sa a pa ka ekstrè soustring vle a soti nan tèks la - sèlman tcheke si li genyen nan li.

Anplis de sa ki pi wo a, gen yon lòt apwòch ki trè byen li te ye nan ti sèk etwat nan pwogramasyon pwofesyonèl, devlopè entènèt ak lòt techies - sa a se ekspresyon regilye (Ekspresyon regilye = RegExp = "regexps" = "regular"). Senpleman mete, RegExp se yon lang kote karaktè espesyal ak règ yo itilize pou chèche sousstrings ki nesesè yo nan tèks la, ekstrè yo oswa ranplase yo ak lòt tèks.. Ekspresyon regilye yo se yon zouti trè pwisan ak bèl ki depase tout lòt fason pou travay ak tèks pa yon lòd nan grandè. Anpil langaj pwogramasyon (C#, PHP, Perl, JavaScript...) ak editè tèks (Word, Notepad++...) sipòte ekspresyon regilye.

Microsoft Excel malerezman pa gen sipò RegExp soti nan bwat la, men sa a ka fasil fikse ak VBA. Louvri Editè Visual Basic soti nan tab la pwomotè (Devlopè) oswa rakoursi klavye Alt+F11. Lè sa a, mete nouvo modil la nan meni an Mete – Modil epi kopye tèks fonksyon macro sa a la:

Fonksyon Piblik RegExpExtract(Tèks Kòm Chèn, Modèl Kòm Chèn, Opsyonèl Atik Kòm nonb antye relatif = 1) Kòm Chèn sou Erè GoTo ErrHandl Mete regex = CreateObject("VBScript.RegExp") regex.Pattern = Modèl regex.Global = Vrè Si regex.Test (Tèks) Lè sa a, Mete alimèt = regex.Execute (Tèks) RegExpExtract = matche.Item (Item - 1) Fonksyon Sòti Fen Si ErrHandl: RegExpExtract = CVErr (xlErrValue) Fonksyon Fen  

Nou kapab kounye a fèmen Editè Visual Basic epi retounen nan Excel pou eseye nouvo karakteristik nou an. Sentaks li se sa ki annapre yo:

=RegExpExtract(Txt; Modèl; Atik)

ki kote

• txt – yon selil ki gen tèks ke nou ap tcheke epi ki soti nan ki nou vle ekstrè substring nou bezwen an
• modèl – mask (modèl) pou rechèch substring
• Atik – nimewo sekans nan substring yo dwe ekstrè, si gen plizyè nan yo (si yo pa espesifye, Lè sa a, premye ensidan an parèt)

Bagay ki pi enteresan isit la, nan kou, se Modèl - yon seri modèl nan karaktè espesyal "nan lang nan" nan RegExp, ki presize ki sa egzakteman ak ki kote nou vle jwenn. Men sa ki pi fondamantal pou w kòmanse:

Si nou ap chèche pou yon sèten kantite karaktè, pou egzanp, yon kòd lapòs sis chif oswa tout kòd pwodwi twa lèt, Lè sa a, nou vin pote sekou. quantifiers or quantifiers se ekspresyon espesyal ki espesifye kantite karaktè yo dwe chèche. Quantifiers yo aplike nan karaktè ki vini anvan li:

Koulye a, ann ale nan pati ki pi enteresan an - yon analiz de aplikasyon an nan fonksyon an kreye ak sa nou te aprann sou modèl sou egzanp pratik nan lavi.

Ekstrè nimewo nan tèks la

Pou kòmanse, ann analize yon ka senp - ou bezwen ekstrè premye nimewo a nan labouyl alfanumerik, pou egzanp, pouvwa a nan ekipman pou pouvwa san enteripsyon nan lis pri a:

Lojik dèyè ekspresyon regilye a se senp: d vle di nenpòt chif, ak quantifier la + di ke nimewo yo ta dwe youn oswa plis. Doub mwens la devan fonksyon an bezwen "sou vole" konvèti karaktè yo ekstrè nan yon nimewo konplè soti nan nimewo-kòm-tèks la.

kòd postal

Nan premye gade, tout bagay se senp isit la - nou ap chèche pou egzakteman sis chif nan yon ranje. Nou itilize yon karaktè espesyal d pou chif ak quantifier 6 {} pou kantite karaktè:

Sepandan, yon sitiyasyon posib lè, sou bò gòch la nan endèks la nan liy lan, gen yon lòt gwo seri nimewo nan yon ranje (nimewo telefòn, TIN, kont labank, elatriye) Lè sa a, sezon regilye nou an pral rale premye 6 yo. chif ki soti nan li, sa vle di pa pral travay kòrèkteman:

Pou anpeche sa rive, nou bezwen ajoute yon modifye alantou bor yo nan ekspresyon regilye nou an b ki vle di nan fen yon mo. Sa a pral fè li klè Excel ke fragman (endèks) nou bezwen an ta dwe yon mo separe, epi yo pa fè pati yon lòt fragman (nimewo telefòn):

Telefòn

Pwoblèm nan jwenn yon nimewo telefòn nan tèks la se ke gen anpil opsyon pou ekri nimewo - avèk ak san tire, atravè espas, avèk oswa san yon kòd rejyon an parantèz, elatriye. Se poutèt sa, nan opinyon mwen, li pi fasil yo. premyèman netwaye tout karaktè sa yo nan tèks sous la lè l sèvi avèk plizyè fonksyon enbrike Ranplasan (SUBSTITUTION)se konsa ke li kole ansanm nan yon sèl antye, ak Lè sa a, ak yon regilye primitif d{11} rale 11 chif nan yon ranje:

ITN

Li yon ti kras pi konplike isit la, paske TIN (nan peyi nou an) ka 10-chif (pou antite legal) oswa 12-chif (pou moun). Si ou pa jwenn fòt espesyalman, Lè sa a, li se byen posib yo dwe satisfè ak regilye a d{10,12}, men, entèdi pale, li pral rale soti tout nimewo soti nan 10 a 12 karaktè, sa vle di ak erè antre nan 11 chif. Li ta pi kòrèk pou itilize de modèl ki konekte pa yon operatè OSWA ki lojik | (ba vètikal):

Tanpri sonje ke nan rechèch la nou premye gade pou nimewo 12-bit, epi sèlman Lè sa a, pou nimewo 10-bit. Si nou ekri ekspresyon regilye nou an nan lòt fason, Lè sa a, li pral rale soti pou tout moun, menm long TIN 12-bit, sèlman 10 premye karaktè yo. Sa vle di, apre yo fin deklanche premye kondisyon an, plis verifikasyon pa fèt ankò:

Sa a se diferans fondamantal ant operatè a | soti nan yon fonksyon lojik excel estanda OR (OSWA), kote reranje agiman yo pa chanje rezilta a.

Pwodwi SKUs

Nan anpil konpayi, idantifyan inik yo asiyen nan machandiz ak sèvis - atik, kòd SAP, SKU, elatriye. Si gen lojik nan notasyon yo, Lè sa a, yo ka fasil rale soti nan nenpòt tèks lè l sèvi avèk ekspresyon regilye. Pou egzanp, si nou konnen ke atik nou yo toujou konpoze de twa lèt majiskil angle, yon tire ak yon nimewo ki vin apre twa chif, lè sa a:

Lojik dèyè modèl la senp. [AZ] – vle di nenpòt lèt majiskil nan alfabè Latin. Pwochen quantifier la 3 {} di ke li enpòtan pou nou ke gen egzakteman twa lèt sa yo. Apre tirè a, nou ap tann pou twa chif, kidonk nou ajoute nan fen an d{3}

Kantite lajan kach

Nan yon fason ki sanble ak paragraf anvan an, ou kapab tou rale soti pri (depans, VAT ...) nan deskripsyon an nan machandiz yo. Si kantite lajan, pa egzanp, endike ak yon tire, lè sa a:

modèl d ak quantifier + chèche nenpòt nimewo jiska yon tire, epi d{2} ap chèche pou peni (de chif) apre.

Si ou bezwen ekstrè pa pri, men VAT, Lè sa a, ou ka itilize twazyèm agiman an si ou vle nan fonksyon RegExpExtract nou an, ki espesifye nimewo a ordinèl nan eleman nan yo dwe ekstrè. Epi, nan kou, ou ka ranplase fonksyon an Ranplasan (SUBSTITUTION) nan rezilta yo, tire nan separasyon desimal estanda a epi ajoute yon doub mwens nan kòmansman an pou Excel entèprete VAT yo jwenn kòm yon nimewo nòmal:

Nimewo plak machin yo

Si ou pa pran machin espesyal, trelè ak lòt motosiklèt, Lè sa a, nimewo machin estanda a analize dapre prensip "lèt - twa nimewo - de lèt - kòd rejyon an". Anplis, kòd rejyon an ka gen 2- oswa 3-chif, epi sèlman sa yo ki sanble nan aparans alfabè Latin yo itilize kòm lèt. Kidonk, ekspresyon regilye sa a ap ede nou ekstrè nimewo nan tèks la:

Tan

Pou ekstrè tan an nan fòma HH:MM, ekspresyon regilye sa a apwopriye:

Apre fragman kolon [0-5]d, kòm li fasil pou evalye, mete nenpòt nimewo nan seri a 00-59. Anvan kolon an nan parantèz, de modèl travay, separe pa yon lojik OR (tiyo):

• [0-1]d – nenpòt nimewo nan seri 00-19
• 2[0-3] – nenpòt nimewo nan seri 20-23

Pou rezilta a jwenn, ou ka anplis aplike fonksyon estanda Excel la TAN (EKIP)konvèti li nan yon fòma tan ki konprann pwogram nan ak apwopriye pou plis kalkil.

Tcheke modpas

Sipoze ke nou bezwen tcheke lis modpas itilizatè yo envante pou kòrèk. Dapre règ nou yo, modpas yo ka sèlman genyen lèt anglè (miniskil oswa majiskil) ak nimewo. Espas, souliye ak lòt siy ponktiyasyon pa pèmèt.

Tcheke ka òganize lè l sèvi avèk ekspresyon regilye senp sa a:

An reyalite, ak yon modèl konsa nou mande pou ant kòmansman an (^) epi fini ($) nan tèks nou an te gen sèlman karaktè nan seri a yo te bay nan parantèz kare. Si ou bezwen tou tcheke longè modpas la (pa egzanp, omwen 6 karaktè), Lè sa a, quantifier la + ka ranplase pa entèval "sis oswa plis" nan fòm lan {6,}:

Vil soti nan adrès

Ann di nou bezwen rale vil la nan ba adrès la. Pwogram regilye a pral ede, ekstrè tèks ki soti nan "g." nan pwochen vigil la:

Ann pran yon gade pi pre nan modèl sa a.

Si ou te li tèks ki anwo a, Lè sa a, ou deja konprann ke kèk karaktè nan ekspresyon regilye (peryòd, asterisk, siy dola, elatriye) gen yon siyifikasyon espesyal. Si ou bezwen chèche karaktè sa yo tèt yo, Lè sa a, yo anvan yo pa yon backslash (pafwa yo rele pwoteksyon). Se poutèt sa, lè w ap chèche fragman "g." nou dwe ekri nan ekspresyon regilye Mesye si nou ap chèche pou yon plis, lè sa a + elatriye.

De pwochen karaktè yo nan modèl nou an, pwen an ak etwal la quantifier, kanpe pou nenpòt ki kantite nenpòt karaktè, sa vle di nenpòt non vil.

Gen yon vigil nan fen modèl la, paske n ap chèche tèks ki soti nan "g." nan yon vigil. Men, ka gen plizyè vigil nan tèks la, pa vre? Non sèlman apre vil la, men tou apre lari a, kay, elatriye. Sou kilès nan yo demann nou an ap sispann? Se pou sa mak kesyon an. San li, ekspresyon regilye nou an ta rale kòd ki pi long posib:

An tèm de ekspresyon regilye, tankou yon modèl se "visye". Pou korije sitiyasyon an, yo bezwen yon mak kesyon - li fè quantifier la apre sa li kanpe "rach" - ak rechèch nou an pran tèks la sèlman jiska premye vigil kontwa apre "g.":

Non fichye soti nan chemen konplè

Yon lòt sitiyasyon trè komen se ekstrè non dosye a soti nan chemen an plen. Yon senp ekspresyon regilye fòm lan ap ede isit la:

Trick la isit la se ke rechèch la, an reyalite, rive nan direksyon opoze a - depi nan fen a nan kòmansman an, paske nan fen modèl nou an se $, epi nou ap chèche pou tout bagay anvan li jiska premye backslash ki soti nan bò dwat la. Backslash la chape, tankou pwen an nan egzanp anvan an.

PS

"Vers la fen" Mwen vle klarifye ke tout sa ki anwo yo se yon ti pati nan tout posiblite yo ke ekspresyon regilye yo bay. Gen yon anpil nan karaktè espesyal ak règ pou itilize yo, ak tout liv yo te ekri sou sijè sa a (mwen rekòmande omwen yon sèl sa a pou yon kòmansman). Nan yon fason, ekri ekspresyon regilye se prèske yon atizay. Prèske tout tan, yon ekspresyon regilye envante ka amelyore oswa konplete, fè li pi elegant oswa kapab travay ak yon seri pi laj nan done opinyon.

Pou analize ak analize ekspresyon regilye lòt moun oswa debogaj pwòp ou a, gen plizyè sèvis pratik sou entènèt: RegEx101, RegExr ak plis ankò

Malerezman, se pa tout karakteristik ekspresyon regilye klasik yo sipòte nan VBA (pa egzanp, rechèch ranvèse oswa klas POSIX) epi yo ka travay ak sirilik, men mwen panse ke sa ki genyen ase pou premye fwa fè ou plezi.

Si ou pa nouvo nan sijè a, epi ou gen yon bagay yo pataje, kite ekspresyon regilye itil lè w ap travay nan Excel nan kòmantè ki anba yo. Yon sèl lide bon, men de bòt se yon pè!

• Ranplase ak netwaye tèks ak fonksyon SUBSTITUTE
• Rechèch ak mete aksan sou karaktè Latin nan tèks la
• Chèche tèks ki sanble ki pi pre a (Ivanov = Ivonov = Ivanof, elatriye)