Retirez les espaces blancs et les lignes vides dans Excel en utilisant Regex

Vous souhaitez gérer les espaces blancs de la manière la plus efficace? Utilisez des expressions régulières pour éliminer tous les espaces d'une cellule, remplacez plusieurs espaces par un seul caractère, coupez les espaces entre les nombres uniquement, et plus encore.

Quelle que soit les données d'entrée que vous utilisez, vous rencontrerez à peine un ensemble de données sans espaces. Dans la plupart des cas, l'espace blanc est bon - vous l'utilisez pour séparer visuellement différentes informations pour faciliter la perception. Dans certaines situations, cependant, cela peut devenir mauvais - les espaces supplémentaires peuvent gâcher vos formules et rendre vos feuilles de calcul presque ingérables.

• Pourquoi utiliser l'expression régulière pour couper les espaces blancs dans Excel?
• Comment activer les expressions régulières dans Excel
• Comment supprimer les espaces avec regex - exemples
  • Retirez tous les espaces d'une cellule
  • Striper des espaces blancs supplémentaires (plus d'un)
  • TRIP LA LIGENNE ET BLANTES ESPACES
  • Éliminez l'espace blanc supplémentaire mais gardez les pauses de ligne
  • Remplacez plusieurs espaces par un seul caractère
  • Retirer les lignes vides
• Comment couper les espaces blancs avec des outils regex

Pourquoi utiliser l'expression régulière pour couper les espaces blancs dans Excel?

Avant de plonger dans le Nitty-Gritty de l'utilisation d'expressions régulières pour supprimer les espaces blancs dans Excel Feuilles de calcul, j'aimerais répondre à la question qui me vient à l'esprit en premier lieu - pourquoi avons-nous besoin d'exploits alors que Excel a déjà la fonction de finition?

Pour comprendre la différence, voyons ce qui est considéré comme un espace blanc dans chaque cas:

• La fonction de garniture intégrée ne peut que déshabiller le caractère d'espace qui a une valeur 32 dans le système ASCII 7 bits.
• Les expressions régulières peuvent identifier quelques formes différentes d'espace telles que l'espace (), l'onglet (\ t), le retour du chariot (\ r) et la nouvelle ligne (\ n). De plus, il existe le (\ s) (\ s) qui correspond à tous ces types et est extrêmement utile pour nettoyer les données d'entrée brutes.

Sachant exactement ce qui se passe dans les coulisses, il est beaucoup plus facile de trouver une solution, non?

Comment activer les expressions régulières dans Excel

C'est un fait bien connu que Excel prêt à l'emploi ne prend pas en charge les expressions régulières. Pour leur activer, vous devez créer une fonction VBA personnalisée. Heureusement, nous en avons déjà un, nommé Regexpreplace . Attendez, pourquoi "remplacer" pendant que nous parlons de retirer? Dans la langue Excel, "Supprimer" n'est qu'un autre mot pour "remplacer par une chaîne vide" :)

Pour ajouter la fonction à votre Excel, copiez simplement son code à partir de cette page, collez-le dans l'éditeur VBA et enregistrez votre fichier en tant que classeur de macro (.xlsm).

Voici la syntaxe de la fonction pour votre référence:

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 d'origine pour rechercher.
• Modèle - Le regex à la recherche.
• Remplacement - le texte à remplacer par. Pour supprimer les espaces blancs , vous définissez cet argument sur:
  • chaîne vide ("") pour couper absolument tous les espaces
  • caractère spatial ("") pour remplacer plusieurs espaces par un seul caractère spatial
• Instance_num (facultatif) - le numéro d'instance. Dans la plupart des cas, vous l'omettre pour remplacer toutes les instances (par défaut).
• Match_case (facultatif) - une valeur booléenne indiquant s'il faut correspondre (vrai) ou ignorer (faux) cas de texte. Pour l'espace, il n'est pas pertinent et donc omis.

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

Comment supprimer les espaces avec regex - exemples

Avec la fonction Regexpreplace ajoutée à votre classeur, abordons différents scénarios un à la fois.

Retirez tous les espaces blancs à l'aide de regex

Pour supprimer tous les espaces d'une chaîne, vous recherchez simplement n'importe quel caractère blanc, y compris un espace, un onglet, un retour chariot et un flux de ligne, et les remplacer par une chaîne vide ("").

Motifs

Remplacement : ""

En supposant que la chaîne source est en A5, la formule en B5 est:

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

Pour faciliter la gestion de vos modèles, vous pouvez saisir le regex dans une cellule prédéfinie et la fournir à la formule en utilisant une référence absolue comme $ a 2 $, de sorte que l'adresse de cellule restera inchangée lors de la copie de la formule en bas de la colonne.

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

Supprimer plus d'un espace blanc

