août 24, 2025

En utilisant des fonctions de remplacement et de substitut Excel

Le tutoriel explique les fonctions de remplacement et de substitut Excel par des exemples d’utilisations. Voir comment utiliser la fonction de remplacement par des chaînes de texte, des nombres et des dates, et comment nidiquer plusieurs fonctions de remplacement ou de substitut dans une formule.

La semaine dernière, nous avons discuté de diverses façons d’utiliser des fonctions de recherche et de recherche dans vos feuilles de calcul Excel. Aujourd’hui, nous allons approfondir deux autres fonctions pour remplacer le texte dans une cellule en fonction de son emplacement ou remplacer une chaîne de texte par une autre en fonction du contenu. Comme vous l’avez peut-être deviné, je parle des fonctions de remplacement et de substitut Excel.

Excel Remplacer la fonction

La fonction de remplacement dans Excel vous permet d’échanger un ou plusieurs caractères dans une chaîne de texte avec un autre caractère ou un ensemble de caractères.

Remplacer (old_text, start_num, num_chars, new_text)

Comme vous le voyez, la fonction de remplacement Excel a 4 arguments, qui sont tous requis.

Old_text – Le texte d’origine (ou une référence à une cellule avec le texte d’origine) dans lequel vous souhaitez remplacer certains caractères.
Start_num – La position du premier caractère dans Old_text que vous souhaitez remplacer.
Num_chars – Le nombre de caractères que vous souhaitez remplacer.
New_text – Le texte de remplacement.

Par exemple, pour changer le mot « soleil » à « fils« , vous pouvez utiliser la formule suivante:

=REPLACE("sun", 2, 1, "o")

Et si vous mettez le mot d’origine dans une cellule, disons A2, vous pouvez fournir la référence de cellule correspondante dans l’argument Old_text:

=REPLACE(A2, 2, 1, "o")

Note. Si l’argument start_num ou num_chars est négatif ou non numerique, une formule Excel remplace le #Value! erreur.

Utilisation de la fonction Excel Remplacer par des valeurs numériques

La fonction de remplacement dans Excel est conçue pour fonctionner avec des chaînes de texte. Bien sûr, vous pouvez l’utiliser pour remplacer les caractères numériques qui font partie d’une chaîne de texte, par exemple:

=REPLACE(A2, 7, 4, "2016")

Notez que nous enfermons « 2016 » en double guillemets comme vous le faites habituellement avec les valeurs de texte.

De la même manière, vous pouvez remplacer un ou plusieurs chiffres dans un nombre. Par exemple:

=REPLACE(A4, 4, 4,"6")

Et encore une fois, vous devez enfermer la valeur de remplacement en doubles devis (« 6 »).

Note. Une formule Excel Remplacer renvoie toujours un chaîne de textepas numéro. Dans la capture d’écran ci-dessus, remarquez l’alignement gauche de la valeur de texte retournée en B2 et comparez-la au numéro d’origine aligné droit dans A2. Et parce que c’est une valeur de texte, vous ne pourrez pas l’utiliser dans d’autres calculs à moins que vous ne le convertiez en nombre, par exemple en multipliant par 1 ou en utilisant toute autre méthode décrite dans la façon de convertir du texte en numéro.

Utilisation de la fonction de remplacement Excel par des dates

Comme vous venez de le voir, la fonction de remplacement fonctionne correctement par des nombres, sauf qu’il renvoie une chaîne de texte 🙂 En rappelant que dans le système Excel interne, les dates sont stockées sous forme de nombres, vous pouvez essayer d’utiliser certaines formules de remplacement aux dates. Les résultats seraient assez embarrassants.

Par exemple, vous avez une date dans A2, disons 1-oct-14, et vous voulez changer « Octobre » à « Nov« . Donc, vous écrivez la formule Remplacer (A2, 4, 3, » nov « ) qui dit à Excel de remplacer 3 caractères dans les cellules A2 commençant par le 4^ème Char… et a obtenu le résultat suivant:

Pourquoi c’est? Parce que « 01-oct-14 » n’est qu’une représentation visuelle du numéro de série sous-jacent (41913) qui représente la date. Ainsi, notre formule de remplacement modifie les 3 derniers chiffres du numéro de série ci-dessus « Nov« Et renvoie la chaîne de texte » 419nov « .

Pour que la fonction Excel remplace correctement les dates, vous pouvez d’abord convertir les dates en chaînes de texte en utilisant la fonction texte ou toute autre technique démontrée dans la façon de convertir la date en texte dans Excel. Alternativement, vous pouvez intégrer la fonction de texte directement dans l’argument Old_Text de la fonction de remplacement:

=REPLACE(TEXT(A2, "dd-mmm-yy"), 4, 3, "Nov")

