Excel Regex Exemples: Utilisation d'expressions régulières dans des formules

Ne peut jamais comprendre pourquoi les expressions régulières ne sont pas soutenues dans les formules Excel? Maintenant, ils sont :) Avec nos fonctions personnalisées, vous pouvez facilement trouver, remplacer, extraire et supprimer les chaînes correspondant à un modèle spécifique.

À première vue, Excel a tout ce dont vous pourriez avoir besoin pour les manipulations de chaînes de texte. Hmm… qu'en est-il des expressions régulières? Oups, il n'y a pas de fonctions regex intégrées dans Excel. Mais personne ne dit que nous ne pouvons pas créer nos propres :)

• Qu'est-ce que Regex?
• Y a-t-il des fonctions regex dans Excel?
• Feuille de triche excel regex
• Fonctions VBA regex personnalisées
• Fonction de correspondance regex
• Fonction d'extrait regex
• Fonction Regex Remplacer
• Outils Regex - Expressions régulières dans Excel sans VBA

Qu'est-ce que l'expression régulière?

Une expression régulière (alias regex ou regexp ) est une séquence de caractères spécialement codée qui définit un modèle de recherche. En utilisant ce modèle, vous pouvez trouver une combinaison de caractères correspondante dans une entrée de données de chaîne ou valider. Si vous connaissez une notation générique, vous pouvez considérer les experts comme une version avancée des jarquettes.

Les expressions régulières ont leur propre syntaxe composée de caractères spéciaux, d'opérateurs et de constructions. Par exemple, [0-5] correspond à n'importe quel chiffre unique de 0 à 5.

Des expressions régulières sont utilisées dans de nombreux langages de programmation, notamment JavaScript et VBA. Ce dernier a un objet regexp spécial, que nous utiliserons pour créer nos fonctions personnalisées.

Excel supporte-t-il Regex?

Malheureusement, il n'y a pas de fonctions regex intégrées dans Excel. Pour pouvoir utiliser des expressions régulières dans vos formules, vous devrez créer votre propre fonction définie par l'utilisateur (basée sur VBA ou .NET) ou installer des outils tiers prenant en charge les regexex.

Feuille de triche excel regex

Qu'un modèle regex soit très simple ou extrêmement sophistiqué, il est construit en utilisant la syntaxe commune. Ce tutoriel ne vise pas à vous enseigner des expressions régulières. Pour cela, il existe de nombreuses ressources en ligne, à partir de tutoriels gratuits pour les débutants aux cours premium pour les utilisateurs avancés.

Ci-dessous, nous fournissons une référence rapide aux principaux modèles regex qui vous aideront à comprendre les bases. Cela peut également fonctionner comme votre feuille de triche lorsque vous étudiez d'autres exemples.

Si vous êtes à l'aise avec des expressions régulières, vous pouvez sauter directement aux fonctions regexp.

Personnages

Ce sont les modèles les plus fréquemment utilisés pour correspondre à certains caractères.

Modèle	Description	Exemple	Matchs
.	Chigarme généralable: correspond à n'importe quel personnage unique sauf une pause de ligne	.ot	point , chaud , pot , @ot
\d	Caractère de chiffre: tout chiffre à un seul 0 à 9	\d	Dans A1b , correspond à 1
\D	Tout personnage qui n'est pas un chiffre	\D	Dans A1b , correspond à A et B
\ s	Caractère de l'espace: espace, onglet, nouvelle ligne et retour en voiture	. \ s.	En 3 cents , correspond à 3 C
\ S	Tout personnage non blanc	\ S	Dans 30 cents , correspond à 30 et cents
\ w	Caractère de mot: toute lettre ASCII, chiffre ou soulignement	\ w	Dans 5_CATS *** , correspond à 5_cats
\ W	Tout caractère qui n'est pas un caractère alphanumérique ou un soulignement	\ W	Dans 5_CATS *** , correspond ***
\ t	Languette
\ n	Nouvelle ligne	\ n \ d	Dans la chaîne à deux lignes ci-dessous, correspond 10 5 chats 10 chiens
\	Échappe à la signification particulière d'un personnage, vous pouvez donc le rechercher	\. \ w \.	Échappe à une période pour que vous puissiez trouver le littéral "." Caractère dans une chaîne M. , Mme , Prof.

