Analyser du texte avec des expressions régulières (RegExp) dans Excel

L'une des tâches les plus chronophages et les plus frustrantes lorsque vous travaillez avec du texte dans Excel est analyse – analyser la « bouillie » alphanumérique en composants et en extraire les fragments dont nous avons besoin. Par exemple:

• extraire le code postal de l'adresse (c'est bien si le code postal est toujours au début, mais si ce n'est pas le cas ?)
• trouver le numéro et la date de la facture à partir de la description du paiement dans le relevé bancaire
• extraction de TIN à partir de descriptions hétéroclites d'entreprises dans la liste des contreparties
• rechercher un numéro de voiture ou un numéro d'article dans la description, etc.
  

Habituellement, dans de tels cas, après une demi-heure de sélection manuelle morne dans le texte, des pensées commencent à venir à l'esprit d'une manière ou d'une autre pour automatiser ce processus (surtout s'il y a beaucoup de données). Il existe plusieurs solutions et avec différents degrés de complexité-efficacité :

• Utilisez fonctions de texte Excel intégrées pour rechercher-couper-coller du texte : LEVSIMV (LA GAUCHE), DROIT (DROITE), PSTR (milieu), STsEPIT (ENCHAÎNER) et ses analogues, COMBINER (TEXTE CONJOINT), EXACT (EXACT) etc. Cette méthode est bonne s'il y a une logique claire dans le texte (par exemple, l'index est toujours au début de l'adresse). Sinon, les formules deviennent beaucoup plus compliquées et, parfois, il s'agit même de formules matricielles, ce qui ralentit grandement sur les grandes tables.
• En utilisant comme opérateur de similarité de texte à partir de Visual Basic enveloppé dans une fonction de macro personnalisée. Cela vous permet d'implémenter une recherche plus flexible en utilisant des caractères génériques (*, #, ?, etc.). Malheureusement, cet outil ne peut pas extraire la sous-chaîne souhaitée du texte - vérifiez uniquement si elle y est contenue.

En plus de ce qui précède, il existe une autre approche très connue dans les cercles étroits des programmeurs professionnels, des développeurs Web et d'autres techniciens - c'est expressions régulières (Expressions régulières = RegExp = "regexps" = "regulars"). Tout simplement, RegExp est un langage où des caractères spéciaux et des règles sont utilisés pour rechercher les sous-chaînes nécessaires dans le texte, les extraire ou les remplacer par un autre texte. Les expressions régulières sont un outil très puissant et magnifique qui surpasse toutes les autres façons de travailler avec du texte d'un ordre de grandeur. De nombreux langages de programmation (C#, PHP, Perl, JavaScript…) et éditeurs de texte (Word, Notepad++…) supportent les expressions régulières.

Microsoft Excel n'a malheureusement pas de support RegExp prêt à l'emploi, mais cela peut être facilement résolu avec VBA. Ouvrez Visual Basic Editor à partir de l'onglet promoteur (Développeur) ou raccourci clavier autre+F11. Insérez ensuite le nouveau module via le menu Insertion – Module et copiez-y le texte de la fonction macro suivante :

Fonction publique RegExpExtract(Text As String, Pattern As String, Élément facultatif As Integer = 1) As String On Error GoTo ErrHandl Set regex = CreateObject("VBScript.RegExp") regex.Pattern = Pattern regex.Global = True If regex.Test (Texte) Alors Set matches = regex.Execute(Text) RegExpExtract = matches.Item(Item - 1) Quitter la fonction End If ErrHandl : RegExpExtract = CVErr(xlErrValue) End Function  

Nous pouvons maintenant fermer Visual Basic Editor et retourner dans Excel pour essayer notre nouvelle fonctionnalité. Sa syntaxe est la suivante :

=RegExpExtract( Txt ; Modèle ; Élément )

De

• txt – une cellule avec le texte que nous vérifions et dont nous voulons extraire la sous-chaîne dont nous avons besoin
• modèle – masque (motif) pour la recherche de sous-chaînes
• Produit – le numéro d'ordre de la sous-chaîne à extraire, s'il y en a plusieurs (si non spécifié, alors la première occurrence est affichée)

La chose la plus intéressante ici, bien sûr, est Pattern - un modèle de chaîne de caractères spéciaux "dans la langue" de RegExp, qui spécifie exactement ce que nous voulons trouver et où nous voulons le trouver. Voici les plus basiques pour vous aider à démarrer :

Si nous recherchons un certain nombre de caractères, par exemple un code postal à six chiffres ou tous les codes de produit à trois lettres, nous venons à la rescousse quantificateurs or quantificateurs sont des expressions spéciales qui spécifient le nombre de caractères à rechercher. Les quantificateurs sont appliqués au caractère qui le précède :

Passons maintenant à la partie la plus intéressante - une analyse de l'application de la fonction créée et de ce que nous avons appris sur les modèles sur des exemples pratiques tirés de la vie.

Extraire des nombres d'un texte

