Contenuti
Una delle attività più lunghe e frustranti quando si lavora con il testo in Excel è l'analisi – analizzare il "porridge" alfanumerico in componenti ed estrarne i frammenti di cui abbiamo bisogno. Per esempio:
- estraendo il CAP dall'indirizzo (va bene se il CAP è sempre all'inizio, ma se non lo fosse?)
- trovare il numero e la data della fattura dalla descrizione del pagamento nell'estratto conto
- estrazione di TIN da descrizioni eterogenee di società nell'elenco delle controparti
- cercare un numero di auto o un numero di articolo nella descrizione, ecc.
Di solito in questi casi, dopo mezz'ora di triste selezione manuale del testo, i pensieri iniziano a venire in mente in qualche modo per automatizzare questo processo (soprattutto se ci sono molti dati). Esistono diverse soluzioni e con diversi gradi di complessità-efficienza:
- Usa il funzioni di testo Excel integrate per cercare testo incollato: LEVSIMV (SINISTRA), DESTRA (GIUSTO), PSTR (metà), STsEPIT (CONCATENARE) e suoi analoghi, COMBINE (TESTO COMUNE), ESATTO (ESATTO) ecc. Questo metodo è utile se c'è una logica chiara nel testo (ad esempio, l'indice è sempre all'inizio dell'indirizzo). In caso contrario, le formule diventano molto più complicate e, a volte, si arriva anche a formule di matrice, che rallentano notevolmente su tabelle di grandi dimensioni.
- utilizzando come l'operatore di somiglianza del testo da Visual Basic racchiuso in una funzione macro personalizzata. Ciò consente di implementare una ricerca più flessibile utilizzando caratteri jolly (*, #,?, ecc.). Sfortunatamente, questo strumento non è in grado di estrarre la sottostringa desiderata dal testo, controlla solo se è contenuta in esso.
In aggiunta a quanto sopra, c'è un altro approccio che è molto noto in circoli ristretti di programmatori professionisti, sviluppatori web e altri tecnici: questo è espressioni regolari (Espressioni regolari = RegExp = “regexps” = “regulars”). In poche parole, RegExp è un linguaggio in cui vengono utilizzati caratteri e regole speciali per cercare le sottostringhe necessarie nel testo, estrarle o sostituirle con altro testo. Le espressioni regolari sono uno strumento molto potente e bello che supera di un ordine di grandezza tutti gli altri modi di lavorare con il testo. Molti linguaggi di programmazione (C#, PHP, Perl, JavaScript...) e editor di testo (Word, Notepad++...) supportano le espressioni regolari.
Microsoft Excel sfortunatamente non ha il supporto RegExp pronto all'uso, ma questo può essere facilmente risolto con VBA. Apri l'editor di Visual Basic dalla scheda sviluppatore (Sviluppatore) o scorciatoia da tastiera altro+F11. Quindi inserire il nuovo modulo tramite il menu Inserisci – Modulo e copia lì il testo della seguente funzione macro:
Funzione pubblica 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 (Testo) Quindi Imposta corrispondenze = regex.Execute(Testo) RegExpExtract = partite.Item(Item - 1) Esci dalla funzione End If ErrHandl: RegExpExtract = CVErr(xlErrValue) End Function Ora possiamo chiudere l'editor di Visual Basic e tornare a Excel per provare la nostra nuova funzionalità. La sua sintassi è la seguente:
=RegExpExtract( Txt ; Modello ; Voce )
where
- txt – una cella con il testo che stiamo controllando e da cui vogliamo estrarre la sottostringa di cui abbiamo bisogno
- modello – maschera (modello) per la ricerca di sottostringhe
- Articolo – il numero progressivo della sottostringa da estrarre, se ce ne sono più (se non specificato, viene visualizzata la prima occorrenza)
La cosa più interessante qui, ovviamente, è Pattern, una stringa modello di caratteri speciali "nella lingua" di RegExp, che specifica cosa esattamente e dove vogliamo trovare. Ecco i più semplici per iniziare:
| Cartamodello | Descrizione |
| . | Il più semplice è un punto. Corrisponde a qualsiasi carattere nel modello nella posizione specificata. |
| s | Qualsiasi carattere che assomigli a uno spazio (spazio, tabulazione o interruzione di riga). |
| S | Un'anti-variante del modello precedente, ovvero qualsiasi carattere non di spazi bianchi. |
| d | Qualsiasi numero |
| D | Un'antivariante della precedente, cioè qualsiasi cifra NOT |
| w | Qualsiasi carattere latino (AZ), cifra o trattino basso |
| W | Un antivariante del precedente, cioè non latino, non un numero e non un trattino basso. |
| [caratteri] | Tra parentesi quadre, puoi specificare uno o più caratteri consentiti nella posizione specificata nel testo. Per esempio Arte corrisponderà a una qualsiasi delle parole: tavolo or Bisogna. Inoltre non puoi enumerare i caratteri, ma impostarli come un intervallo separato da un trattino, cioè invece di [ABDCDEF] scrivere [AF]. o invece , introdurre [-4 7]. Ad esempio, per designare tutti i caratteri cirillici, puoi utilizzare il modello [a-yaA-YayoYo]. |
| [^caratteri] | Se dopo la parentesi quadra di apertura aggiungere il simbolo “coperchio” ^, quindi il set acquisirà il significato opposto: nella posizione specificata nel testo, saranno consentiti tutti i caratteri, ad eccezione di quelli elencati. Sì, modello [^ЖМ]ut troverà sentiero or Sostanza or dimenticare, Ma non Pauroso or Coraggio, per esempio. |
| | | Operatore booleano OR (O) per verificare uno qualsiasi dei criteri specificati. Per esempio (сgio|spari|fattura) cercherà nel testo una qualsiasi delle parole specificate. In genere, un insieme di opzioni è racchiuso tra parentesi. |
| ^ | Inizio linea |
| $ | Fine della riga |
| b | Fine della parola |
Se stiamo cercando un certo numero di caratteri, ad esempio un codice postale di sei cifre o tutti i codici prodotto di tre lettere, allora veniamo in soccorso quantificatori or quantificatori sono espressioni speciali che specificano il numero di caratteri da cercare. I quantificatori vengono applicati al carattere che lo precede:
| quantori | Descrizione |
| ? | Zero o una occorrenza. Per esempio .? significherà qualsiasi carattere o la sua assenza. |
| + | Una o più voci. Per esempio d+ significa qualsiasi numero di cifre (ovvero qualsiasi numero compreso tra 0 e infinito). |
| * | Zero o più occorrenze, cioè qualsiasi quantità. Così s* indica un numero qualsiasi di spazi o nessuno spazio. |
| {numero} or {number1,number2} | Se è necessario specificare un numero rigorosamente definito di occorrenze, viene specificato tra parentesi graffe. Per esempio d{6} significa rigorosamente sei cifre e il modello s{2,5} – da due a cinque spazi |
Passiamo ora alla parte più interessante: un'analisi dell'applicazione della funzione creata e di ciò che abbiamo appreso sui modelli su esempi pratici dalla vita.
Estrazione di numeri dal testo
Per cominciare, analizziamo un caso semplice: è necessario estrarre il primo numero dal porridge alfanumerico, ad esempio la potenza dei gruppi di continuità dal listino:
La logica dietro l'espressione regolare è semplice: d indica qualsiasi cifra e il quantificatore + dice che il loro numero dovrebbe essere uno o più. Il doppio meno davanti alla funzione è necessario per convertire "al volo" i caratteri estratti in un numero intero dal numero come testo.
Codice postale
A prima vista, qui tutto è semplice: stiamo cercando esattamente sei cifre di seguito. Usiamo un carattere speciale d per cifra e quantificatore 6 {} per il numero di caratteri:
Tuttavia, una situazione è possibile quando, a sinistra dell'indice nella riga, c'è un altro grande insieme di numeri di fila (numero di telefono, TIN, conto bancario, ecc.) Quindi la nostra stagione regolare tirerà fuori i primi 6 cifre da esso, cioè non funzionerà correttamente:
Per evitare che ciò accada, dobbiamo aggiungere un modificatore attorno ai bordi della nostra espressione regolare b a significare la fine di una parola. Questo renderà chiaro a Excel che il frammento (indice) di cui abbiamo bisogno dovrebbe essere una parola separata e non parte di un altro frammento (numero di telefono):
Telefono
Il problema di trovare un numero di telefono nel testo è che ci sono così tante opzioni per scrivere i numeri – con e senza trattini, attraverso spazi, con o senza prefisso regionale tra parentesi, ecc. Pertanto, secondo me, è più facile prima ripulisci tutti questi caratteri dal testo sorgente usando diverse funzioni nidificate SOSTITUIRE (SOSTITUTO)in modo che si unisca in un tutto unico, e poi con un regolare primitivo d{11} estrarre 11 cifre di seguito:
ITN
È un po' più complicato qui, perché TIN (nel nostro Paese) può essere di 10 cifre (per le persone giuridiche) o di 12 cifre (per le persone fisiche). Se non trovi difetti in particolare, allora è del tutto possibile essere soddisfatto del normale d{10,12}, ma, a rigor di termini, estrarrà tutti i numeri da 10 a 12 caratteri, ovvero 11 cifre inserite erroneamente. Sarebbe più corretto utilizzare due modelli collegati da un operatore OR logico | (barra verticale):
Tieni presente che nella query cerchiamo prima i numeri a 12 bit e solo successivamente i numeri a 10 bit. Se scriviamo la nostra espressione regolare al contrario, verranno estratti per tutti, anche per TIN lunghi a 12 bit, solo i primi 10 caratteri. Cioè, dopo l'attivazione della prima condizione, l'ulteriore verifica non viene più eseguita:
Questa è la differenza fondamentale tra l'operatore | da una funzione logica excel standard OR (O), dove la riorganizzazione degli argomenti non cambia il risultato.
SKU del prodotto
In molte aziende, gli identificatori univoci vengono assegnati a beni e servizi: articoli, codici SAP, SKU, ecc. Se c'è una logica nella loro notazione, possono essere facilmente estratti da qualsiasi testo utilizzando espressioni regolari. Ad esempio, se sappiamo che i nostri articoli sono sempre composti da tre lettere inglesi maiuscole, un trattino e un successivo numero di tre cifre, allora:
La logica dietro il modello è semplice. [AZ] – indica qualsiasi lettera maiuscola dell'alfabeto latino. Il prossimo quantificatore 3 {} dice che è importante per noi che ci siano esattamente tre di queste lettere. Dopo il trattino, stiamo aspettando tre cifre, quindi aggiungiamo alla fine d{3}
Importi in contanti
In modo analogo al paragrafo precedente, puoi anche estrarre i prezzi (costi, IVA…) dalla descrizione della merce. Se gli importi monetari, ad esempio, sono indicati con un trattino, allora:
Cartamodello d con quantificatore + cerca qualsiasi numero fino a un trattino, e d{2} cercherà penny (due cifre) dopo.
Se devi estrarre non i prezzi, ma l'IVA, puoi utilizzare il terzo argomento facoltativo della nostra funzione RegExpExtract, che specifica il numero ordinale dell'elemento da estrarre. E, naturalmente, puoi sostituire la funzione SOSTITUIRE (SOSTITUTO) nei risultati, trattino il separatore decimale standard e aggiungi un doppio meno all'inizio in modo che Excel interpreti l'IVA trovata come un numero normale:
Numeri di targa dell'auto
Se non si trasportano veicoli speciali, rimorchi e altre motociclette, il numero dell'auto standard viene analizzato secondo il principio "lettera - tre numeri - due lettere - codice regionale". Inoltre, il codice regionale può essere di 2 o 3 cifre e come lettere vengono utilizzate solo quelle simili nell'aspetto all'alfabeto latino. Pertanto, la seguente espressione regolare ci aiuterà a estrarre i numeri dal testo:
Ora
Per estrarre l'ora nel formato HH:MM, è adatta la seguente espressione regolare:
Dopo il frammento del colon [0-5] d, come è facile intuire, imposta qualsiasi numero nell'intervallo 00-59. Prima dei due punti tra parentesi, funzionano due pattern, separati da un OR logico (pipe):
- [0-1] d – qualsiasi numero nell'intervallo 00-19
- 2[0-3] – qualsiasi numero nell'intervallo 20-23
Al risultato ottenuto è inoltre possibile applicare la funzione standard di Excel ORARIO (SQUADRA)per convertirlo in un formato orario comprensibile al programma e adatto per ulteriori calcoli.
Controllo password
Supponiamo di dover controllare la correttezza dell'elenco delle password inventate dagli utenti. Secondo le nostre regole, le password possono contenere solo lettere inglesi (minuscole o maiuscole) e numeri. Non sono ammessi spazi, trattini bassi e altri segni di punteggiatura.
Il controllo può essere organizzato utilizzando la seguente semplice espressione regolare:
Infatti, con un tale schema richiediamo che tra l'inizio (^) e fine ($) nel nostro testo c'erano solo caratteri dell'insieme indicato tra parentesi quadre. Se devi controllare anche la lunghezza della password (ad esempio, almeno 6 caratteri), allora il quantificatore + può essere sostituito dall'intervallo "sei o più" nel modulo {,}:
Città dall'indirizzo
Diciamo che dobbiamo estrarre la città dalla barra degli indirizzi. Il programma regolare aiuterà, estraendo il testo da "g". alla virgola successiva:
Diamo un'occhiata più da vicino a questo modello.
Se hai letto il testo sopra, allora hai già capito che alcuni caratteri nelle espressioni regolari (punti, asterischi, segni del dollaro, ecc.) Hanno un significato speciale. Se devi cercare questi personaggi stessi, sono preceduti da una barra rovesciata (a volte chiamata schermatura). Pertanto, quando si cerca il frammento "g". dobbiamo scrivere in espressione regolare Signore se stiamo cercando un vantaggio, allora + ecc.
I prossimi due caratteri nel nostro modello, il punto e l'asterisco del quantificatore, stanno per qualsiasi numero di qualsiasi carattere, cioè qualsiasi nome di città.
C'è una virgola alla fine del modello, perché stiamo cercando il testo da "g". ad una virgola. Ma ci possono essere diverse virgole nel testo, giusto? Non solo dopo la città, ma anche dopo la strada, le case, ecc. Su quale di esse si fermerà la nostra richiesta? Ecco a cosa serve il punto interrogativo. Senza di essa, la nostra espressione regolare estrarrebbe la stringa più lunga possibile:
In termini di espressioni regolari, un tale schema è "avido". Per correggere la situazione, è necessario un punto interrogativo – rende il quantificatore dopo il quale rimane “avaro” – e la nostra query prende il testo solo fino alla prima controvirgola dopo la “g.”:
Nome file dal percorso completo
Un'altra situazione molto comune consiste nell'estrarre il nome del file dal percorso completo. Una semplice espressione regolare del modulo aiuterà qui:
Il trucco qui è che la ricerca, in effetti, avviene nella direzione opposta, dalla fine all'inizio, perché alla fine del nostro modello è $, e stiamo cercando tutto prima fino alla prima barra rovesciata da destra. La barra rovesciata viene esclusa, come il punto nell'esempio precedente.
PS
“Verso la fine” Voglio chiarire che tutto quanto sopra è una piccola parte di tutte le possibilità che offrono le espressioni regolari. Ci sono molti caratteri speciali e regole per il loro utilizzo, e su questo argomento sono stati scritti interi libri (per cominciare consiglio almeno questo). In un certo senso, scrivere espressioni regolari è quasi un'arte. Quasi sempre, un'espressione regolare inventata può essere migliorata o integrata, rendendola più elegante o in grado di funzionare con una gamma più ampia di dati di input.
Per analizzare e analizzare le espressioni regolari di altre persone o eseguire il debug delle tue, sono disponibili diversi comodi servizi online: RegEx101, RegExr e ancora più
Sfortunatamente, non tutte le funzionalità delle espressioni regolari classiche sono supportate in VBA (ad esempio, ricerca inversa o classi POSIX) e possono funzionare con il cirillico, ma penso che quello che c'è sia sufficiente per la prima volta per farti piacere.
Se non sei nuovo all'argomento e hai qualcosa da condividere, lascia le espressioni regolari utili quando lavori in Excel nei commenti qui sotto. Una mente è buona, ma due stivali sono una coppia!
- Sostituzione e pulizia del testo con la funzione SOSTITUZIONE
- Ricerca ed evidenziazione dei caratteri latini nel testo
- Cerca il testo simile più vicino (Ivanov = Ivanov = Ivanof, ecc.)