Regex pour supprimer certains caractères ou texte dans Excel

Avez-vous déjà pensé à quel point Excel serait puissant si quelqu'un pouvait enrichir sa boîte à outils avec des expressions régulières? Nous avons non seulement pensé, mais nous avons travaillé dessus :) Et maintenant, vous pouvez ajouter cette merveilleuse fonction Regex à vos propres classeurs et effacer les sous-chaînes qui correspondent à un modèle en un rien de temps!

La semaine dernière, nous avons examiné comment utiliser des expressions régulières pour remplacer les chaînes dans Excel. Pour cela, nous avons créé une fonction regex personnalisée. Il s'est avéré que la fonction va au-delà de son utilisation principale et peut non seulement remplacer les chaînes mais également les retirer. Comment cela pourrait-il être? En termes d'Excel, la suppression d'une valeur n'est rien d'autre que la remplacer par une chaîne vide, quelque chose que notre fonction regex est très bonne!

• Fonction VBA Regexp pour supprimer les sous-chaînes
• Excel regex supprimer des exemples
  • Supprimer tous les matchs ou la correspondance spécifique
  • Dépuiser certains caractères
  • Supprimer les caractères spéciaux
  • Supprimer les caractères non numériques
  • Dépuiser tout après l'espace
  • Supprimer le texte après un certain caractère
  • Purgez tout avant l'espace
  • Éliminez tout avant le caractère
  • Supprimer tout sauf
  • Expression régulière pour supprimer les balises HTML
• AbleBits Regex Supprimer l'outil

Fonction vba regexp pour supprimer les sous-chaînes dans Excel

Comme nous le savons tous, les expressions régulières ne sont pas prises en charge dans Excel par défaut. Pour leur permettre, vous devez créer votre propre fonction définie par l'utilisateur. La bonne nouvelle est qu'une telle fonction est déjà écrite, testée et prête à l'emploi. Tout ce que vous avez à faire est de copier ce code, de le coller dans votre éditeur VBA, puis d'enregistrer votre fichier en tant que classeur de macro (.xlsm).

La fonction a la syntaxe suivante:

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

Les trois premiers arguments sont requis, les deux derniers sont facultatifs.

Où:

• Texte - La chaîne de texte pour rechercher.
• Modèle - l'expression régulière à rechercher.
• Remplacement - le texte à remplacer par. Pour supprimer les sous-chaînes correspondant au modèle, utilisez une chaîne vide ("") pour le remplacement.
• Instance_num (facultatif) - L'instance à remplacer. S'il est omis, toutes les correspondances trouvées sont remplacées (par défaut).
• Match_case (facultatif) - une valeur booléenne indiquant s'il faut correspondre ou ignorer le cas de texte. Pour la correspondance sensible à la casse, utilisez True (par défaut); Pour l'insensible à la cas - Faux.

Pour plus d'informations, veuillez consulter la fonction Regexpreplace.

Conseil. Dans des cas simples, vous pouvez supprimer des caractères ou des mots spécifiques des cellules avec des formules Excel. Mais les expressions régulières offrent beaucoup plus d'options à cela.

Comment supprimer les chaînes à l'aide d'expressions régulières - Exemples

Comme mentionné ci-dessus, pour supprimer les parties du texte correspondant à un motif, vous devez les remplacer par une chaîne vide. Ainsi, une formule générique prend cette forme:

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

Les exemples ci-dessous montrent diverses implémentations de ce concept de base.

Supprimer toutes les correspondances ou la correspondance spécifique

La fonction regexpreplace est conçue pour trouver toutes les sous-chaînes qui correspondent à un regex donné. Les événements à supprimer sont contrôlés par l'argument facultatif 4 ^e , nommé instance_num .

La valeur par défaut est "toutes les correspondances" - lorsque l'argument instance_num est omis, toutes les correspondances trouvées sont supprimées. Pour supprimer une correspondance spécifique, définissez le numéro d'instance.