Pour commencer, analysons un cas simple - vous devez extraire le premier chiffre de la bouillie alphanumérique, par exemple, la puissance des alimentations sans coupure de la liste de prix :

La logique derrière l'expression régulière est simple : d signifie n'importe quel chiffre, et le quantificateur + dit que leur nombre devrait être un ou plusieurs. Le double moins devant la fonction est nécessaire pour convertir "à la volée" les caractères extraits en un nombre complet à partir du nombre sous forme de texte.

Code postal

À première vue, tout est simple ici - nous recherchons exactement six chiffres d'affilée. Nous utilisons un caractère spécial d pour chiffre et quantificateur sept {} pour le nombre de caractères :

Cependant, une situation est possible lorsque, à gauche de l'index dans la ligne, il y a un autre grand ensemble de chiffres d'affilée (numéro de téléphone, TIN, compte bancaire, etc.). Ensuite, notre saison régulière retirera les 6 premiers chiffres de celui-ci, c'est-à-dire ne fonctionnera pas correctement :

Pour éviter que cela ne se produise, nous devons ajouter un modificateur autour des bords de notre expression régulière b signifiant la fin d'un mot. Cela indiquera clairement à Excel que le fragment (index) dont nous avons besoin doit être un mot séparé et non faire partie d'un autre fragment (numéro de téléphone) :

Téléphone

Le problème avec la recherche d'un numéro de téléphone dans le texte est qu'il y a tellement d'options pour écrire des chiffres - avec et sans tirets, entre espaces, avec ou sans code de région entre parenthèses, etc. Par conséquent, à mon avis, il est plus facile de nettoyez d'abord tous ces caractères du texte source en utilisant plusieurs fonctions imbriquées REMPLACER (REMPLAÇANT)de sorte qu'il colle ensemble en un seul tout, puis avec une primitive régulière j{11} tirez 11 chiffres d'affilée:

MII

C'est un peu plus compliqué ici, car le TIN (dans Notre Pays) peut être à 10 chiffres (pour les personnes morales) ou à 12 chiffres (pour les particuliers). Si vous ne trouvez pas à redire surtout, alors il est tout à fait possible de se contenter du régulier j{10,12}, mais, à proprement parler, il retirera tous les nombres de 10 à 12 caractères, c'est-à-dire et saisis par erreur 11 chiffres. Il serait plus correct d'utiliser deux motifs reliés par un opérateur logique OU | (barre verticale):

Veuillez noter que dans la requête, nous recherchons d'abord les nombres 12 bits, puis seulement les nombres 10 bits. Si nous écrivons notre expression régulière dans l'autre sens, elle ne sortira pour tout le monde, même les longs TIN 12 bits, que les 10 premiers caractères. C'est-à-dire qu'après le déclenchement de la première condition, aucune autre vérification n'est effectuée :

C'est la différence fondamentale entre l'opérateur | à partir d'une fonction logique Excel standard OR (OR), où la réorganisation des arguments ne modifie pas le résultat.

SKU de produit

Dans de nombreuses entreprises, des identifiants uniques sont attribués aux biens et services - articles, codes SAP, SKU, etc. S'il y a une logique dans leur notation, ils peuvent être facilement extraits de n'importe quel texte à l'aide d'expressions régulières. Par exemple, si nous savons que nos articles se composent toujours de trois lettres anglaises majuscules, d'un trait d'union et d'un nombre à trois chiffres, alors :

La logique derrière le modèle est simple. [A à Z] – désigne toutes les lettres majuscules de l'alphabet latin. Le quantificateur suivant sept {} dit qu'il est important pour nous qu'il y ait exactement trois de ces lettres. Après le trait d'union, nous attendons trois chiffres, nous ajoutons donc à la fin j{3}

Montants en espèces

De la même manière qu'au paragraphe précédent, vous pouvez également extraire les prix (coûts, TVA ...) de la description des marchandises. Si les montants monétaires, par exemple, sont indiqués par un trait d'union, alors :

Patron de Couture d avec quantificateur + recherche n'importe quel nombre jusqu'à un trait d'union, et j{2} cherchera ensuite des centimes (deux chiffres).

Si vous avez besoin d'extraire non pas les prix, mais la TVA, vous pouvez utiliser le troisième argument optionnel de notre fonction RegExpExtract, qui spécifie le numéro ordinal de l'élément à extraire. Et, bien sûr, vous pouvez remplacer la fonction REMPLACER (REMPLAÇANT) dans les résultats, tiret au séparateur décimal standard et ajoutez un double moins au début pour qu'Excel interprète la TVA trouvée comme un nombre normal :

Numéros de plaque de voiture

Si vous ne prenez pas de véhicules spéciaux, remorques et autres motos, le numéro de voiture standard est analysé selon le principe « lettre – trois chiffres – deux lettres – code région ». De plus, le code de région peut être à 2 ou 3 chiffres, et seuls ceux dont l'apparence est similaire à l'alphabet latin sont utilisés comme lettres. Ainsi, l'expression régulière suivante nous aidera à extraire des nombres du texte :