Pour éliminer les espaces supplémentaires (c'est-à-dire plusieurs espaces consécutifs), utilisez les mêmes regex \ s mais remplacez les correspondances trouvées par un seul caractère d'espace.

Motifs

Remplacement : ""

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

Veuillez prêter attention que cette formule garde un personnage d'espace non seulement entre les mots mais aussi au début et à la fin d'une chaîne, ce qui n'est pas bon. Pour vous débarrasser de l'espace blanc dirigeant et traînant, nid la formule ci-dessus dans une autre fonction Regexpreplace qui dépouille les espaces du début et de la fin:

=RegExpReplace(RegExpReplace(A5, "\s ", " "), "^[\s] |[\s] $", "")

Regex pour éliminer les espaces blancs et traînants

Pour rechercher un espace blanc au début ou à la fin d'une ligne, utilisez le démarrage ^ et terminez $ ancres.

Espace blanc principal :

Modèle : ^ [\ S]

Espace blanc de traîne :

Modèle : [\ s] $

Espace blanc en tête et en arrière :

Modèle : ^ [\ s] | [\ s] $

Quel que soit l'expansion que vous choisissez, remplacez les matchs par rien.

Remplacement : ""

Par exemple, pour éliminer tous les espaces au début et à la fin d'une chaîne en A5, la formule est:

=RegExpReplace(A5, "^[\s] |[\s] $", "")

Comme indiqué dans la capture d'écran ci-dessous, cela ne fait que supprimer les espaces blancs et traînés. Les espaces entre les mots restent intacts en créant une vue visuellement agréable pour l'œil du lecteur.

Retirez les espaces supplémentaires mais gardez les pauses de ligne

Lorsque vous travaillez avec des chaînes multi-lignes, vous souhaiterez peut-être vous débarrasser des espaces supplémentaires mais préserver les ruptures de ligne. Pour que cela soit fait, au lieu du caractère Whitespace \ S, recherchez des espaces [] ou des espaces et des onglets [\ t]. Ce dernier modèle est utile lorsque vos données source sont importées d'une autre source, par exemple un éditeur de texte.

Dans l'ensemble de données ci-dessous, supposons que vous souhaitiez couper tous les espaces de tête / arrière et tous les espaces intermédiaires sauf un, en gardant plusieurs lignes intactes. Pour remplir la tâche, vous aurez besoin de deux fonctions RegexPreplace différentes.

La première fonction remplace plusieurs espaces par un seul caractère d'espace.

=RegExpReplace(A5, " ", " ")

L'autre dépouille les espaces du début et de la fin d'une ligne:

=RegExpReplace(A5, "^ | $", "")

Nicez simplement les deux fonctions les unes dans les autres:

=RegExpReplace(RegExpReplace(A5, " ", " "), "^ | $", "")

Et vous obtiendrez un résultat parfait:

Regex pour remplacer plusieurs espaces par un seul caractère

Si vous souhaitez supprimer tous les espaces d'une chaîne et remplacer chaque groupe d'espaces consécutifs par un caractère spécifique, c'est ce que vous devez faire:

Tout d'abord, utilisez ce regex pour couper les espaces blancs de tête et de traîne:

=RegExpReplace(A8, "^[\s] |[\s] $", "")

Ensuite, servez la fonction ci-dessus à l'argument texte d'un autre regexpreplace qui remplace un ou plusieurs espaces blancs consécutifs par le caractère que vous spécifiez, par exemple un trait d'union:

Motifs

Remplacement : -

En supposant que la chaîne source est dans A8, la formule prend cette forme:

=RegExpReplace(RegExpReplace(A8, "^[\s] |[\s] $", ""), "\s ", "-")

Ou vous pouvez saisir les modèles et les remplacements dans des cellules séparées comme indiqué dans la capture d'écran:

Regex pour supprimer les lignes vides

Voici une question que les utilisateurs qui ont plusieurs lignes dans une cellule se demandent fréquemment: "Il y a beaucoup de lignes vierges dans mes cellules. Y a-t-il un moyen de s'en débarrasser autrement que de passer par chaque cellule et de supprimer chaque ligne manuellement?" La réponse: c'est facile!

Pour correspondre aux lignes vides qui n'ont pas un seul caractère du début ^ de la ligne actuelle jusqu'à la ligne suivante \ n, le regex est:

Modèle : ^ \ n

Si vos lignes visuellement vides contiennent des espaces ou des onglets, utilisez cette expression régulière:

Modèle : ^ [\ t] * \ n

Remplacez simplement le regex par une chaîne vide en utilisant cette formule, et toutes les lignes vierges seront parties en même temps!

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

Suppression des espaces blancs avec des outils Regex

Les exemples ci-dessus ont démontré juste une petite partie des merveilleuses possibilités fournies par Regex. Malheureusement, toutes les fonctionnalités des expressions régulières classiques ne sont pas disponibles en VBA.

Heureusement, les outils regex inclus dans notre suite ultime sont exempts de ces limitations car elles sont traitées par le moteur .NET Regex de Microsoft. Cela vous permet de construire des modèles plus sophistiqués qui ne sont pas pris en charge par VBA Regexp. Vous trouverez ci-dessous un exemple d'une telle expression régulière.

Regex pour supprimer l'espace entre les nombres

Dans une chaîne alphanumérique, supposons que vous souhaitiez supprimer uniquement les espaces entre les nombres, donc une chaîne telle que "A 1 2 B" devient "A 12 B".

Pour correspondre à un espace blanc entre deux chiffres, vous pouvez utiliser les regards suivants:

Modèle : (?

Pour créer une formule basée sur les regexins ci-dessus, voici deux étapes faciles à effectuer:

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 les données source, entrez votre exploitement, choisissez l'option Supprimer et appuyez sur Supprimer .

   Pour obtenir les résultats en tant que formules, pas les valeurs, n'oubliez pas de mettre une coche dans la case d' insertion en tant que formule .

   

Dans un instant, vous verrez la fonction AbleBitsRegexRemove insérée dans une nouvelle colonne à droite des données d'origine.

Alternativement, vous pouvez saisir le regex dans une cellule, par exemple A5, et insérer la formule directement dans une cellule à l'aide de la boîte de dialogue de la fonction d'insertion , où ABSEBBITSREGEXREMOVE est classé sous AbleBitsudfs .

Comme cette fonction est spécialement conçue pour éliminer les chaînes, elle ne nécessite que deux arguments - la chaîne d'entrée et le regex:

=AblebitsRegexRemove(A5, $A$2)

Voilà comment supprimer les espaces 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

Supprimez l'espace avec 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!