Dans les chaînes ci-dessous, supposons que vous souhaitiez supprimer le numéro de première commande. Tous ces numéros commencent par le panneau de hachage (#) et contiennent exactement 5 chiffres. Ainsi, nous pouvons les identifier en utilisant ce regex:

Modèle : # \ d {5} \ b

Le mot limite \ b spécifie qu'une sous-chaîne correspondante ne peut pas faire partie d'une chaîne plus grande telle que # 10000001.

Pour supprimer toutes les correspondances, l'argument d'instance_num n'est pas défini:

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

Pour éradiquer uniquement la première occurrence, nous définissons l'argument d'instance_num sur 1:

=RegExpReplace(A5, "#\d{5}\b", "", 1)

Regex pour supprimer certains caractères

Pour retirer certains caractères d'une chaîne, notez simplement tous les caractères indésirables et séparez-les avec une barre verticale | qui agit comme un ou opérateur dans Regexes.

Par exemple, pour normaliser les numéros de téléphone écrits dans divers formats, nous nous débarrassons d'abord de caractères spécifiques tels que des parenthèses, des traits de gamme, des points et des espaces blancs.

Modèle : \ (| \) | - | \. | \ S

=RegExpReplace(A5, "\(|\)|-|\.|\s", "")

Le résultat de cette opération est un numéro à 10 chiffres comme "1234567890".

Pour plus de commodité, vous pouvez saisir le regex est une cellule distincte et vous référer à cette cellule en utilisant une référence absolue telle que $ a 2 $:

=RegExpReplace(A5, $A$2, "")

Et puis, vous pouvez standardiser le formatage comme vous le souhaitez en utilisant l'opérateur de concaténation (&) et les fonctions de texte telles que droite, milieu et gauche.

Par exemple, pour écrire tous les numéros de téléphone au format (123) 456-7890, la formule est:

="("&LEFT(B5, 3)&") "&MID(B5, 4, 3)&"-"&RIGHT(B5, 4)

Où B5 est la sortie de la fonction regexpreplace.

Supprimer des caractères spéciaux en utilisant Regex

Dans l'un de nos tutoriels, nous avons examiné comment supprimer les caractères indésirables dans Excel en utilisant des fonctions intégrées et personnalisées. Les expressions régulières facilitent beaucoup les choses! Au lieu de répertorier tous les caractères à supprimer, spécifiez simplement ceux que vous souhaitez conserver :)

Le motif est basé sur des classes de caractères niées - un caret est mis à l'intérieur d'une classe de caractères [^] pour correspondre à un seul caractère non entre parenthèses. Le quantificateur le force à considérer les caractères consécutifs comme une seule correspondance, de sorte qu'un remplacement est effectué pour une sous-chaîne correspondante plutôt que pour chaque caractère individuel.

Selon vos besoins, choisissez l'un des regex suivants.

Pour supprimer les caractères non alphanumériques , c'est-à-dire tous les caractères à l'exception des lettres et des chiffres:

Modèle : [^ 0-9a-za-z]

Pour purger tous les caractères sauf les lettres , chiffres et espaces :

Modèle : [^ 0-9a-za-z]

Pour supprimer tous les caractères, à l'exception des lettres , des chiffres et des soulignements , vous pouvez utiliser \ w qui représente tout caractère qui n'est pas un caractère alphanumérique ou un soulignement:

Modèle : \ W

Si vous voulez garder d'autres personnages , par exemple, les marques de ponctuation, mettez-les à l'intérieur des supports.

Par exemple, pour retirer tout personnage autre qu'une lettre, un chiffre, une période, une virgule ou un espace, utilisez le regex suivant:

Modèle : [^ 0-9a-za-z \.,]

Cela élimine avec succès tous les caractères spéciaux, mais des espaces supplémentaires demeurent.

Pour résoudre ce problème, vous pouvez nicher la fonction ci-dessus en une autre qui remplace plusieurs espaces par un seul caractère d'espace.

=RegExpReplace(RegExpReplace(A5,$A$2,""), " ", " ")

Ou utilisez simplement la fonction de garniture native avec le même effet:

=TRIM(RegExpReplace(A5, $A$2, ""))

Regex pour supprimer les caractères non nucères

Pour supprimer tous les caractères non numériques d'une chaîne, vous pouvez utiliser cette formule longue ou l'un des armes très simples répertoriées ci-dessous.

Faites correspondre tout personnage qui n'est pas un chiffre:

Modèle : \ D

Strip des caractères non nucères à l'aide de classes ni cirées:

Modèle : [^ 0-9]

Modèle : [^ \ d]

Conseil. Si votre objectif est de supprimer le texte et de renverser les nombres restants dans des cellules séparées ou de les placer toutes dans une cellule séparée avec un délimiteur spécifié, utilisez la fonction regexpextract comme expliqué dans la façon d'extraire les nombres de la chaîne en utilisant des expressions régulières.

Regex pour tout supprimer après l'espace

Pour anéantir tout après un espace, utilisez le caractère spatial () ou espace blanc (\ s) pour trouver le premier espace et. * Pour correspondre à tous les personnages après.

Si vous avez des chaînes à ligne unique qui ne contiennent que des espaces normaux (valeur 32 dans le système ASCII 7 bits), il n'a pas vraiment d'importance de laquelle ci-dessous regex que vous utilisez. En cas de chaînes multi-lignes, cela fait une différence.

Pour tout supprimer après un caractère d'espace , utilisez ce regex:

Modèle : " .*"

=RegExpReplace(A5, " .*", "")

Cette formule dépouillera n'importe quoi après le premier espace de chaque ligne . Pour que les résultats s'affichent correctement, assurez-vous d'activer le texte enveloppé.

Pour tout retirer après un espace blanc (y compris un espace, une languette, un retour en chariot et une nouvelle ligne), le regex est:

Motifs .*

=RegExpReplace(A5, "\s.*", "")

Parce que \ s correspond à quelques types d'espaces blancs différents, y compris une nouvelle ligne (\ n), cette formule supprime tout après le premier espace dans une cellule, peu importe le nombre de lignes qu'il y a.

Regex pour supprimer le texte après un caractère spécifique

En utilisant les méthodes de l'exemple précédent, vous pouvez éradiquer le texte après tout caractère que vous spécifiez.

Pour gérer chaque ligne séparément:

Modèle générique : char. *

Dans les chaînes à une seule ligne, cela supprimera tout après Char . Dans les chaînes multi-lignes, chaque ligne sera traitée individuellement car dans la saveur Regex VBA, une période (.) Correspond à n'importe quel personnage sauf une nouvelle ligne.

Pour traiter toutes les lignes en une seule chaîne:

Modèle générique : char (. | \ N) *

Pour supprimer n'importe quoi après un caractère donné, y compris de nouvelles lignes, \ n est ajouté au modèle.

Par exemple, pour supprimer le texte après la première virgule dans une chaîne, essayez ces expressions régulières:

Modèle : ,.*

Modèle :, (. | \ N) *

Dans la capture d'écran ci-dessous, vous pouvez examiner comment les résultats diffèrent.

Regex pour tout supprimer avant l'espace

Lorsque vous travaillez avec de longues chaînes de texte, vous pouvez parfois les rendre plus courts en supprimant la même partie des informations dans toutes les cellules. Ci-dessous, nous en discuterons de deux cas.

Retirez tout avant le dernier espace

Comme pour l'exemple précédent, une expression régulière dépend de votre compréhension d'un "espace".

Pour faire correspondre quoi que ce soit au dernier espace , ce regex fera l'affaire (des guillemets sont ajoutés pour faire un espace après un astérisque perceptible).

Modèle : ".* "

Pour correspondre à n'importe quoi avant le dernier espace blanc (y compris un espace, une languette, un retour en chariot et une nouvelle ligne), utilisez cette expression régulière.

Motifs

La différence est particulièrement visible sur les chaînes multi-lignes.

Tout enlever avant le premier espace

Pour faire correspondre quoi que ce soit au premier espace d'une chaîne, vous pouvez utiliser cette expression régulière:

Modèle : ^ [^] *

Dès le début d'une chaîne ^, nous correspondons à zéro ou plus de caractères non espaces [^] * qui sont immédiatement suivis d'un ou plusieurs espaces "". La dernière partie est ajoutée pour empêcher les espaces principaux potentiels dans les résultats.

Pour supprimer le texte avant le premier espace dans chaque ligne, la formule est écrite dans le mode par défaut "toutes les correspondances" ( instance_num omise):

=RegExpReplace(A5, "^[^ ]* ", "")

Pour supprimer du texte avant le premier espace dans la première ligne et laisser toutes les autres lignes intactes, l'argument instance_num est défini sur 1:

=RegExpReplace(A5, "^[^ ]* ", "", 1)

Regex pour tout supprimer avant le personnage

Le moyen le plus simple de supprimer tout le texte avant un caractère spécifique est d'utiliser un regex comme celui-ci:

Modèle générique : ^ [^ char] * char]