Temps

Pour extraire l'heure au format HH:MM, l'expression régulière suivante convient :

Après fragment de côlon [0-5]j, comme il est facile à comprendre, définit n'importe quel nombre dans la plage 00-59. Avant les deux-points entre parenthèses, deux modèles fonctionnent, séparés par un OU logique (tuyau):

• [0-1]j – n'importe quel nombre dans la plage 00-19
• 2[0-3] – n'importe quel nombre dans la plage 20-23

Au résultat obtenu, vous pouvez en plus appliquer la fonction Excel standard (ÉQUIPE)pour le convertir dans un format d'heure compréhensible pour le programme et adapté à d'autres calculs.

Vérification de mot de passe

Supposons que nous devions vérifier l'exactitude de la liste des mots de passe inventés par les utilisateurs. Selon nos règles, les mots de passe ne peuvent contenir que des lettres anglaises (minuscules ou majuscules) et des chiffres. Les espaces, traits de soulignement et autres signes de ponctuation ne sont pas autorisés.

La vérification peut être organisée à l'aide de l'expression régulière simple suivante :

En fait, avec un tel motif, nous exigeons qu'entre le début (^) et fin ($) dans notre texte, il n'y avait que des caractères de l'ensemble donné entre crochets. Si vous devez également vérifier la longueur du mot de passe (par exemple, au moins 6 caractères), alors le quantificateur + peut être remplacé par l'intervalle "six ou plus" sous la forme {6,}:

Ville d'adresse

Disons que nous devons retirer la ville de la barre d'adresse. Le programme régulier vous aidera, en extrayant le texte de "g". à la virgule suivante :

Examinons de plus près ce modèle.

Si vous avez lu le texte ci-dessus, vous avez déjà compris que certains caractères des expressions régulières (points, astérisques, signes dollar, etc.) ont une signification particulière. Si vous devez rechercher ces caractères eux-mêmes, ils sont précédés d'une barre oblique inverse (parfois appelée blindage). Par conséquent, lors de la recherche du fragment "g." il faut écrire en expression régulière Monsieur si nous recherchons un plus, alors + et ainsi de suite

Les deux caractères suivants de notre modèle, le point et l'astérisque quantificateur, représentent n'importe quel nombre de caractères, c'est-à-dire n'importe quel nom de ville.

Il y a une virgule à la fin du modèle, car nous recherchons du texte à partir de "g". à une virgule. Mais il peut y avoir plusieurs virgules dans le texte, n'est-ce pas ? Non seulement après la ville, mais aussi après la rue, les maisons, etc. Sur lesquels s'arrêtera notre requête ? C'est à ça que sert le point d'interrogation. Sans cela, notre expression régulière extrairait la chaîne la plus longue possible :

En termes d'expressions régulières, un tel modèle est "gourmand". Pour corriger la situation, un point d'interrogation est nécessaire - il rend le quantificateur après quoi il reste "avare" - et notre requête ne prend le texte que jusqu'à la première contre-virgule après "g." :

Nom de fichier à partir du chemin complet

Une autre situation très courante consiste à extraire le nom du fichier du chemin complet. Une simple expression régulière du formulaire aidera ici:

L'astuce ici est que la recherche, en fait, se produit dans la direction opposée - de la fin au début, car à la fin de notre modèle se trouve $, et nous recherchons tout ce qui précède jusqu'à la première barre oblique inverse à partir de la droite. La barre oblique inverse est échappée, comme le point dans l'exemple précédent.

PS

"Vers la fin", je tiens à préciser que tout ce qui précède n'est qu'une petite partie de toutes les possibilités offertes par les expressions régulières. Il y a beaucoup de caractères spéciaux et de règles pour leur utilisation, et des livres entiers ont été écrits sur ce sujet (je recommande au moins celui-ci pour commencer). D'une certaine manière, écrire des expressions régulières est presque un art. Presque toujours, une expression régulière inventée peut être améliorée ou complétée, la rendant plus élégante ou capable de fonctionner avec une plus large gamme de données d'entrée.

Pour analyser et analyser les expressions régulières d'autres personnes ou déboguer les vôtres, il existe plusieurs services en ligne pratiques : RegEx101, RegExr et plus encore

Malheureusement, toutes les fonctionnalités des expressions régulières classiques ne sont pas prises en charge en VBA (par exemple, la recherche inversée ou les classes POSIX) et peuvent fonctionner avec le cyrillique, mais je pense que ce qu'il y a est suffisant pour la première fois pour vous plaire.

Si vous n'êtes pas nouveau sur le sujet et que vous avez quelque chose à partager, laissez les expressions régulières utiles lorsque vous travaillez dans Excel dans les commentaires ci-dessous. Un esprit c'est bien, mais deux bottes font une paire !

• Remplacer et nettoyer du texte avec la fonction SUBSTITUTE
• Recherche et mise en évidence de caractères latins dans le texte
• Rechercher le texte similaire le plus proche (Ivanov = Ivonov = Ivanof, etc.)