Classes de personnages

En utilisant ces modèles, vous pouvez faire correspondre les éléments de différents jeux de caractères.

Modèle	Description	Exemple	Matchs
[caractères]	Correspond à n'importe quel personnage unique entre crochets	d [oi] g	chien et creuser
[^ caractères]	Correspond à un seul personnage qui n'est pas entre parenthèses	d [^ oi] g	Correspond à Dag, Dug , D1G ne correspond pas à un chien et à creuser
[de - à]	Correspond à tout caractère dans la gamme entre les supports	[0-9] [AZ] [AZ]	Tout chiffre unique de 0 à 9 toute lettre minuscule une seule lettre majuscule

Quantificateurs

Les quantificateurs sont des expressions spéciales qui spécifient le nombre de caractères à correspondre. Un quantificateur s'applique toujours au personnage avant.

Modèle	Description	Exemple	Matchs
*	Zéro ou plus d'occurrences	1A *	1, 1A , 1AA, 1AAA , etc.
	Un ou plusieurs événements	pote	Dans le pot , correspond à PO en pauvre , correspond au caca
?	Zéro ou un événement	route	route, tige
*?	Zéro ou plus d'occurrences, mais aussi moins que possible	1a *?	En 1A , 1AA et 1AAA , correspond à 1a
?	Un ou plusieurs événements, mais aussi moins que possible	po?	En pot et pauvre , correspond à Po
??	Zéro ou un événement, mais aussi moins que possible	Roa ??	Sur la route et la canne , correspond à RO
{n}	Correspond au modèle précédent n fois	\ d {3}	Exactement 3 chiffres
{n,}	Correspond au modèle précédent n ou plus	\ d {3,}	3 chiffres ou plus
{n, m}	Correspond au modèle précédent entre n et m fois	\ d {3,5}	De 3 à 5 chiffres

Regroupement

Les constructions de regroupement sont utilisées pour capturer une sous-chaîne à partir de la chaîne source, vous pouvez donc effectuer une opération avec lui.

Syntaxe	Description	Exemple	Matchs
(modèle)	Capturer Group: capture une sous-chaîne correspondante et lui attribue un numéro ordinal	(\d )	Dans 5 chats et 10 chiens , capture 5 (groupe 1) et 10 (groupe 2)
(?:modèle)	Groupe non capturant: correspond à un groupe mais ne le capture pas	(\ d) (?: chiens)	Dans 5 chats et 10 chiens , capture 10
\ 1	Contenu du groupe 1	(\ d) \ (\ d) = \ 2 \ \ 1	Correspond à 5 10 = 10 5 et capture 5 et 10 , qui sont en capture de groupes
\ 2	Contenu du groupe 2

Ancres

Les ancres spécifient une position dans la chaîne d'entrée où chercher une correspondance.

Ancre	Description	Exemple	Matchs
^	Démarrage de la chaîne Remarque: [^ Bracets intérieurs] signifie "pas"	^ \ D	Tout nombre de chiffres au début de la chaîne. Dans 5 chats et 10 chiens , correspond 5
$	Fin de chaîne	\ d $	Tout nombre de chiffres à la fin de la chaîne. En 10 plus 5 en donne 15 , matchs 15
\ b	Limite de mot	\ bjoy \ b	Correspond à la joie comme un mot séparé, mais pas dans le plaisir .
\ B	Pas une limite de mot	\ Bjoy \ b	Correspond à la joie dans l'agréable , mais pas comme un mot séparé.

Construction de l'alternance (ou)

L'opérand d'alternance permet la logique ou la logique, vous pouvez donc correspondre à celle-ci ou à cet élément.

Construction	Description	Exemple	Matchs
|	Correspond à tout élément unique séparé par la barre verticale	(s | sh) ells	Dans elle vend des coquilles de mer, des matchs et des obus

Regards

Les constructions de sotation sont utiles lorsque vous souhaitez correspondre à quelque chose qui est ou non suivi ou précédé par autre chose. Ces expressions sont parfois appelées "assertions zéro largeur" ​​ou "correspondance à largeur zéro" car elles correspondent à une position plutôt que des caractères réels.