N’oubliez pas que le résultat de la formule ci-dessus est un chaîne de texteet donc cette solution ne fonctionne que si vous ne prévoyez pas d’utiliser les dates modifiées dans d’autres calculs. Si vous avez besoin de dates plutôt que de chaînes de texte, utilisez la fonction DateValue pour remettre les valeurs renvoyées par la fonction Excel Remplacer les dates:

=DATEVALUE(REPLACE(TEXT(A2, "dd-mmm-yy"), 4, 3, "Nov"))

Fonctions de remplacement imbriquées pour effectuer plusieurs remplacements dans une cellule

Très souvent, vous devrez peut-être effectuer plus d’un remplacement dans la même cellule. Bien sûr, vous pouvez effectuer un remplacement, sortir un résultat intermédiaire dans une colonne supplémentaire, puis utiliser à nouveau la fonction de remplacement. Cependant, un moyen meilleur et plus professionnel est d’utiliser Fonctions de remplacement imbriquées Cela vous permet d’effectuer plusieurs remplacements par une seule formule. Dans ce contexte, le «nidification» signifie placer une fonction dans une autre.

Considérez l’exemple suivant. En supposant que vous avez une liste de numéros de téléphone dans la colonne A formaté comme « 123456789 » et que vous souhaitez les faire ressembler davantage à des numéros de téléphone en ajoutant des traits de gamme. En d’autres termes, votre objectif est de transformer « 123456789 » en « 123-456-789 ».

Insérer le premier trait d’union est facile. Vous écrivez une formule excel habituelle qui remplace zéro personnages Avec un trait d’union, c’est-à-dire un trait d’union dans le 4^ème position dans une cellule:

=REPLACE(A2,4,0,"-")

Le résultat de la formule de remplacement ci-dessus est le suivant:

D’accord, et maintenant nous devons insérer un trait d’union de plus dans le 8^ème position. Pour ce faire, vous placez la formule ci-dessus dans une autre fonction de remplacement Excel. Plus précisément, vous l’intégrez dans le old_text Argument de l’autre fonction, de sorte que la deuxième fonction de remplacement gère la valeur renvoyée par le premier remplacement, et non la valeur de la cellule A2:

=REPLACE(REPLACE(A2,4,0,"-"),8,0,"-")

En conséquence, vous obtenez les numéros de téléphone dans le formatage souhaité:

De la même manière, vous pouvez utiliser des fonctions de remplacement imbriquées pour faire en sorte que les chaînes de texte ressemblent à des dates en ajoutant une barre oblique avant (/) le cas échéant:

=(REPLACE(REPLACE(A2,3,0,"https://www.ablebits.com/"),6,0,"https://www.ablebits.com/"))

De plus, vous pouvez convertir les chaînes de texte en dates réelles en emballage la formule de remplacement ci-dessus par la fonction DateValue:

=DATEVALUE(REPLACE(REPLACE(A2,3,0,"https://www.ablebits.com/"),6,0,"https://www.ablebits.com/"))

Et naturellement, vous n’êtes pas limité dans le nombre de fonctions que vous pouvez nicher dans une formule (les versions modernes d’Excel 2010, 2013 et 2016 permettent jusqu’à 8192 caractères et jusqu’à 64 fonctions imbriquées dans une formule).

Par exemple, vous pouvez utiliser 3 fonctions de remplacement imbriquées pour avoir un nombre en A2 apparaissant comme la date et l’heure:

=REPLACE(REPLACE(REPLACE(REPLACE(A2,3,0,"https://www.ablebits.com/") ,6,0,"https://www.ablebits.com/"), 9,0, " "), 12,0, ":")

Remplacement d’une chaîne qui apparaît dans une position différente dans chaque cellule

Jusqu’à présent, dans tous les exemples, nous avons traité des valeurs de nature similaire et avons fait des remplacements dans la même position dans chaque cellule. Mais les tâches réelles sont souvent plus compliquées que cela. Dans vos feuilles de travail, les personnages à remplacer ne peuvent pas nécessairement apparaître au même endroit dans chaque cellule, et donc vous devrez donc Trouvez la position du premier caractère qui doit être remplacé. L’exemple suivant montrera de quoi je parle.

En supposant que vous avez une liste d’e-mails adressant dans la colonne A. et le nom d’une entreprise est passé de « ABC » à, disons, « BCA ». Vous devez donc mettre à jour tous les e-mails des clients en conséquence.

Mais le problème est que les noms des clients sont d’une longueur différente, et c’est pourquoi vous ne pouvez pas spécifier exactement où commence le nom de l’entreprise. En d’autres termes, vous ne savez pas quelle valeur fournir dans l’argument start_num de la fonction de remplacement Excel. Pour le découvrir, utilisez la fonction Excel Find pour déterminer la position du premier char dans la chaîne « @abc »:

=FIND("@abc",A2)