Traduit en un langage humain, il dit: "Dès le début d'une corde ancrée par ^, correspond à 0 ou plus de caractères sauf char [^ char] * jusqu'à la première occurrence de char .

Par exemple, pour supprimer tout le texte avant le premier côlon, utilisez cette expression régulière:

Modèle : ^ [^:] *:

Pour éviter les espaces de tête dans les résultats, ajoutez un caractère blanc d'espace \ S * à la fin. Cela supprimera tout avant le premier côlon et coupera tous les espaces juste après:

Modèle : ^ [^:] *: \ S *

=RegExpReplace(A5, "^[^:]*:\s*", "")

Conseil. Outre les expressions régulières, Excel a ses propres moyens de supprimer le texte par position ou correspondant. Pour apprendre à accomplir la tâche avec des formules natives, veuillez voir comment supprimer le texte avant ou après un personnage dans Excel.

Regex pour tout supprimer sauf

Pour éradiquer tous les caractères d'une chaîne, sauf ceux que vous souhaitez conserver, utilisez des classes de caractères nigrées.

Par exemple, pour supprimer tous les caractères à l'exception des lettres et des points minuscules, le regex est:

Modèle : [^ az \.]

En fait, nous pourrions nous passer du quantificateur ici car notre fonction remplace toutes les correspondances trouvées. Le quantificateur le rend un peu plus rapide - au lieu de gérer chaque caractère individuel, vous remplacez une sous-chaîne.

