Remplacer du texte avec SUBSTITUTE

Les données nécessitent souvent une recherche et un remplacement dans les formules : supprimer des tirets de numéros de téléphone, remplacer un mot ou supprimer des symboles superflus.

La fonction SUBSTITUTE recherche un fragment de texte dans une cellule et remplace chaque occurrence par un nouveau texte, sans modifier la cellule d'origine.

C'est l'équivalent fondé sur une formule de la fonction Rechercher et remplacer, mais la mise à jour se fait automatiquement lorsque vos données changent.

SUBSTITUTE accepte jusqu'à quatre arguments :

• texte la chaîne ou la cellule d'origine
• ancien_texte le texte à rechercher
• nouveau_texte le texte à mettre à sa place
• numéro_instance (facultatif) l'occurrence à remplacer

La structure est =SUBSTITUTE(text, old_text, new_text, [instance_num]). Si vous omettez le dernier argument, toutes les occurrences sont remplacées.

Une astuce courante consiste à remplacer quelque chose par rien pour le supprimer. Pour cela, définissez le nouveau texte sur une chaîne vide "".

Si A2 contient 555-123-4567, la formule ci-dessous supprime chaque tiret et renvoie 5551234567.

Ce modèle nettoie les numéros de téléphone, les identifiants et d'autres valeurs contenant de la ponctuation.

SUBSTITUTE fonctionne aussi sur des mots entiers, et pas seulement sur des caractères isolés.

Si A2 contient Rapport annuel 2023, vous pouvez mettre à jour l'année en remplaçant le mot :

Le résultat est Rapport annuel 2024. Chaque occurrence correspondante dans la cellule est modifiée.

Important : SUBSTITUTE fait correspondre le texte exactement, y compris les majuscules et les minuscules.

Si A2 contient Chat chat CAT, alors =SUBSTITUTE(A2, "cat", "dog") ne modifie que le chat en minuscules et donne Chat chien CAT.

Si vous avez besoin d'un remplacement insensible à la casse, normalisez d'abord la casse avec LOWER ou UPPER, ou utilisez les fonctions d'expressions régulières de la leçon suivante dans les feuilles de calcul.

Le quatrième argument facultatif, numéro_instance, vous permet de cibler une seule occurrence au lieu de toutes.

Si A2 contient a-b-c-d et que vous souhaitez modifier uniquement le deuxième tiret, définissez le numéro d'instance sur 2.

Le résultat est a-b/c-d : seul le deuxième tiret devient une barre oblique.

Imaginez un montant importé stocké sous forme de texte : $1,250.00. Pour le convertir en nombre utilisable, vous devez supprimer le signe dollar et la virgule.

Imbriquez deux appels à SUBSTITUTE, l'un dans l'autre, pour supprimer ces deux caractères.

L'appel interne supprime la virgule ; l'appel externe supprime le signe dollar, ce qui laisse 1250.00.

Vous pouvez imbriquer autant d'appels à SUBSTITUTE que nécessaire, chacun nettoyant un caractère différent.

Pour normaliser un code désordonné contenant des espaces, des points et des barres obliques en tirets propres, enchaînez trois substitutions.

Chaque niveau transmet son résultat au suivant, construisant progressivement la chaîne finale nettoyée.

Ne confondez pas SUBSTITUTE avec la fonction similaire REPLACE.

• SUBSTITUTE remplace le texte en faisant correspondre son contenu (trouver ce mot, le remplacer).
• REPLACE remplace le texte selon sa position (remplacer 4 caractères à partir de la position 5).

Utilisez SUBSTITUTE lorsque vous savez quoi rechercher, et REPLACE lorsque vous savez où se trouve le texte.

Astuce intéressante : combinez SUBSTITUTE avec LEN pour compter combien de fois un élément apparaît.

Soustrayez de la longueur d'origine la longueur obtenue après suppression d'un caractère. Si A2 contient a,b,c,d, cette méthode compte les virgules et renvoie 3.

Cette astuce est idéale pour compter les éléments d'une liste délimitée.

Supposons que A2 contienne des numéros désordonnés avec des points et des espaces, comme 555.123 4567. Vous voulez obtenir un format uniforme avec des tirets : 555-123-4567.

Commencez par supprimer les points et les espaces, puis vous pourriez réinsérer des tirets ; pour le moment, contentez-vous d'uniformiser les séparateurs en les remplaçant par des tirets.

Des substitutions imbriquées uniformisent les séparateurs superflus, quels qu'ils soient.

Testez votre compréhension de SUBSTITUTE.

Vous avez appris à rechercher et à remplacer du texte dans les formules :

• =SUBSTITUTE(text, old_text, new_text, [instance_num])
• Utilisez "" comme nouveau texte pour supprimer des caractères.
• La fonction est sensible à la casse ; normalisez d'abord la casse si nécessaire.
• L'argument numéro d'instance facultatif cible une seule occurrence.
• Imbriquez les appels pour nettoyer plusieurs caractères ; associez-les à LEN pour compter les occurrences.

Vous avez terminé la section « Joindre et modifier du texte ». Excellent travail pour créer des données propres et cohérentes !