Fonction SUBSTITUE Excel - Rechercher et remplacer du texte

Télécharger un exemple de classeur

Téléchargez l'exemple de classeur

Ce didacticiel montre comment utiliser le Fonction de REMPLACEMENT d'Excel dans Excel pour rechercher et remplacer du texte.

Présentation de la fonction SUBSTITUT

La fonction SUBSTITUTE Recherche et remplace le texte existant par une nouvelle chaîne de texte. Sensible aux majuscules et minuscules.

Pour utiliser la fonction de feuille de calcul Excel SUBSTITUE, sélectionnez une cellule et tapez :

(Remarquez comment les entrées de formule apparaissent)

Fonction SUBSTITUTE Syntaxe et entrées :

1	= SUBSTITUTE(texte,ancien_texte,nouveau_texte,numéro_instance)

texte - La chaîne de texte d'origine.

ancien_texte - La chaîne de texte que vous souhaitez rechercher et remplacer.

nouveau_test - Le texte de remplacement.

num_instance - Optionnel. Le numéro d'instance à remplacer. Si vide, toutes les instances sont remplacées.

Qu'est-ce que le SUBSTITUT ?

La fonction SUBSTITUTE dans Excel est utilisée pour remplacer une chaîne de caractères dans une chaîne de texte par une chaîne de texte spécifiée différente.

Regardons l'exemple suivant en remplaçant la chaîne "quick brown" par "slow white".

1	=REMPLACER(C2,C3,C4)

SUBSTITUTE regarde dans la chaîne en C2, trouve « quick brown » et le remplace par « slow white ».

Comment utiliser SUBSTITUT

La fonction SUBSTITUTE prend 3 arguments obligatoires et 1 facultatif :

• Texte : Le texte que vous souhaitez remplacer les caractères
• Old_text : le texte que vous souhaitez remplacer
• New_text : le texte par lequel vous souhaitez remplacer Old_text
• Instance_num (facultatif) : spécifie l'occurrence de Old_text que vous souhaitez remplacer par New_text. Par défaut, toutes les occurrences de Old_text seront remplacées si une instance n'est pas spécifiée.

Imbrication SUBSTITUT

Et si on devait substituer deux parties de la même corde ? Voyons comment nous combinerions deux fonctions SUBSTITUTE.

1	=REMPLACER(REMPLACER(C2,C3,C4),C5,C6)

Remarquez comment le résultat de SUBSTITUTE(C2,C3,C4) est utilisé comme premier argument pour le second SUBSTITUTE. Si nous décomposons la formule :

=SUBSTITUTE(C2,C3,C4) est évalué à « Le blanc lent le renard saute par-dessus le chien paresseux ».

Ensuite, SUBSTITUE ("Le renard blanc lent saute par-dessus le chien paresseux", C5,C6) qui évalue à "Le blanc lent Lion saute par-dessus le chien paresseux ».

Différence entre REMPLACER et REMPLACER

Une fonction similaire à REMPLACER est REMPLACER. Dans la plupart des cas, ceux-ci peuvent être utilisés de manière interchangeable, cependant, la règle empirique suivante s'applique.

La fonction SUBSTITUE doit être utilisée lorsque le chaîne de texte être remplacé est connu, ou un mot entier ou des mots sont remplacés, comme notre exemple de renard utilisé plus tôt.

1	=REMPLACER(C2,C3,C4)

La fonction REMPLACER doit être utilisée lorsque le position des caractères du texte à remplacer dans la chaîne sont connus, ou lorsqu'une partie d'un mot ou d'une chaîne est remplacée. Par exemple, supprimer un trait d'union dans une chaîne de nombres.

La fonction REPLACE peut également être imbriquée comme SUBSTITUTE cependant, il est important de noter que la position relative du caractère de début peut changer en fonction de la longueur de la chaîne à remplacer dans la première étape de la formule imbriquée.

Par exemple, remplacer « marron rapide » par « blanc lent » revient à remplacer 11 caractères par 10 caractères. Par conséquent, s'il est imbriqué, le caractère de départ du deuxième REPLACE serait à 1 caractère des positions de caractère initiales.

REMPLACER Conseils

• SUBSTITUTE peut être utilisé sur des sous-chaînes :
• N'oubliez pas que vous devez spécifier un instance_num si vous essayez de substituer un ensemble de caractères qui se répètent dans la chaîne :

Notez que le 3030 est incorrect car « 20 » et « 20 » dans « 2020 » ont été remplacés par « 30 ».

Intéressé par les fonctions Plus de texte ?

