Parse Text mat reguläre Ausdréck (RegExp) an Excel

Ee vun de stäerkste Zäit-opwänneg an frustréiernd Aufgaben wann Dir mat Text an Excel schafft ass analyséieren - den alphanumeresche "Kot" an Komponenten ze analyséieren an d'Fragmenter ze extrahieren déi mir brauchen. Zum Beispill:

• de Postcode vun der Adress extrahéieren (et ass gutt wann de Postleitzuel ëmmer am Ufank ass, awer wat wann et net ass?)
• d'Zuel an den Datum vun der Rechnung ze fannen aus der Beschreiwung vun der Bezuelung op der Bankausso
• Extraktioun vun TIN aus motley Beschreiwunge vun Entreprisen an der Lëscht vun Géigespiller
• Sich no enger Autosnummer oder Artikelnummer an der Beschreiwung, asw.
  

Normalerweis an esou Fäll, no enger hallwer Stonn vun dréchen Texter manuell plécke, Gedanken fänken iergendwéi ze vergiessen dëse Prozess ze automatiséieren (besonnesch wann et vill Donnéeën ass). Et gi verschidde Léisungen a mat ënnerschiddleche Grad vu Komplexitéit-Effizienz:

• benotzt gebaut-an Excel Text Funktiounen fir Text ze sichen: LEVSIMV (LÉNKS), RIETS (RIETS), PSTR (Mëtt), STEPIT (CONCATENATE) a seng Analoga, KOMBINÉIEREN (JOINTEXT), EXAKT (EXAKT) etc. Dës Method ass gutt, wann et eng kloer Logik am Text ass (zum Beispill, den Index steet ëmmer um Ufank vun der Adress). Soss ginn d'Formelen vill méi komplizéiert an heiansdo kënnt et souguer zu Arrayformelen, déi op groussen Dëscher staark verlangsamen.
• benotzt wéi Text Ähnlechkeet Bedreiwer vu Visual Basic gewéckelt an enger personaliséierter Makro Funktioun. Dëst erlaabt Iech eng méi flexibel Sich mat Wildcard Zeechen ëmzesetzen (*, #,?, etc.) Leider kann dëst Tool net déi gewënschte Substring aus dem Text extrahéieren - kontrolléiert nëmmen ob et dran ass.

Zousätzlech zu den uewe genannte gëtt et eng aner Approche déi ganz gutt bekannt ass a schmuele Kreesser vu professionnelle Programméierer, Webentwéckler an aner Techniken - dëst ass reegelméisseg Ausdrock (Regelméisseg Ausdréck = RegExp = "regexps" = "Regularer"). Einfach gesot, RegExp ass eng Sprooch wou speziell Zeechen a Regele benotzt gi fir déi néideg Substrings am Text ze sichen, se ze extrahéieren oder duerch aner Texter ze ersetzen. Regelméisseg Ausdréck sinn e ganz mächtegt a schéint Tool dat all aner Manéiere fir mam Text mat enger Uerdnung vun der Gréisst ze schaffen iwwerschreift. Vill Programméierungssproochen (C#, PHP, Perl, JavaScript ...) an Texteditoren (Word, Notepad ++...) ënnerstëtzen reegelméisseg Ausdréck.

Microsoft Excel huet leider keng RegExp Ënnerstëtzung aus der Këscht, awer dëst kann einfach mat VBA fixéiert ginn. Öffnen de Visual Basic Editor vun der Tab Entwéckler (Entwéckler) oder Tastatur Ofkiirzung Alt+F11. Da gitt den neie Modul duerch de Menü Insert - Modul a kopéiert den Text vun der folgender Makrofunktioun do:

Ëffentlech Funktioun RegExpExtract(Text Als String, Muster Als String, Optional Item As Integer = 1) As String On Error GoTo ErrHandl Set regex = CreateObject("VBScript.RegExp") regex.Pattern = Muster regex.Global = True If regex.Test (Text) Then Set matches = regex.Execute(Text) RegExpExtract = matches.Item(Item - 1) Exit Function End If ErrHandl: RegExpExtract = CVErr(xlErrValue) End Function  

Mir kënnen elo de Visual Basic Editor zoumaachen an zréck op Excel fir eis nei Feature auszeprobéieren. Seng Syntax ass déi folgend:

=RegExpExtract(Txt; Pattern; Item)

wou

• txt - eng Zell mat dem Text dee mir iwwerpréiwen an aus deem mir de Substring wëllen extrahieren dee mir brauchen
• Muster - Mask (Muster) fir Ënnerstring Sich
• Kuurzmeldung - d'Sequenznummer vun der Substring déi extrahéiert gëtt, wann et e puer vun hinnen sinn (wann net spezifizéiert, da gëtt déi éischt Optriede ugewisen)

Déi interessantst Saach hei ass natierlech Muster - e Schabloun String vu speziellen Zeechen "an der Sprooch" vu RegExp, déi spezifizéiert wat genau a wou mir wëllen fannen. Hei sinn déi meescht Basis fir Iech unzefänken:

Wa mir no enger bestëmmter Unzuel u Charaktere sichen, zum Beispill e sechsziffere Postcode oder all dräi-Bréif Produktcoden, da komme mir zur Rettung quantifizéierer or quantifizéierer si speziell Ausdréck déi d'Zuel vun den Zeechen spezifizéieren fir ze sichen. Quantifikateure ginn op de Charakter ugewannt dee virdru kënnt:

Loosst eis elo op den interessantsten Deel goen - eng Analyse vun der Uwendung vun der erstallt Funktioun a wat mir iwwer Mustere geléiert hunn iwwer praktesch Beispiller vum Liewen.

Extrait Zuelen aus Text

Fir unzefänken, loosst eis en einfache Fall analyséieren - Dir musst déi éischt Nummer aus alphanumeresche Porridge extrahéieren, zum Beispill d'Kraaft vun onënnerbrachbare Stroumversuergung aus der Präislëscht:

D'Logik hannert dem regulären Ausdrock ass einfach: d heescht all Ziffer, an de Quantifizéierer + seet, datt hir Zuel soll een oder méi ginn. Den duebelen Minus virun der Funktioun ass néideg fir "on the fly" déi extrahéiert Zeechen an eng voll Zuel aus der Nummer-als-Text ëmzewandelen.

Postleitzuel

Op den éischte Bléck ass alles einfach hei - mir sichen no genee sechs Zifferen an enger Zeil. Mir benotzen e spezielle Charakter d fir Ziffer an quantifier 6 {} fir d'Zuel vun Zeechen:

Allerdéngs ass eng Situatioun méiglech, wann lénks vum Index an der Linn nach eng grouss Zuel vun Zuelen hannereneen ass (Telefonsnummer, TIN, Bankkonto, etc.) Dann zitt eis regulär Saison déi éischt 6 eraus Zifferen dovun, dh wäert net richteg funktionnéieren:

Fir dëst ze verhënneren, musse mir e Modifikateur ronderëm d'Kante vun eisem reguläre Ausdrock addéieren b d'Enn vun engem Wuert bedeit. Dëst wäert et fir Excel kloer maachen datt de Fragment (Index) dee mir brauchen e separat Wuert sollt sinn, an net Deel vun engem anere Fragment (Telefonsnummer):

Telefon

De Problem fir eng Telefonsnummer am Text ze fannen ass datt et sou vill Méiglechkeete gëtt fir Zuelen ze schreiwen - mat an ouni Bindestrecken, duerch Plazen, mat oder ouni Regiounscode an Klammern, etc. Dofir ass et menger Meenung no méi einfach ze maachen. propper fir d'éischt all dës Zeechen aus dem Quelltext mat e puer nestéierte Funktiounen SUBSTITUT (SUBSTITUT)sou datt et zu engem eenzege Ganzt zesummenhänkt, an dann mat engem primitive Regelméisseg d {11} zitt 11 Zifferen hannereneen eraus:

ITN

Et ass e bësse méi komplizéiert hei, well TIN (an eisem Land) kann 10-Zifferen (fir legal Entitéite) oder 12-Zifferen (fir Eenzelpersounen) sinn. Wann Dir net besonnesch Feeler fannt, dann ass et ganz méiglech zefridden mat der regulärer d {10,12}, mee, streng geschwat, et zitt all Zuelen aus 10 ze 12 Zeechen, dh a falsch aginn 11 Zifferen. Et wier méi korrekt zwee Muster ze benotzen verbonne vun engem logeschen ODER Bedreiwer | (vertikal Bar):

Notéiert w.e.g. datt mir an der Ufro fir d'éischt no 12-Bit Zuelen kucken, an nëmmen dann no 10-Bit Zuelen. Wa mir eise regulären Ausdrock ëmgedréint schreiwen, da zitt et fir jiddereen eraus, och laang 12-Bit TINs, nëmmen déi éischt 10 Zeechen. Dat ass, nodeems déi éischt Konditioun ausgeléist ass, gëtt weider Verifizéierung net méi gemaach:

Dëst ass de fundamentalen Ënnerscheed tëscht dem Bedreiwer | vun enger Standard excel Logik Funktioun OR (ODER), wou d'Rearrangement vun den Argumenter d'Resultat net ännert.

Produkt SKUs

A ville Firmen sinn eenzegaarteg Identifizéierer fir Wueren a Servicer zougewisen - Artikelen, SAP Coden, SKUs, etc.. Wann et Logik an hirer Notatioun ass, da kënne se einfach aus all Text mat reguläre Ausdréck erausgezunn ginn. Zum Beispill, wa mir wëssen datt eis Artikelen ëmmer aus dräi grouss englesch Buschtawen, engem Bindestrich an eng spéider dräi-Zifferen Nummer besteet, dann:

D'Logik hannert der Schabloun ass einfach. [AZ] - heescht all Haaptbuschtawen vum laténgesche Alphabet. Déi nächst Quantifizéierer 3 {} seet, datt et fir eis wichteg ass, datt et genee dräi esou Buschtawen ginn. Nom Bindestrich waarden mir op dräi Zifferen, also addéiere mer um Enn d {3}

Cash Zommen

Op eng ähnlech Manéier wéi am virege Paragraphe kënnt Dir och Präisser (Käschten, TVA ...) aus der Beschreiwung vu Wueren erauszéien. Wann monetär Zommen, zum Beispill, mat engem Bindestrecken uginn sinn, dann:

Muster d mat quantifier + sicht all Zuel bis zu engem Bindestrich, an d {2} wäert fir Penny kucken (zwee Ziffere) na.

Wann Dir musst net Präisser extrahéieren, mee TVA, da kënnt Dir dat drëtt fakultativ Argument vun eiser RegExpExtract Funktioun benotzen, déi d'Ordinale Zuel vum Element spezifizéiert dat extrahéiert gëtt. An, natierlech, kënnt Dir d'Funktioun ersetzen SUBSTITUT (SUBSTITUT) an de Resultater, Bindestrich op de Standard Dezimal Separator a füügt en duebelen Minus am Ufank un, sou datt Excel déi fonnt TVA als normal Zuel interpretéiert:

Auto Plack Zuelen

Wann Dir keng speziell Gefierer, Unhänger an aner Motorrieder hëlt, da gëtt d'Standardautonummer no dem Prinzip "Bréif - dräi Zuelen - zwee Buschtawen - Regiounscode" parséiert. Ausserdeem kann de Regiounscode 2- oder 3-Zifferen sinn, an nëmmen déi, déi am Erscheinungsbild dem laténgesche Alphabet ähnlech sinn, ginn als Buschtawen benotzt. Also hëlleft de folgende reguläre Ausdrock eis Zuelen aus dem Text ze extrahieren:

Zäit

Fir d'Zäit am HH:MM Format ze extrahieren, ass de folgende reguläre Ausdrock passend:

No Colon Fragmenter [0-5] d, wéi et einfach ass erauszefannen, setzt all Zuel am Beräich 00-59. Virun dem Doppelpunkt an Klammeren funktionnéieren zwee Musteren, getrennt vun engem logeschen ODER (Päif):

• [0-1] d - all Zuel am Beräich 00-19
• 2 [0-3] - all Zuel am Beräich 20-23

Fir d'Resultat kritt Dir zousätzlech d'Standard Excel Funktioun uwenden TIME (TEAM)fir et an en Zäitformat ëmzewandelen, dee fir de Programm verständlech ass a fir weider Berechnungen gëeegent ass.

Passwuert kontrolléieren

Ugeholl datt mir d'Lëscht vun de Passwierder erfonnt vun de Benotzer fir Korrektheet musse kontrolléieren. No eise Reegele kënnen Passwierder nëmmen englesch Buschtawen (Kleng- oder Groussbuchstaben) an Zuelen enthalen. Plazen, Ënnersträicher an aner Punktuéierungszeechen sinn net erlaabt.

Iwwerpréiwung kann organiséiert ginn mat dem folgenden einfache reguläre Ausdrock:

Tatsächlech, mat sou engem Muster verlaange mir datt tëscht dem Ufank (^) an Enn ($) an eisem Text waren et nëmmen Zeeche aus dem Set an de Klammeren. Wann Dir och d'Längt vum Passwuert kontrolléiere musst (zum Beispill op d'mannst 6 Zeechen), dann de Quantifizéierer + kann duerch den Intervall "sechs oder méi" an der Form ersat ginn {6,}:

Stad aus Adress

Loosst eis soen, mir mussen d'Stad vun der Adressbar zéien. De reguläre Programm wäert hëllefen, den Text aus "g" extrahéieren. zum nächste Komma:

Loosst eis dëst Muster méi no kucken.

Wann Dir den Text hei uewen gelies hutt, da hutt Dir scho verstanen datt e puer Zeechen a reguläre Ausdréck (Perioden, Asterisken, Dollarschëlder, etc.) eng speziell Bedeitung hunn. Wann Dir fir dës Personnagen selwer sichen musst, da gi se virun engem Réckschnëtt (heiansdo genannt ofschëdden). Dofir, wann Dir no dem Fragment "g" sicht. mir mussen am reguläre Ausdrock schreiwen Mr. wa mir no engem Plus sichen, dann + etc.

Déi nächst zwee Zeechen an eiser Schabloun, de Punkt an de Quantifikateur Asterisk, stinn fir all Zuel vun all Zeechen, also all Stadnumm.

Et gëtt e Komma um Enn vun der Schabloun, well mir sichen no Text vum "g." zu engem Komma. Mee et kënnen e puer Kommaen am Text sinn, oder? Net nëmmen no der Stad, mä och no der Strooss, Haiser, etc. Op wéi eng vun hinnen hält eis Demande op? Dofir ass d'Frozeechen fir. Ouni et géif eise regulären Ausdrock de längste String erauszéien:

Am Sënn vun reegelméissegen Ausdréck ass sou e Muster "giereg". Fir d'Situatioun ze korrigéieren, ass e Fragezeechen gebraucht - et mécht de Quantifizéierer, duerno ass et "knäppeg" - an eis Ufro hëlt den Text nëmmen bis op den éischte Konterkomma nom "g.":

Dateinumm vum ganze Wee

Eng aner ganz heefeg Situatioun ass de Dateinumm aus dem ganze Wee ze extrahieren. En einfache reguläre Ausdrock vun der Form hëlleft hei:

Den Trick hei ass datt d'Sich tatsächlech an der entgéintgesate Richtung geschitt - vum Enn bis zum Ufank, well um Enn vun eiser Schabloun ass $, a mir sichen alles ier et bis zum éischte Réckschlag vu riets. De Réckschnëtt ass entkomm, wéi de Punkt am virege Beispill.

PS

"A Richtung Enn" Ech wëll klären, datt all déi uewe genannte e klengen Deel vun all de Méiglechkeeten ass, déi regulär Ausdréck bidden. Et gi vill speziell Zeechen a Regele fir hir Notzung, a ganz Bicher goufen iwwer dëst Thema geschriwwen (Ech recommandéieren op d'mannst dëst fir e Start). Op eng Manéier ass d'Regelméisseg Ausdréck schreiwen bal eng Konscht. Bal ëmmer kann en erfonnten regulären Ausdrock verbessert oder ergänzt ginn, wat et méi elegant mécht oder fäeg ass mat enger méi breet Palette vun Inputdaten ze schaffen.

Fir aner Leit hir regulär Ausdréck ze analyséieren an ze analyséieren oder Är eegen ze debuggen, ginn et e puer praktesch Online Servicer: RegEx 101, RegExr a méi

Leider sinn net all d'Features vu klassesche reguläre Ausdréck an VBA ënnerstëtzt (zum Beispill, ëmgedréint Sich oder POSIX Klassen) a kënne mat kyrillesche schaffen, awer ech mengen datt wat et ass genuch fir d'éischte Kéier fir Iech ze gefalen.

Wann Dir net nei op d'Thema sidd, an Dir hutt eppes ze deelen, loosst regelméisseg Ausdréck nëtzlech wann Dir an Excel an de Kommentaren hei drënner schafft. Ee Geescht ass gutt, awer zwee Stiwwelen sinn e Paar!

• Ersetzen a botzen Text mat der SUBSTITUTE Funktioun
• Sich an Highlight vun Latäin Zeechen am Text
• Sich no noosten ähnlechen Text (Ivanov = Ivonov = Ivanof, etc.)