Et puis, fournissez la fonction de recherche ci-dessus dans l’argument start_num de votre formule de remplacement:

=REPLACE(A2, FIND("@abc",A2), 4, "@bca")

Conseil. Nous incluons « @ » dans notre formule Excel Rechercher et remplacer pour éviter les remplacements accidentels dans la partie du nom des adresses e-mail. Bien sûr, il y a de très minces chances que de tels matchs se produisent, et vous voudrez peut-être être sûr.

Comme vous le voyez dans la capture d’écran suivante, la formule n’a aucun problème à trouver et à remplacer l’ancien texte par le nouveau. Cependant, si la chaîne de texte à remplacer n’est pas trouvée, la formule renvoie le #Value! erreur:

Et nous voulons que la formule renvoie l’adresse e-mail d’origine au lieu de l’erreur. Alors, joignons notre formule Find & Replacez dans la fonction IFERROR:

=IFERROR(REPLACE(A2, FIND("@abc",A2), 4, "@bca"),A2)

Et cette formule améliorée fonctionne parfaitement, n’est-ce pas?

Une autre application pratique de la fonction de remplacement consiste à capitaliser la première lettre dans une cellule. Chaque fois que vous traitez une liste de noms, de produits, etc., vous pouvez utiliser la formule liée ci-dessus pour modifier la première lettre en majuscule.

Conseil. Si vous souhaitez effectuer les remplacements dans les données d’origine, un moyen plus facile consisterait à utiliser la boîte de dialogue Excel Find et remplacer.

Fonction de substitut Excel

La fonction de substitut dans Excel remplace un ou plusieurs instances d’un caractère ou d’une chaîne de texte donnée par un ou des caractères spécifiés.

La syntaxe de la fonction de substitution Excel est la suivante:

Substitut (texte, old_text, new_text, [instance_num])

Les trois premiers arguments sont requis et le dernier est facultatif.

Texte – Le texte original dans lequel vous souhaitez remplacer les caractères. Peut être fourni en tant que chaîne de test, référence cellulaire ou résultat d’une autre formule.
Old_text – Le (s) personnage (s) que vous souhaitez remplacer.
New_text – Le ou les nouveaux caractères pour remplacer Old_text par.
Instance_num – L’occurrence d’Old_text que vous souhaitez remplacer. S’il est omis, chaque occurrence de l’ancien texte sera modifiée en nouveau texte.

Par exemple, toutes les formules ci-dessous remplacent « 1 » par « 2 » dans la cellule A2, mais renvoient différents résultats en fonction du numéro que vous fournissez dans le dernier argument:

=SUBSTITUTE(A2, "1", "2", 1) – remplace la première occurrence de « 1 » avec « 2 ».

=SUBSTITUTE(A2, "1", "2", 2) – remplace la deuxième occurrence de « 1 » avec « 2 ».

=SUBSTITUTE(A2, "1", "2") – remplace toutes les occurrences de « 1 » avec « 2 ».

En pratique, la fonction de substitution est également utilisée pour éliminer les caractères indésirables des cellules. Pour des exemples réels, veuillez voir:

Note. La fonction de substitut dans Excel est sensible aux majuscules et minuscules. Par exemple, la formule suivante remplace toutes les instances de la majuscule « x » par « y » dans la cellule A2, mais elle ne remplacera aucune instance du « x » en minuscules.

Remplacer plusieurs valeurs par une seule formule (substitut imbriqué)

Comme c’est le cas avec la fonction de remplacement Excel, vous pouvez nicher plusieurs fonctions de substitut dans une seule formule pour effectuer plusieurs substitutions à la fois, c’est-à-dire remplacer plusieurs caractères ou sous-chaînes avec une seule formule.

En supposant que vous avez une chaîne de texte comme « PR1, ML1, T1« Dans la cellule A2, où » PR « signifie » Project, « ML » signifie « Milestone » et « T » signifie « tâche ». Ce que vous voulez, c’est remplacer les trois codes par des noms complets. Pour y parvenir, vous pouvez écrire 3 formules de substitut différentes:

=SUBSTITUTE(A2,"PR", "Project ")

=SUBSTITUTE(A2, "ML", "Milestone ")

=SUBSTITUTE(A2, "T", "Task ")

Et puis les nid les uns dans les autres:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"PR","Project "),"ML","Milestone "),"T","Task ")

Notez que nous avons ajouté un espace à la fin de chaque argument New_text pour une meilleure lisibilité.

Pour apprendre d’autres façons de remplacer plusieurs valeurs à la fois, veuillez voir comment faire de la masse trouver et remplacer dans Excel.

C’est ainsi que vous utilisez le substitut et remplacez les fonctions dans Excel. Espérons que ces exemples s’avéreront utiles pour résoudre vos tâches. Je vous remercie d’avoir lu et j’espère voir notre blog la semaine prochaine!