cuntenutu
Unu di i travaglii più fastidiosi è frustranti quandu u travagliu cù u testu in Excel hè parsing - analizà u "porridge" alfanumericu in cumpunenti è estrae i frammenti chì avemu bisognu. Per esempiu:
- estrazione di u codice postale da l'indirizzu (hè bonu se u codice postale hè sempre à u principiu, ma chì s'ellu ùn hè micca?)
- truvà u numeru è a data di a fattura da a descrizzione di u pagamentu in u rapportu bancariu
- estrazione di TIN da diverse descrizioni di cumpagnie in a lista di contrapartiti
- cerca un numeru di vittura o un numeru d'articulu in a descrizzione, etc.
Di solitu in tali casi, dopu à una meza ora di cugliera in u testu manualmente, i pinsamenti cumincianu à vene in mente in qualchì modu per automatizà stu prucessu (soprattuttu s'ellu ci hè assai dati). Ci sò parechje soluzioni è cù diversi gradi di cumplessità-efficienza:
- U Paghjolu funzioni di testu integrate in Excel per circà u testu-cut-glue: LEVSIMV (MANCA), DIRITTA (DIRITTA), PSTR (mezzu), STsEPIT (CONCATENA) è i so analoghi, CUMBINA (TEXT JOINTU), ESATTU (ESATTU) etc. Stu metudu hè bonu s'ellu ci hè una logica chjara in u testu (per esempiu, l'indici hè sempre à u principiu di l'indirizzu). Altrimenti, e formule diventanu assai più complicate è, à volte, vene ancu à formule di array, chì rallenta assai in tavule grande.
- Praticà cum'è operatore di similarità di testu da Visual Basic impannillatu in una funzione macro persunalizata. Questu permette di implementà una ricerca più flexibule cù caratteri cumeddi (*, #,?, etc.) Sfurtunatamente, questu strumentu ùn pò micca estrattà a substringa desiderata da u testu - verificate solu s'ellu hè cuntenutu in questu.
In più di ciò chì sopra, ci hè un altru approcciu chì hè assai cunnisciutu in i circles stretti di programatori prufessiunali, sviluppatori web è altri techies - questu hè espressioni regulare (Espressioni Regular = RegExp = "regexps" = "regulars"). Simply put, RegExp hè una lingua induve i caratteri speciali è e regule sò usati per circà i substrings necessarii in u testu, estratti o rimpiazzà cù un altru testu.. L'espressioni rigulari sò un strumentu assai putente è bellu chì supera tutti l'altri modi di travaglià cù u testu da un ordine di grandezza. Parechji linguaggi di prugrammazione (C#, PHP, Perl, JavaScript ...) è editori di testu (Word, Notepad ++...) supportanu espressioni regulari.
Sfurtunatamente, Microsoft Excel ùn hà micca supportu RegExp fora di a scatula, ma questu pò esse facilmente riparatu cù VBA. Aprite l'Editor Visual Basic da a tabulazione documentaliste (Sviluppatore) o scorciatoia da tastiera Alt+F11. Allora inserite u novu modulu attraversu u menu Inserisce - Modulu è copià u testu di a seguente macro funzione quì:
Funzione Publica 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 = True If regex.Test (Text) Allora Set matchs = regex.Execute (Text) RegExpExtract = matchs.Item (Item - 1) Exit Function End If ErrHandl: RegExpExtract = CVErr (xlErrValue) End Function Pudemu avà chjude l'Editor Visual Basic è vultà in Excel per pruvà a nostra nova funzione. A so sintassi hè a seguente:
=RegExpExtract (Txt; Pattern; Elementu)
induva
- txt - una cellula cù u testu chì avemu verificatu è da quale vulemu estrarre a substringa chì avemu bisognu
- mudellu - maschera (mudellu) per a ricerca di substring
- Item - u numeru di sequenza di a substringa da esse estratta, s'ellu ci sò parechji di elli (se ùn hè micca specificatu, allora a prima occorrenza hè visualizata)
A cosa più interessante quì, sicuru, hè Pattern - un mudellu di stringa di caratteri speciali "in a lingua" di RegExp, chì specifica ciò chì esattamente è induve vulemu truvà. Eccu i più basi per avè principiatu:
| Pattern | Description |
| . | U più simplice hè un puntu. Corrisponde à qualsiasi caratteru in u mudellu à a pusizione specifica. |
| s | Ogni caratteru chì s'assumiglia à un spaziu (spaziu, tabulazione o salta di linea). |
| S | Un anti-variante di u mudellu precedente, vale à dì ogni caratteru chì ùn hè micca spaziu in biancu. |
| d | Ogni numaru |
| D | Un anti-variante di u precedente, vale à dì ogni cifru NOT |
| w | Ogni caratteru latinu (AZ), cifru, o underscore |
| W | Un antivariante di a precedente, vale à dì micca latinu, micca un numeru è micca un underscore. |
| [caratteri] | In parentesi quadrate, pudete specificà unu o più caratteri permessi à a pusizione specifica in u testu. Per esempiu arte currisponde à qualsiasi di e parolle: tàvula or sedia. Ùn pudete ancu micca enumerate i caratteri, ma stabiliscenu cum'è un intervallu separatu da un trattino, vale à dì invece di [ABDCDEF] scrivemu [AF]. o invece [4567] Intruduce [-4 7]. Per esempiu, per designà tutti i caratteri cirillichi, pudete aduprà u mudellu [a-yaA-YayoYo]. |
| [^caratteri] | Se dopu a parentesi quadrate di apertura aghjunghje u simbulu "coperchio" ^, allura l'inseme acquisterà u significatu oppostu - à a pusizione specifica in u testu, tutti i caratteri seranu permessi, salvu quelli chì sò listati. Iè, mudellu [^ЖМ]ut truvà Caminu or Sustancia or La Ragione, ma micca Scary or Mututu, p.e. |
| | | Operatore booleanu OR (OR) per verificà qualsiasi di i criteri specificati. Per esempiu (daGio|sancu | fattura) cercherà u testu per qualsiasi di e parolle specificate. Di genere, un inseme di opzioni hè chjusu in parentesi. |
| ^ | U principiu di a linea |
| $ | Fine di ligna |
| b | Fine di a parolla |
Sè avemu cercatu un certu numaru di caratteri, per esempiu, un codice postale di sei cifre o tutti i codici di prudutti di trè lettere, allora venemu à u salvamentu. quantificatori or quantificatori sò espressioni spiciali chì specificanu u numeru di caratteri da circà. I quantificatori sò applicati à u caratteru chì vene prima:
| Quantu | Description |
| ? | Zeru o una occorrenza. Per esempiu .? significherà qualsiasi caratteru o a so assenza. |
| + | Una o più entrate. Per esempiu d+ significa ogni numeru di cifre (vale à dì ogni numeru trà 0 è infinitu). |
| * | Zero o più occurrence, vale à dì ogni quantità. Allora s* significa un numeru di spazii o senza spazii. |
| {nùmeru} or {numeru1,numeru2} | Sè avete bisognu di specificà un nùmeru strettu definitu d'occurrence, allora hè specificatu in curly braces. Per esempiu d {6} significa strettamente sei cifre, è u mudellu s {2,5} - dui à cinque spazii |
Avà andemu à a parte più interessante - un analisi di l'applicazione di a funzione creata è ciò chì avemu amparatu nantu à mudelli nantu à esempi pratichi da a vita.
Estrazione di numeri da u testu
Per principià, analizzemu un casu simplice - avete bisognu di caccià u primu numeru da u porridge alfanumericu, per esempiu, a putenza di l'alimentazione ininterrotta da a lista di prezzi:
A logica daretu à l'espressione regulare hè simplice: d significa ogni cifru, è u quantificatore + dice chì u so numeru deve esse unu o più. U doppiu minus davanti à a funzione hè necessariu per "à a mosca" cunvertisce i caratteri estratti in un numeru sanu da u numeru cum'è testu.
Codice postale
À u primu sguardu, tuttu hè simplice quì - cerchemu esattamente sei cifre in una fila. Avemu aduprà un caratteru speciale d per i numeri è quantificatori 6 {} per u numeru di caratteri:
In ogni casu, una situazione hè pussibule quandu, à a manca di l'indici in a linea, ci hè un altru grande inseme di numeri in una fila (numeru di telefunu, TIN, contu bancariu, etc.) Allora a nostra stagione regular tirarà fora i primi 6. numeri da ellu, vale à dì ùn funziona micca bè:
Per impediscenu chì questu succede, avemu bisognu di aghjunghje un modificatore intornu à i bordi di a nostra espressione regulare b significà a fine di una parolla. Questu hà da esse chjaru à Excel chì u fragmentu (indici) chì avemu bisognu deve esse una parolla separata, è micca parte di un altru fragmentu (numeru di telefunu):
Téléphone
U prublema cù truvà un numeru di telefunu in u testu hè chì ci sò tanti ozzione per scrive numeri - cù è senza trattini, attraversu spazii, cù o senza un codice di regione in parentesi, etc. Per quessa, in u mo parè, hè più faciule per esse. prima pulisce tutti questi caratteri da u testu fonte utilizendu parechje funzioni nidificate SUSTITUTU (SUBSTITUT)cusì ch'ellu si appiccica inseme in un unicu tutale, è dopu cù un regulare primitivu d {11} tira 11 cifre in una fila:
ITN
Hè un pocu più cumplicatu quì, perchè TIN (in u nostru paese) pò esse 10-cifre (per e persone giuridiche) o 12-cifre (per individui). Se ùn truvate micca difetti soprattuttu, allora hè abbastanza pussibule di esse cuntentu cù u regula d {10,12}, ma, in modu strettu, tirarà fora tutti i numeri da 10 à 12 caratteri, vale à dì è inseritu erroneamente 11 cifre. Saria più currettu aduprà dui mudelli cunnessi da un operatore OR logicu | (barra verticale):
Per piacè nutate chì in a quistione circhemu prima numeri 12-bit, è solu dopu per numeri 10-bit. Se scrivemu a nostra espressione regulare à l'inversu, allora tirarà fora per tutti, ancu longu TIN 12-bit, solu i primi 10 caratteri. Vale à dì, dopu chì a prima cundizione hè attivata, più verificazione ùn hè più fatta:
Questa hè a diferenza fundamentale trà l'operatore | da una funzione logica standard excel OR (OR), induve rearrangendu l'argumenti ùn cambia micca u risultatu.
SKU di produttu
In parechje cumpagnie, identificatori unichi sò attribuiti à merchenzie è servizii - articuli, codici SAP, SKU, etc. Se ci hè una logica in a so notazione, ponu esse facilmente tirati fora di qualsiasi testu cù espressioni regulari. Per esempiu, se sapemu chì i nostri articuli sò sempre custituiti da trè lettere capitali inglesi, un trattino è un numeru di trè cifre dopu, allora:
A logica daretu à u mudellu hè simplice. [AZ] - significa ogni lettere majuscule di l'alfabetu latinu. U prossimu quantificatore 3 {} dice chì hè impurtante per noi chì ci sò esattamente trè tali lettere. Dopu à u trattino, aspittemu trè cifre, cusì aghjunghje à a fine d {3}
A quantità di soldi
In modu simili à u paràgrafu precedente, pudete ancu tirà i prezzi (costu, IVA ...) da a descrizzione di e merchenzie. Se l'ammonti monetari, per esempiu, sò indicati cù un trattino, allora:
Pattern d cun quantificatore + cerca ogni numeru finu à un trattino, è d {2} cercherà centesimi (dui cifre) dopu.
Sè avete bisognu di estratti micca i prezzi, ma l'IVA, pudete aduprà u terzu argumentu facultativu di a nostra funzione RegExpExtract, chì specifica u numeru ordinale di l'elementu per esse estratti. E, sicuru, pudete rimpiazzà a funzione SUSTITUTU (SUBSTITUT) in i risultati, trattenu à u separatore decimale standard è aghjunghje un doppiu minus à u principiu per chì Excel interpreti l'IVA truvata cum'è un numeru normale:
Numeri di targhe di vittura
Se ùn pigliate micca veiculi speciali, rimorchi è altre motociclette, allora u numeru di vittura standard hè analizatu secondu u principiu "lettera - trè numeri - duie lettere - codice di regione". Inoltre, u codice di a regione pò esse di 2 o 3 cifre, è solu quelli chì sò simili in l'apparenza à l'alfabetu latinu sò usati com'è lettere. Cusì, a seguente spressione regulare ci aiuterà à estrae numeri da u testu:
tempu
Per caccià u tempu in u formatu HH:MM, l'espressione regulare seguente hè adattata:
Dopu à u fragmentu di u colon [0-5] d, cum'è hè faciule per capisce, stabilisce qualsiasi numeru in a gamma 00-59. Prima di u colon in parentesi, dui mudelli travaglianu, siparati da un OR logicu (pipe):
- [0-1] d - ogni numeru in u range 00-19
- 2 [0-3] - ogni numeru in u range 20-23
À u risultatu ottenutu, pudete ancu applicà a funzione standard di Excel POPULU (SQUADRA)a cunvertisce in un furmatu di tempu chì hè capisci à u prugrammu è adattatu per più calculi.
Verificate a password
Suppone chì avemu bisognu di verificà a lista di password inventati da l'utilizatori per a correzione. Sicondu e nostre regule, i password ponu cuntene solu lettere inglesi (minuscule o maiuscule) è numeri. I spazii, i underscore è altri segni di puntuazione ùn sò micca permessi.
A verificazione pò esse urganizata cù a seguente espressione regulare simplice:
In fatti, cù un tali mudellu avemu bisognu chì trà u principiu (^) è finisce ($) in u nostru testu ci era solu caratteri da u settore datu in parentesi quadrate. Sè ancu bisognu di verificà a lunghezza di a password (per esempiu, almenu 6 caratteri), allora u quantificatore + pò esse rimpiazzatu da l'intervallu "sei o più" in a forma {6,}:
A cità da l'indirizzu
Diciamu chì avemu bisognu di tirà a cità da a barra di indirizzu. U prugramma rigulari aiuterà, estrattendu u testu da "g". à a virgola dopu:
Fighjemu un ochju più vicinu à stu mudellu.
Sè avete lettu u testu sopra, allora avete digià capitu chì certi caratteri in l'espressioni regulare (periodi, asterischi, segni di dòlar, etc.) anu un significatu particulari. Sè avete bisognu di circà sti caratteri stessi, allora sò preceduti da un backslash (qualchì volta chjamatu schermatura). Dunque, quandu cercate u frammentu "g". avemu da scrive in spressione regulare Mr. s'è no circamu un plus, allura + etc.
I seguenti dui caratteri in u nostru mudellu, u puntu è l'asteriscu quantificatore, stanu per qualsiasi numeru di qualsiasi caratteri, vale à dì ogni nome di cità.
Ci hè una virgola à a fine di u mudellu, perchè cerchemu u testu da "g". à una virgola. Ma pò esse parechje virgule in u testu, nò ? Ùn solu dopu à a cità, ma ancu dopu à a strada, case, etc. In quale di elli a nostra dumanda ferma? Hè per quessa chì u puntu d'interrogazione hè. Senza ellu, a nostra espressione regulare tirava fora a stringa più longa pussibule:
In termini di espressioni rigulari, un tali mudellu hè "greedy". Per curregà a situazione, hè necessariu un segnu d'interrogazione - face u quantificatore dopu à quale si trova "avara" - è a nostra dumanda piglia u testu solu finu à a prima virgola di cuntrariu dopu à "g.":
Filename da u percorsu sanu
Una altra situazione assai cumuna hè di caccià u nome di u schedariu da u percorsu sanu. Una semplice espressione regulare di a forma aiuterà quì:
U truccu quì hè chì a ricerca, in fattu, si trova in a direzzione opposta - da a fine à u principiu, perchè à a fine di u nostru mudellu hè $, è circhemu tuttu prima finu à u primu backslash da a diritta. U backslash hè scappatu, cum'è u puntu in l'esempiu precedente.
PS
"Versu a fine" Vogliu chjarificà chì tuttu ciò chì sopra hè una piccula parte di tutte e pussibulità chì l'espressioni regulare furnisce. Ci sò assai caratteri speciali è regule per u so usu, è libri interi sò stati scritti nantu à questu tema (recumandemu almenu questu per un principiu). In una certa manera, scrive spressione regulare hè quasi un arte. Quasi sempre, una espressione regulare inventata pò esse migliurata o supplementata, facendu più elegante o capace di travaglià cù una gamma più larga di dati di input.
Per analizà è analizà l'espressioni regulare di l'altri o debug u vostru propiu, ci sò parechji servizii in linea convenienti: RegEx101, RegExr e di più
Sfurtunatamente, micca tutte e funziunalità di l'espressioni rigulari classici sò supportati in VBA (per esempiu, ricerca inversa o classi POSIX) è ponu travaglià cù Cyrillic, ma pensu chì ciò chì ci hè abbastanza per a prima volta per piacè.
Se ùn site micca novu in u tema, è avete qualcosa di sparte, lasciate espressioni regulari utili quandu travagliate in Excel in i cumenti sottu. Una mente hè bona, ma dui stivali sò un paru!
- Sustituisce è pulisce u testu cù a funzione SOSTITUTI
- Ricerca è evidenziazione di caratteri latini in u testu
- Cerca u testu simili più vicinu (Ivanov = Ivonov = Ivanof, etc.)