Note. Dans la saveur VBA Regex, les lookbehinds ne sont pas soutenus.

Modèle	Description	Exemple	Matchs
(? =)	Lookahead positif	X (? = Y)	Correspond à l'expression x quand il est suivi de y (c'est-à-dire s'il y a y avant x)
(?!)	Lookahead négatif	X (?! Y)	Correspond à l'expression x s'il n'est pas suivi par y
(?	Look positif	(?	Correspond à l'expression x quand il est précédé de y (c'est-à-dire s'il y a y derrière x)
(?	Look négatif	(?	Correspond à l'expression x quand il n'est pas précédé de y

Maintenant que vous connaissez les éléments essentiels, passons à la partie la plus intéressante - en utilisant des experts sur des données réelles pour analyser les chaînes et trouver les informations requises. Si vous avez besoin de plus de détails sur la syntaxe, le guide Microsoft sur le langage d'expression régulière peut s'avérer utile.

Fonctions regex personnalisées pour excel

Comme déjà mentionné, Microsoft Excel n'a pas de fonctions regex intégrées. Pour activer les expressions régulières, nous avons créé trois fonctions VBA personnalisées (aka fonctions définies par l'utilisateur). Vous pouvez copier les codes à partir des pages ci-dessous ou à partir de notre exemple de classeur, puis coller dans vos propres fichiers Excel.

Comment fonctionnent les fonctions VBA Regexp

Cette section explique la mécanique intérieure et peut être intéressante pour ceux qui veulent savoir exactement ce qui se passe au backend.

Pour commencer à utiliser des expressions régulières dans VBA, vous devez soit activer la bibliothèque de référence d'objet Regex ou utiliser la fonction CreateObject. Pour vous épargner le problème de définir la référence dans l'éditeur VBA, nous avons choisi cette dernière approche.

L'objet regexp a 4 propriétés:

• Modèle - est le modèle à correspondre dans la chaîne d'entrée.
• Global - Contrôle de trouver toutes les correspondances dans la chaîne d'entrée ou tout simplement la première. Dans nos fonctions, il est défini sur TRUE pour obtenir toutes les correspondances .
• Multiline - Détermine s'il faut faire correspondre le motif entre les ruptures de ligne dans les chaînes multi-lignes ou uniquement dans la première ligne. Dans nos codes, il est défini sur TRUE pour rechercher dans chaque ligne .
• IgnoreCase - définit si une expression régulière est sensible à la casse (par défaut) ou insensible à la casse (définie sur true). Dans notre cas, cela dépend de la façon dont vous configurez le paramètre Match_case en option. Par défaut, toutes les fonctions sont sensibles à la casse .

Limitations VBA Regexp

Excel VBA implémente les modèles regex essentiels, mais il manque de nombreuses fonctionnalités avancées disponibles dans les moteurs .NET, Perl, Java et autres moteurs regex. Par exemple, VBA Regexp ne prend pas en charge les modificateurs en ligne tels que (? I) pour la correspondance insensible à la casse ou (? M) pour le mode multi-ligne, LookBehinds, Posix Classes, pour n'en nommer que quelques-uns.

Excel Regex Match Fonction

La fonction regexpmatch recherche une chaîne d'entrée pour le texte qui correspond à une expression régulière et renvoie true si une correspondance est trouvée, fausse autrement.

Regexpmatch (texte, modèle, [match_case])

Où:

• Texte (requis) - Une ou plusieurs chaînes pour rechercher.
• Modèle (requis) - l'expression régulière à correspondre.
• Match_case (facultatif) - type de correspondance. Vrai ou omis - sensible à la casse; Faux - insensible à la cas

Le code de la fonction est ici.

Exemple: comment utiliser les expressions régulières pour faire correspondre les chaînes

Dans l'ensemble de données ci-dessous, supposons que vous souhaitiez identifier les entrées contenant des codes SKU.

Étant donné que chaque SKU commence par 2 lettres majuscules, suivies d'un trait d'union, suivie de 4 chiffres, vous pouvez les faire correspondre en utilisant l'expression suivante.

Modèle : \ b [az] {2} - \ d {4} \ b

Où [az] {2} signifie 2 lettres majuscules de a à z et \ d {4} signifie 4 chiffres de 0 à 9. Une limite de mot \ b indique qu'un SKU est un mot distinct et ne fait pas partie d'une chaîne plus grande.

Avec le modèle établi, commencez à taper une formule comme vous le faites normalement, et le nom de la fonction apparaîtra dans la liste suggérée par la saisie semi-automatique d'Excel:

En supposant que la chaîne d'origine est en A5, la formule va comme suit:

=RegExpMatch(A5, "\b[AZ]{2}-\d{3}\b")

Pour plus de commodité, vous pouvez saisir l'expression régulière dans une cellule séparée et utiliser une référence absolue ($ a 2 $) pour l'argument du modèle . Cela garantit que l'adresse cellulaire restera inchangée lorsque vous copiez la formule à d'autres cellules:

=RegExpMatch(A5, $A$2)

Pour afficher vos propres étiquettes de texte au lieu de True et False, nidine RegexPMatch dans la fonction IF et spécifiez les textes souhaités dans les arguments Value_IF_TRUE et Value_IF_FALSE :

=IF(RegExpMatch(A5, $A$2), "Yes", "No")

Pour plus d'exemples de formules, veuillez consulter:

• Comment faire correspondre les chaînes en utilisant des expressions régulières
• Excel Validation des données avec Regex

Excel Regex Extrait Fonction

La fonction regexpextract recherche des sous-chaînes qui correspondent à une expression régulière et extrait toutes les correspondances ou la correspondance spécifique.

Regexpextract (texte, modèle, [instance_num], [match_case])

Où:

• Texte (requis) - La chaîne de texte pour rechercher.
• Modèle (requis) - l'expression régulière à correspondre.
• Instance_num (facultatif) - un numéro de série qui indique quelle instance extraire. En cas d'omission, renvoie toutes les correspondances trouvées (par défaut).
• Match_case (facultatif) - définit s'il faut correspondre (vrai ou omis) ou ignorer (faux) cas de texte.

Vous pouvez obtenir le code de la fonction ici.

Exemple: comment extraire des chaînes en utilisant des expressions régulières

Prenant notre exemple un peu plus loin, extrons les numéros de facture. Pour cela, nous utiliserons un regex très simple qui correspond à tout numéro à 7 chiffres:

Modèle : \ b \ d {7} \ b

Mettez le motif dans A2 et vous ferez le travail avec cette formule compacte et élégante:

=RegExpExtract(A5, $A$2)

Si un motif est apparié, la formule extrait un numéro de facture, si aucune correspondance n'est trouvée - rien n'est retourné.

Pour plus d'exemples, veuillez consulter: Comment extraire les chaînes dans Excel en utilisant Regex.

Excel Regex Remplacer la fonction

La fonction regexpreplace remplace les valeurs qui correspondent à un regex avec le texte que vous spécifiez.

Regexpreplace (texte, modèle, remplacement, [instance_num], [match_case])

Où:

• Texte (requis) - La chaîne de texte pour rechercher.
• Modèle (requis) - l'expression régulière à correspondre.
• Remplacement (requis) - Le texte pour remplacer les sous-chaînes correspondantes.
• Instance_num (facultatif) - L'instance à remplacer. La valeur par défaut est "toutes les correspondances".
• Match_case (Facultatif) - Contrôle de faire correspondre (vrai ou omis) ou ignorer (faux) cas de texte.

Le code de la fonction est disponible ici.

Exemple: comment remplacer ou supprimer les chaînes à l'aide de regex

Certains de nos dossiers contiennent des numéros de carte de crédit. Ces informations sont confidentielles et vous voudrez peut-être les remplacer par quelque chose ou supprimer complètement. Les deux tâches peuvent être accomplies à l'aide de la fonction Regexpreplace . Comment? Dans un deuxième scénario, nous remplacerons par une chaîne vide.

Dans notre tableau d'échantillons, tous les numéros de carte ont 16 chiffres, qui sont écrits en 4 groupes séparés avec des espaces. Pour les trouver, nous reproduisons le modèle en utilisant cette expression régulière:

Modèle : \ b \ d {4} \ d {4} \ d {4} \ d {4} \ b

Pour le remplacement, la chaîne suivante est utilisée:

Remplacement : xxxx xxxx xxxx xxxx

Et voici une formule complète pour remplacer les numéros de carte de crédit par des informations insensibles:

=RegExpReplace(A5, "\b\d{4} \d{4} \d{4} \d{4}\b", "XXXX XXXX XXXX XXXX")

Avec le regex et le texte de remplacement dans des cellules séparées (A2 et B2), la formule fonctionne aussi bien:

Dans Excel, la «suppression» est un cas particulier de «remplacement». Pour supprimer les numéros de carte de crédit, utilisez simplement une chaîne vide ("") pour l'argument de remplacement :

=RegExpReplace(A5, "\b\d{4} \d{4} \d{4} \d{4}\b", "")

Conseil. Pour obtenir une plate-forme de lignes vides dans les résultats, vous pouvez utiliser une autre fonction RegexPreplace comme indiqué dans cet exemple: comment supprimer les lignes vides à l'aide de regex.

Pour plus d'informations, veuillez consulter:

• Comment remplacer les chaînes dans Excel en utilisant Regex
• Comment supprimer les chaînes en utilisant Regex
• Comment déshabiller les espaces à l'aide de regex

Outils Regex pour correspondre, extraire, remplacer et supprimer les sous-chaînes

Les utilisateurs de notre suite ultime peuvent obtenir toute la puissance des expressions régulières sans insérer une seule ligne de code dans leurs classeurs. Tout le code nécessaire est écrit par nos développeurs et Smoothy intégrés dans votre Excel lors de l'installation.

Contrairement aux fonctions VBA discutées ci-dessus, les fonctions d'Ultimate Suite sont basées sur .NET, ce qui offre deux avantages principaux:

1. Vous pouvez utiliser des expressions régulières dans des classeurs .xlsx normaux sans ajouter de code VBA et avoir à les enregistrer sous forme de fichiers macro compatibles.
2. Le moteur .net Regex prend en charge les expressions régulières classiques complètes, ce qui vous permet de construire des modèles plus sophistiqués.

Comment utiliser Regex dans Excel

Avec la suite ultime installée, l'utilisation d'expressions régulières dans Excel est aussi simple que ces deux étapes:

1. Dans l'onglet Données AbleBits , dans le groupe de texte , cliquez sur Regex Tools .

   

2. Sur le vget des outils Regex , procédez comme suit:
   • Sélectionnez les données source.
   • Entrez votre modèle regex.
   • Choisissez l'option souhaitée: correspondez , extraire , supprimer ou remplacer .
   • Pour obtenir le résultat en tant que formule et non la valeur, sélectionnez la case d' insertion en tant que formule .
   • Appuyez sur le bouton d'action.

   Par exemple, pour supprimer les numéros de carte de crédit des cellules A2: A6, nous configurons ces paramètres:

   

Dans un tririce, une fonction AbleBitsRegex sera insérée dans une nouvelle colonne à droite de vos données d'origine. Dans notre cas, la formule est:

=AblebitsRegexRemove(A2, "\b\d{4} \d{4} \d{4} \d{4}\b")

Une fois la formule là, vous pouvez la modifier, la copier ou la déplacer comme n'importe quelle formule native.

Comment insérer une formule regex directement dans une cellule

Les fonctions AbleBitsRegex peuvent également être insérées directement dans une cellule sans utiliser l'interface du complément. Voici comment:

1. Cliquez sur le bouton FX dans la barre de formule ou insérez la fonction dans l'onglet Formules .
2. Dans la boîte de dialogue Insert Fonction , sélectionnez la catégorie AbleBitsudfs , choisissez la fonction d'intérêt et cliquez sur OK.

   

3. Définissez les arguments de la fonction comme vous le faites normalement et cliquez sur OK. Fait!

Pour plus d'informations, veuillez consulter les outils Regex pour Excel.

C'est comment utiliser des expressions régulières pour correspondre, extraire, remplacer et supprimer le texte dans les cellules Excel. Je vous remercie d'avoir lu et j'ai hâte de vous voir sur notre blog la semaine prochaine!

Téléchargements disponibles

Excel Regex - Exemples de formule (fichier .xlsm) Ultimate Suite - Version d'essai (fichier .exe)

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!