=RegExpReplace(A5, "[^az\.] ", "")

Regex pour supprimer les balises HTML dans Excel

Tout d'abord, il convient de noter que le HTML n'est pas un langage régulier, donc l'analyser en utilisant des expressions régulières n'est pas le meilleur moyen. Cela dit, les regex peuvent certainement aider à retirer les étiquettes de vos cellules pour rendre votre nettoyant pour ensemble de données.

Étant donné que les balises HTML sont toujours placées dans les supports d'angle , vous pouvez les trouver en utilisant l'un des regex suivants.

Classe niée:

Modèle : ] *>

Ici, nous correspondons à un support d'angle d'ouverture, suivi de zéro ou plus d'occurrences de tout caractère à l'exception du support d'angle de fermeture [^>] * jusqu'au support d'angle de fermeture le plus proche.

Recherche paresseuse:

Modèle : <.>

Ici, nous correspondons à tout, du premier support d'ouverture au premier support de clôture. Le point d'interrogation force. * Pour correspondre au moins de personnages que possible jusqu'à ce qu'il trouve un support de clôture.

Quel que soit le modèle que vous choisissez, le résultat sera absolument le même.

Par exemple, pour supprimer toutes les balises HTML d'une chaîne dans A5 et laisser du texte, la formule est:

=RegExpReplace(A5, "]*>", "")

Ou vous pouvez utiliser le quantificateur paresseux comme indiqué dans la capture d'écran:

Cette solution fonctionne parfaitement pour le texte unique (lignes 5 - 9). Pour plusieurs textes (lignes 10 - 12), les résultats sont discutables - les textes de différentes balises sont fusionnés en un seul. Est-ce correct ou non? J'ai peur, ce n'est pas quelque chose qui peut être facilement décidé - tout dépend de votre compréhension du résultat souhaité. Par exemple, dans B11, le résultat "A1" est attendu; Pendant que dans B10, vous voudrez peut-être que "data1" et "data2" soient séparés avec un espace.

