Comment utiliser SUBSTITUTE dans Google Sheets : guide complet

SUBSTITUTE (SUBSTITUE dans un Google Sheets en français) remplace un texte précis à l'intérieur d'une chaîne par un nouveau texte. C'est l'outil de nettoyage simple, à correspondance exacte : supprimer tous les espaces, échanger un caractère contre un autre, ou ne modifier que la deuxième occurrence d'un mot. Quand le texte à remplacer a une forme plutôt qu'une valeur fixe, utilisez plutôt REGEXREPLACE.

Syntaxe

=SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])

text_to_search : Le texte à modifier.
search_for : Le texte exact à trouver.
replace_with : Le texte à mettre à la place.
occurrence_number [Facultatif] : Quelle occurrence remplacer. Si omis, toutes les occurrences sont remplacées.

SUBSTITUTE tient compte de la casse : "a" ne correspond pas à "A".

Exemples de base

=SUBSTITUTE("Bonjour Monde", "Monde", "Sheets")

Renvoie Bonjour Sheets.

=SUBSTITUTE("a-b-c-d", "-", "/")

Renvoie a/b/c/d : chaque tiret est remplacé.

=SUBSTITUTE("a-b-c-d", "-", "/", 2)

Renvoie a-b/c-d : seul le deuxième tiret est remplacé.

Formules pratiques

Supprimer des caractères

Remplacez par une chaîne vide pour supprimer :

=SUBSTITUTE(A2, " ", "")

Supprime tous les espaces, y compris ceux entre les mots.

=SUBSTITUTE(A2, "-", "")

Retire les tirets d'un numéro de téléphone ou d'un identifiant.

Supprimer plusieurs caractères

Imbriquez les appels à SUBSTITUTE :

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "(", ""), ")", ""), "-", "")

Au-delà de deux ou trois caractères, REGEXREPLACE(A2, "[()-]", "") est plus courte.

Corriger les espaces insécables

Le texte copié depuis des pages web contient souvent des espaces insécables (CHAR(160)) que TRIM ne sait pas supprimer et qui font échouer les recherches. Remplacez-les par des espaces normaux :

=TRIM(SUBSTITUTE(A2, CHAR(160), " "))

Remplacer les sauts de ligne

=SUBSTITUTE(A2, CHAR(10), " ")

Transforme les sauts de ligne dans une cellule en espaces. Utilisez CHAR(10) pour un saut de ligne.

Convertir les virgules décimales en points

=VALUE(SUBSTITUTE(A2, ",", "."))

Utile quand des nombres importés d'un autre paramètre régional arrivent sous forme de texte avec des virgules.

Compter combien de fois un texte apparaît

=(LEN(A2) - LEN(SUBSTITUTE(A2, "a", ""))) / LEN("a")

La longueur perdue en retirant une chaîne, divisée par la longueur de cette chaîne, donne le nombre d'occurrences. Notez que c'est sensible à la casse ; entourez A2 de LOWER pour un décompte insensible à la casse.

Compter les mots d'une cellule

=LEN(TRIM(A2)) - LEN(SUBSTITUTE(TRIM(A2), " ", "")) + 1

Remplacer la dernière occurrence

Remplacez la n-ième occurrence où n est le nombre total :

=SUBSTITUTE(A2, "-", "/", LEN(A2) - LEN(SUBSTITUTE(A2, "-", "")))

Appliquer à toute une colonne

=ARRAYFORMULA(SUBSTITUTE(A2:A, "-", ""))

Erreurs courantes

Rien ne change. Casse différente ("US" face à "us"), ou le texte contient un espace insécable ou un caractère invisible. Vérifiez avec CODE(MID(A2, n, 1)).
Les nombres deviennent du texte. Le résultat est du texte, même quand il ressemble à un nombre. Entourez de VALUE pour calculer avec.
Les zéros initiaux disparaissent. Si une cellule est un nombre, 007 vaut déjà 7. Mettez d'abord la source au format texte brut.
Trop de remplacements. SUBSTITUTE remplace toutes les correspondances. Utilisez occurrence_number pour limiter.
Les caractères génériques ne fonctionnent pas. * et ? sont littéraux ici. Utilisez REGEXREPLACE pour les motifs.

SUBSTITUTE, REPLACE ou REGEXREPLACE ?

SUBSTITUTE : remplace un texte précis, où qu'il apparaisse.
REPLACE : remplace des caractères d'après leur position et leur longueur, quels qu'ils soient.
REGEXREPLACE : remplace le texte qui correspond à un motif, avec options insensibles à la casse et fondées sur des groupes.

Questions fréquentes

SUBSTITUTE tient-elle compte de la casse ? Oui. Pour un remplacement insensible à la casse, utilisez REGEXREPLACE avec (?i).

Accepte-t-elle les caractères génériques ? Non. Utilisez REGEXREPLACE.

Peut-on remplacer plusieurs textes différents à la fois ? Imbriquez les appels, ou utilisez REGEXREPLACE avec |, comme REGEXREPLACE(A2, "chat|chien", "animal").

Que fait-elle si le texte recherché est introuvable ? Elle renvoie le texte d'origine inchangé.

Fonctions associées

REGEXREPLACE : Remplace du texte par motif.
TRIM : Supprime les espaces en trop.
SPLIT : Divise un texte au niveau d'un séparateur.
LEN : Renvoie la longueur d'un texte.
VALUE : Convertit un texte en nombre.

SUBSTITUTE