Lorsque vous travaillez avec des données de texte dans Excel, vous devrez peut-être supprimer le texte avant ou après un caractère ou une chaîne de texte spécifique.
Par exemple, si vous avez le nom et les données de désignation des personnes, vous pouvez supprimer la désignation après la virgule et ne conserver que le nom (ou vice-versa lorsque vous conservez la désignation et supprimez le nom).
Parfois, cela peut être fait avec une formule simple ou une recherche et un remplacement rapides, et parfois, cela nécessite des formules ou des solutions de contournement plus complexes.
Dans ce tutoriel, je vais vous montrer comment supprimer le texte avant ou après un caractère spécifique dans Excel (en utilisant différents exemples).
Commençons donc par quelques exemples simples.
Supprimer le texte après un caractère à l'aide de Rechercher et remplacer
Si vous souhaitez supprimer rapidement tout le texte après une chaîne de texte spécifique (ou avant une chaîne de texte), vous pouvez le faire en utilisant Rechercher et remplacer et des caractères génériques.
Supposons que vous ayez un ensemble de données comme indiqué ci-dessous et que vous souhaitiez supprimer la désignation après la virgule et conserver le texte avant la virgule.
Vous trouverez ci-dessous les étapes à suivre :
- Copiez et collez les données de la colonne A à la colonne B (pour conserver également les données d'origine)
- Avec les cellules de la colonne B sélectionnées, cliquez sur l'onglet Accueil
- Dans le groupe Édition, cliquez sur l'option Rechercher et sélectionner
- Dans les options qui apparaissent dans la liste déroulante, cliquez sur l'option Remplacer. Cela ouvrira la boîte de dialogue Rechercher et remplacer
- Dans le champ « Rechercher », saisissez ,* (c'est-à-dire une virgule suivie d'un astérisque)
- Laissez le champ « Remplacer par » vide
- Cliquez sur le bouton Remplacer tout
Les étapes ci-dessus trouveraient la virgule dans l'ensemble de données et supprimeraient tout le texte après la virgule (y compris la virgule).
Étant donné que cela remplace le texte des cellules sélectionnées, c'est une bonne idée de copier le texte dans une autre colonne, puis d'effectuer cette opération de recherche et de remplacement, ou de créer une copie de sauvegarde de vos données afin que vous ayez les données d'origine intactes.
Comment cela marche-t-il?
* (signe astérisque) est un caractère générique qui peut représenter n'importe quel nombre de caractères.
Lorsque je l'utilise après la virgule (dans le champ "Rechercher"), puis que je clique sur le bouton Remplacer tout, il trouve la première virgule dans la cellule et la considère comme une correspondance.
En effet, le signe astérisque (*) est considéré comme une correspondance avec la chaîne de texte entière qui suit la virgule.
Ainsi, lorsque vous appuyez sur le bouton Remplacer tout, il remplace la virgule et tout le texte qui suit.
Noter: Cette méthode fonctionne bien alors vous n'avez qu'une virgule dans chaque cellule (comme dans notre exemple). Si vous avez plusieurs virgules, cette méthode trouvera toujours la première virgule, puis supprimera tout ce qui la suit. Vous ne pouvez donc pas utiliser cette méthode si vous souhaitez remplacer tout le texte après la deuxième virgule et conserver la première telle quelle.Au cas où vous voudriez supprimer tous les caractères avant la virgule, modifiez l'entrée du champ rechercher quoi en faisant signe l'astérisque avant la virgule (*, au lieu de ,*)
Supprimer du texte à l'aide de formules
Si vous avez besoin de plus de contrôle sur la façon de rechercher et de remplacer du texte avant ou après un caractère spécifique, il est préférable d'utiliser les formules de texte intégrées dans Excel.
Supposons que vous ayez l'ensemble de données ci-dessous où vous souhaitez supprimer tout le texte après la virgule.
Ci-dessous la formule pour le faire :
=GAUCHE(A2,TROUVER(",",A2)-1)
La formule ci-dessus utilise la fonction FIND pour trouver la position de la virgule dans la cellule.
Ce numéro de position est ensuite utilisé par la fonction GAUCHE pour extraire tous les caractères avant la virgule. Comme je ne veux pas de virgule dans le résultat, j'ai soustrait 1 de la valeur résultante de la formule de recherche.
C'était un scénario simple.
Prenons-en un un peu complexe.
Supposons que j'ai cet ensemble de données ci-dessous où je veux supprimer tout le texte après la deuxième virgule.
Voici la formule qui fera cela :
=GAUCHE(A2,RECHERCHER("!",REMPLACER(A2,",","!",2))-1)
Étant donné que cet ensemble de données comporte plusieurs virgules, je ne peux pas utiliser la fonction FIND pour obtenir la position de la première virgule et extraire tout ce qui se trouve à sa gauche.
Je dois en quelque sorte trouver la position de la deuxième virgule, puis extraire tout ce qui se trouve à gauche de la deuxième virgule.
Pour ce faire, j'ai utilisé la fonction SUBSTITUTE pour changer la deuxième virgule en un point d'exclamation. Maintenant, cela me donne un caractère unique dans la cellule. Je peux maintenant utiliser la position du point d'exclamation pour extraire tout à gauche de la deuxième virgule.
Cette position du point d'exclamation est utilisée dans la fonction GAUCHE pour tout extraire avant la deuxième virgule.
Jusqu'ici tout va bien!
Mais que se passe-t-il s'il y a un nombre incohérent de virgules dans l'ensemble de données.
Par exemple, dans l'ensemble de données ci-dessous, certaines cellules ont deux virgules et certaines cellules ont trois virgules, et je dois extraire tout le texte avant la dernière virgule.
Dans ce cas, je dois en quelque sorte déterminer la position de la dernière occurrence de la virgule, puis extraire tout ce qui se trouve à sa gauche.
Ci-dessous la formule qui fera cela
=GAUCHE(A2,RECHERCHER("!",REMPLACER(A2,",","!",LEN(A2)-LEN(REMPLACER(A2,",",""))))-1)
La formule ci-dessus utilise la fonction LEN pour trouver la longueur totale du texte dans la cellule ainsi que la longueur du texte sans la virgule.
Lorsque je soustrait ces deux valeurs, cela me donne le nombre total de virgules dans la cellule.
Donc, cela me donnerait 3 pour la cellule A2 et 2 pour la cellule A4.
Cette valeur est ensuite utilisée dans la formule SUBSTITUTE pour remplacer la dernière virgule par un point d'exclamation. Et puis vous pouvez utiliser la fonction gauche extraire tout ce qui se trouve à gauche du point d'exclamation (où se trouvait la dernière virgule)
Comme vous pouvez le voir dans les exemples, l'utilisation d'une combinaison de formules de texte vous permet de gérer de nombreuses situations différentes.
De plus, étant donné que le résultat est lié aux données d'origine, lorsque vous modifiez les données d'origine, le résultat est automatiquement mis à jour.
Supprimer du texte à l'aide de Flash Fill
Flash Fill est un outil qui a été introduit dans Excel 2013 et est disponible dans toutes les versions par la suite.
Cela fonctionne en identifiant des modèles lorsque vous entrez manuellement les données, puis en extrapolant pour vous donner les données de la colonne entière.
Donc, si vous souhaitez supprimer le texte avant ou après un caractère spécifique, il vous suffit de montrer à Flash Fairy à quoi ressemblerait le résultat (en le saisissant manuellement plusieurs fois), et le remplissage flash comprendrait automatiquement le motif et vous donnerait les résultats.
Laissez-moi vous montrer cela avec un exemple.
Ci-dessous, j'ai l'ensemble de données où je veux supprimer tout le texte après la virgule.
Voici les étapes pour le faire à l'aide de Flash Fill :
- Dans la cellule B2, qui est la colonne adjacente de nos données, entrez manuellement « Jeffery Haggins » (qui est le résultat attendu)
- Dans la cellule B3, entrez « Tim Scott » (qui est le résultat attendu pour la deuxième cellule)
- Sélectionnez la gamme B2:B10
- Cliquez sur l'onglet Accueil
- Dans le groupe Édition, cliquez sur l'option déroulante Remplir
- Cliquez sur Remplissage Flash
Les étapes ci-dessus vous donneraient le résultat ci-dessous :
Vous pouvez également utiliser le raccourci clavier Flash Fill Contrôle + E après avoir sélectionné les cellules dans la colonne résultat (Colonne B dans notre exemple)
Flash Fill est un outil formidable et dans la plupart des cas, il est capable de reconnaître le motif et de vous donner le bon résultat. mais dans certains cas, il peut ne pas être en mesure de reconnaître correctement le modèle et peut vous donner de mauvais résultats.
Assurez-vous donc de vérifier vos résultats de remplissage Flash
Et tout comme nous avons supprimé tout le texte après un caractère spécifique à l'aide du remplissage flash, vous pouvez utiliser les mêmes étapes pour supprimer le texte avant un caractère spécifique. montrez simplement le remplissage flash à quoi le résultat devrait ressembler à mon Internet manuellement dans la colonne adjacente, et il ferait le reste.
Supprimer du texte à l'aide de VBA (fonction personnalisée)
L'ensemble du concept de suppression du texte avant ou après un caractère spécifique dépend de la recherche de la position de ce caractère.
Comme si on le voyait ci-dessus, trouver la dernière occurrence de ce caractère bonne signifie utiliser une concoction de formules.
Si c'est quelque chose que vous devez faire assez souvent, vous pouvez simplifier ce processus en créant une fonction personnalisée à l'aide de VBA (appelée Fonctions définies par l'utilisateur).
Une fois créée, vous pouvez réutiliser cette fonction encore et encore. C'est également beaucoup plus simple et plus facile à utiliser (car la plupart des tâches lourdes sont effectuées par le code VBA dans le back-end).
Ci-dessous le code VBA que vous pouvez utiliser pour créer la fonction personnalisée dans Excel :
Function LastPosition(rCell As Range, rChar As String) 'Cette fonction donne la dernière position du caractère spécifié' Ce code a été développé par Sumit Bansal (https://trumpexcel.com) Dim rLen As Integer rLen = Len(rCell) For i = rLen To 1 Step -1 If Mid(rCell, i - 1, 1) = rChar Then LastPosition = i - 1 Exit Function End If Next i End Function
Vous devez placer le code VBA dans un module standard de l'éditeur VB ou dans le classeur de macros personnelles. Une fois que vous l'avez là, vous pouvez l'utiliser comme n'importe quelle autre fonction de feuille de calcul normale dans le classeur.
Cette fonction prend 2 arguments :
- La référence de cellule pour laquelle vous souhaitez rechercher la dernière occurrence d'un caractère ou d'une chaîne de texte spécifique
- Le caractère ou la chaîne de texte dont vous devez trouver la position
Supposons que vous ayez maintenant l'ensemble de données ci-dessous et que vous souhaitiez supprimer tout le texte après la dernière virgule et n'avoir que le texte avant la dernière virgule.
Voici la formule qui le fera :
=GAUCHE(A2,DernièrePosition(A2,",")-1)
Dans la formule ci-dessus, j'ai spécifié de trouver la position de la dernière virgule. Au cas où vous voudriez trouver la position d'un autre caractère ou d'une chaîne de texte, vous devez l'utiliser comme deuxième argument dans la fonction.
Comme vous pouvez le voir, c'est beaucoup plus court et plus facile à utiliser, par rapport à la formule de texte long que nous avons utilisée dans la section précédente
Si vous placez le code VBA dans un module d'un classeur, vous ne pourrez utiliser cette fonction personnalisée que dans ce classeur spécifique. Si vous souhaitez l'utiliser dans tous les classeurs de votre système, vous devez copier et coller ce code dans le classeur de macros personnelles.
Voici donc quelques-uns des e-mails que vous pouvez utiliser pour supprimer rapidement le texte avant ou après un caractère spécifique dans Excel.
S'il s'agit d'une chose simple et ponctuelle, vous pouvez le faire en utilisant rechercher et remplacer. et si c'est un peu plus complexe, alors vous devez utiliser une combinaison de formules Excel intégrées, ou même créer votre propre formule personnalisée à l'aide de VBA.
J'espère que vous avez trouvé ce tutoriel utile.