Pour supprimer les balises HTML et séparer les textes restants avec des espaces, vous pouvez procéder de cette manière:

1. Remplacez les balises par des espaces "", pas des chaînes vides:

   =RegExpReplace(A5, "]*>", " ")

2. Réduisez plusieurs espaces à un seul caractère d'espace:

   =RegExpReplace(RegExpReplace(A5, "]*>", " "), " ", " ")

3. Trime des espaces de tête et de fuite:

   =TRIM(RegExpReplace(RegExpReplace(A5, "]*>", " "), " ", " "))

Le résultat ressemblera à ceci:

AbleBits Regex Supprimer l'outil

Si vous avez eu la chance d'utiliser notre suite ultime pour Excel, vous avez probablement déjà découvert les nouveaux outils Regex introduits avec la récente version. La beauté de ces fonctions regex basées sur .NET est qu'elles prennent en charge la syntaxe d'expression régulière complète sans limitations regexp VBA, et deuxièmement, ne nécessitent pas d'insertion de code VBA dans vos classeurs car toute l'intégration du code est effectuée par nous au backend.

Votre partie du travail consiste à construire une expression régulière et à la servir à la fonction :) Permettez-moi de vous montrer comment le faire sur un exemple pratique.

Comment supprimer le texte entre parenthèses et parenthèses en utilisant Regex

Dans de longues chaînes de texte, des informations moins importantes sont souvent enfermées entre [crochets] et (parenthèses). Comment supprimer ces détails non pertinents en conservant toutes les autres données?

En fait, nous avons déjà construit un regex similaire pour supprimer des balises HTML, c'est-à-dire du texte dans les supports d'angle. De toute évidence, les mêmes méthodes fonctionneront également pour les supports carrés et ronds.

Modèle : (\ (. *? \)) | (\ [. *? \])

L'astuce consiste à utiliser un quantificateur paresseux (*?) Pour correspondre à la sous-chaîne la plus courte possible. Le premier groupe (\ (. *? \)) Correspond à quelque chose, d'une parenthèse d'ouverture à la première parenthèse de clôture. Le deuxième groupe (\ [. *? \]) Correspond à tout, d'un support d'ouverture au premier support de clôture. Une barre verticale | agit comme opérateur OR.

Avec le motif déterminé, «le nourrissons» à notre fonction Regex Retirez. Voici comment:

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

   

2. Dans le vget des outils Regex , sélectionnez vos chaînes de source, entrez votre exploitement, choisissez l'option Supprimer et appuyez sur Supprimer .

   Pour obtenir les résultats sous forme de formules, pas de valeurs, sélectionnez la case d' insertion en tant que formule .

   Pour supprimer le texte entre parenthèses des chaînes dans A2: A5, nous configurons les paramètres comme suit:

   

En conséquence, la fonction AbleBitsRegexRemove est insérée dans une nouvelle colonne à côté de vos données d'origine.

La fonction peut également être entrée directement dans une cellule via la boîte de dialogue de la fonction d'insertion standard, où elle est classée sous AbleBitsudfs .

Comme ABSEBITSREGEXREMOVE est conçu pour supprimer le texte, il ne nécessite que deux arguments - la chaîne source et le regex. Les deux paramètres peuvent être définis directement dans une formule ou fournis sous forme de références cellulaires. Si nécessaire, cette fonction personnalisée peut être utilisée avec toutes les natives.

Par exemple, pour couper les espaces supplémentaires dans les chaînes résultantes, vous pouvez utiliser la fonction de garniture comme un wrapper:

=TRIM(AblebitsRegexRemove(A5, $A$2))

Voilà comment supprimer les chaînes dans Excel en utilisant des expressions régulières. Je vous remercie d'avoir lu et j'ai hâte de vous voir sur notre blog la semaine prochaine!

Téléchargements disponibles

Supprimer les chaînes à l'aide de regex - Exemples (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!