Consultez nos autres articles pour en savoir plus sur REPLACE ou sur l'utilisation d'autres fonctions de texte dans Excel telles que LEFT, MID et RIGHT.

REMPLACER dans Google Sheets

La fonction SUBSTITUTE fonctionne exactement de la même manière dans Google Sheets que dans Excel :

Notes complémentaires

La fonction SUBSTITUTE est sensible à la casse !. Vous pouvez utiliser les fonctions LOWER ou UPPER pour convertir vos chaînes de texte en une casse cohérente avant d'utiliser la fonction SUBSTITUTE. Ensuite, si vous le souhaitez, vous pouvez utiliser la fonction PROPER pour mettre en majuscule la première lettre de chaque mot.

Alternativement, vous pouvez utiliser les fonctions RECHERCHER et REMPLACER pour simuler un SUBSTITUT non sensible à la casse. Utilisez la fonction SEARCH pour trouver la position de départ de la chaîne de texte. La fonction SEARCH n'est pas sensible à la casse. Utilisez ensuite la fonction REPLACE pour remplacer le texte. Vous devrez utiliser le résultat de la fonction SEARCH comme entrée start_num dans REPLACE. Vous devrez également définir le nombre de caractères à remplacer ( num_chars). Vous pouvez les compter manuellement ou utiliser la fonction LEN pour compter le nombre de caractères. Exemple:

Substituer - Supprimer les traits d'union

Une question courante est de savoir comment supprimer les tirets du texte. L'astuce consiste à remplacer le trait d'union par rien.

Pour cet exemple, supposons que la cellule A1 contienne du texte avec des tirets. Voici comment les supprimer

1. Dans une cellule d'aide vide, tapez =SUBSTITUTE(
2. Cliquez sur la cellule dans laquelle vous souhaitez substituer des caractères ou A1 (cela remplit automatiquement votre formule)
3. Tapez, (virgule)
4. Tapez "-"
5. Tapez, (virgule)
6. Tapez "" et appuyez sur Entrée
Votre résultat final dans votre cellule d'assistance devrait ressembler à ceci :
=REMPLACER(A1,"-","")

3 autres exemples de substitution :

En regardant la cellule B3, nous voulons remplacer le mot « malade » par « grande ». On peut utiliser l'expression suivante :

1	=REMPLACER(B3,,"malade","grand",1)

Si le texte que nous voulons remplacer apparaît plus d'une fois, nous devons spécifier l'instance que nous voulons dire. Dans le deuxième exemple, nous avons deux occurrences du mot « ancien ». Donc, si nous voulons remplacer la deuxième instance par le mot "gris", nous devons utiliser :

1	=REMPLACER(B4,"vieux","gris",2)

Notez que si l'occurrence n'est pas spécifiée, toutes les instances sont remplacées. Donc:

1	=REMPLACER(B4,"vieux","gris")

Se débarrasserait de toutes les instances du mot « vieux » et les remplacerait par le mot « gris ». Notez que si l'ancien texte est introuvable, la chaîne reste inchangée. Donc en regardant le dernier exemple :

1	=REMPLACER(B5,"noir","gris",1)

Cela signifie que nous essayons de remplacer le mot « noir » par le mot « gris ». Cependant, comme « noir » ne se produit pas, la chaîne d'origine est restée inchangée :

Pour télécharger le fichier .XLSX de cet article, cliquez ici

REMPLACER Exemples en VBA

Vous pouvez également utiliser la fonction SUBSTITUTE dans VBA. Taper:
application.worksheetfunction.substitute(text,old_text,new_text,instance_num)
Pour les arguments de la fonction (texte, etc.), vous pouvez soit les saisir directement dans la fonction, soit définir des variables à utiliser à la place.

Lorsque le code suivant est exécuté

1	Range("A1") = WorksheetFunction.Substitute(Range("A1"), "using", "", 1)

Le contenu de la cellule A1 deviendra :

Ainsi, le mot « using » a été remplacé par une chaîne vide. Le dernier paramètre (1) indique quelle instance du texte doit être remplacée, au cas où le texte est présent plus d'une fois. Ainsi, par exemple si nous exécutons l'instruction suivante

1	Range("A1") = WorksheetFunction.Substitute(Range("A1"), "le", "un", 1)

La cellule A1 ressemblera à ceci

Notez que la première occurrence de « le » a été remplacée par « un », tandis que la seconde « le » n'a pas été affectée. Si nous avions omis le dernier paramètre, les deux mots « les » auraient été remplacés

Revenir à la liste de toutes les fonctions dans Excel