Lorsque vous travaillez avec des données textuelles dans Excel, vous pouvez avoir besoin de supprimer le texte avant ou après un caractère spécifique ou une chaîne de texte.
Par exemple, si vous avez les données de nom et de désignation des personnes, vous pouvez vouloir supprimer la désignation après la virgule et ne garder que le nom (ou vice-versa où vous gardez la désignation et supprimez le nom).
Parfois, cela peut être fait avec une formule simple ou un Find and Replace rapide, et parfois, cela nécessite des formules plus complexes ou des solutions de contournement.
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).
Démarrons donc avec des exemples simples.
Ce tutoriel couvre :
Supprimer le texte après un caractère à l’aide de Rechercher et remplacer
Si vous voulez 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 les caractères de remplacement.
Supposons que vous avez un ensemble de données comme indiqué ci-dessous et que vous voulez supprimer la désignation après le caractère virgule et garder le texte avant la virgule.
Voici les étapes à suivre pour y parvenir :
- Copier et coller les données de la colonne A vers la colonne B (ceci afin de conserver également les données originales)
- Avec les cellules de la colonne B sélectionnées, cliquez sur l’onglet Accueil
- Dans le groupe Édition, cliquez sur l’option Rechercher & 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 quoi’, entrez ,* (c’est-à-dire, virgule suivie d’un signe 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).
Puisque cela remplace le texte des cellules sélectionnées, c’est une bonne idée de copier le texte dans une autre colonne et d’effectuer ensuite cette opération de recherche et de remplacement, ou de créer une copie de sauvegarde de vos données afin d’avoir les données originales intactes
Comment cela fonctionne-t-il ?
* (signe astérisque) est un caractère générique qui peut représenter un nombre quelconque de caractères.
Lorsque je l’utilise après la virgule (dans le champ ‘Rechercher quoi’) et que je clique ensuite sur le bouton Remplacer tout, il trouve la première virgule de la cellule et la considère comme une correspondance.
C’est parce que le signe astérisque (*) est considéré comme une correspondance à toute la chaîne de texte qui suit la virgule.
Donc, lorsque vous appuyez sur le bouton remplacer tout, il remplace la virgule et tout le texte qui suit.
Dans le cas où vous voulez supprimer tous les caractères avant la virgule, modifiez l’entrée du champ trouver quoi en ayant le signe 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 trouver et de remplacer le 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 avez l’ensemble de données ci-dessous Où vous voulez supprimer tout le texte après la virgule.
Voici la formule pour y parvenir :
=LEFT(A2,FIND(",",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 LEFT pour extraire tous les caractères avant la virgule. Comme je ne veux pas que la virgule fasse partie du résultat, j’ai soustrait 1 de la valeur résultante de la formule Find.
C’était un scénario simple.
Prenons un scénario légèrement plus 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 va faire cela :
=LEFT(A2,FIND("!",SUBSTITUTE(A2,",","!",2))-1)
Comme 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 d’une manière ou d’une autre trouver la position de la deuxième virgule et ensuite 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 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 ce qui se trouve à gauche de la deuxième virgule.
Cette position du point d’exclamation est utilisée dans la fonction LEFT pour extraire tout ce qui se trouve avant la deuxième virgule.
Pour l’instant tout va bien !
Mais que faire 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 d’autres ont trois virgules, et j’ai besoin d’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.
Voici la formule qui permettra de le faire
=LEFT(A2,FIND("!",SUBSTITUTE(A2,",","!",LEN(A2)-LEN(SUBSTITUTE(A2,",",""))))-1)
La formule ci-dessus utilise la fonction LEN pour trouver la longueur globale du texte dans la cellule ainsi que la longueur du texte sans la virgule.
Quand je soustrais 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 au sein de la formule SUBSTITUTE pour remplacer la dernière virgule par un point d’exclamation. Et ensuite, vous pouvez utiliser la fonction left pour extraire tout ce qui se trouve à gauche du point d’exclamation (qui est l’endroit 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.
En outre, comme le résultat est lié aux données d’origine, lorsque vous modifiez ces dernières, le résultat serait automatiquement mis à jour.
Supprimer du texte à l’aide de Flash Fill
Flash Fill est un outil qui a été introduit dans Excel 2013 et qui est disponible dans toutes les versions ultérieures.
Il fonctionne en identifiant des modèles lorsque vous saisissez manuellement les données, puis en extrapolant pour vous donner les données de la colonne entière.
Donc, si vous voulez 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 flash fill comprendrait automatiquement le modèle et vous donnerait les résultats.
Laissez-moi vous le montrer avec un exemple.
Ci-après, j’ai l’ensemble de données où je veux supprimer tout le texte après la virgule.
Voici les étapes pour faire cela en utilisant Flash Fill :
- Dans la cellule B2, qui est la colonne adjacente de nos données, saisissez manuellement ‘Jeffery Haggins’ (qui est le résultat attendu)
- Dans la cellule B3, saisissez ‘Tim Scott’ (qui est le résultat attendu pour la deuxième cellule)
- Sélectionnez la plage B2 :B10
- Cliquez sur l’onglet Accueil
- Dans le groupe Édition, cliquez sur l’option déroulante Remplissage
- Cliquez sur Remplissage flash
Les étapes ci-dessus vous donneraient le résultat indiqué ci-dessous :
Vous pouvez également utiliser le raccourci clavier Flash Fill Control + E après avoir sélectionné les cellules de la colonne de résultat (Colonne B dans notre exemple)
Le Flash Fill est un outil merveilleux 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 le motif correctement et vous donner des résultats erronés.
Donc, assurez-vous de vérifier deux fois vos résultats de Flash Fill
Et tout comme nous avons supprimé tout le texte après un caractère spécifique en utilisant flash fill, vous pouvez utiliser les mêmes étapes pour supprimer le texte avant un caractère spécifique. il suffit de montrer à flash fill comment le résultat devrait ressembler à mon Internet manuellement dans la colonne adjacente, et il ferait le reste.
Supprimer le texte en utilisant VBA (fonction personnalisée)
Tout le 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 vu ci-dessus, trouver la dernière occurrence de ce caractère bon 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 User Defined Functions).
Une fois créée, vous pouvez réutiliser cette fonction encore et encore. C’est également beaucoup plus simple et facile à utiliser (car la plupart des tâches lourdes sont effectuées par le code VBA en arrière-plan).
Vous trouverez 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)'This function gives the last position of the specified character'This code has been developed by Sumit Bansal (https://trumpexcel.com)Dim rLen As IntegerrLen = Len(rCell)For i = rLen To 1 Step -1If Mid(rCell, i - 1, 1) = rChar ThenLastPosition = i - 1Exit FunctionEnd IfNext iEnd Function
Vous devez placer le code VBA dans un module régulier 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 régulière de feuille de calcul dans le classeur.
Cette fonction prend 2 arguments :
- La référence de la cellule pour laquelle vous voulez trouver 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 avez maintenant l’ensemble de données ci-dessous et que vous voulez 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 permettra de le faire :
=LEFT(A2,LastPosition(A2,",")-1)
Dans la formule ci-dessus, j’ai spécifié de trouver la position de la dernière virgule. Dans le cas où vous voulez 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, cette formule est beaucoup plus courte et plus facile à utiliser, par rapport à la longue formule de texte que nous avons utilisée la section précédente
Si vous mettez le code VBA dans un module d’un classeur, vous ne pourriez utiliser cette fonction personnalisée que dans ce classeur spécifique. Si vous voulez l’utiliser dans tous les classeurs de votre système, vous devez copier et coller ce code dans le classeur de macros personnelles.
Ce sont donc quelques-uns des mails que vous pouvez utiliser pour supprimer rapidement le texte avant ou après un caractère spécifique dans Excel.
Si c’est une chose simple et unique, alors vous pouvez le faire en utilisant la recherche et le remplacement. ce qui 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 en utilisant VBA.
J’espère que vous avez trouvé ce tutoriel utile.
D’autres tutoriels Excel que vous pourriez apprécier :
- Compter les caractères d’une cellule (ou d’une plage de cellules) à l’aide de formules dans Excel
- Comment supprimer le premier caractère d’une chaîne de caractères dans Excel
- Comment supprimer la virgule dans… Excel (à partir de texte et de nombres)
- Comment extraire le premier mot d’une chaîne de texte dans Excel
- Séparer le prénom et le nom de famille dans Excel (séparer les noms à l’aide de formules)
.