Qu'est-ce que la fonction SUBSTITUE ?
SUBSTITUE (SUBSTITUTE en anglais) te sert quand un même texte revient sous plusieurs formes dans tes données et que tu veux les uniformiser sans reprendre chaque cellule à la main. Elle t'évite des heures de correction manuelle sur des imports mal formatés ou des saisies incohérentes.
Concrètement, c'est elle qui nettoie 500 numéros de téléphone en un seul coup, corrige les fautes d'orthographe dans toute une colonne, uniformise les noms de départements écrits de dix façons différentes, ou modifie chirurgicalement seulement la deuxième occurrence d'un mot dans un texte structuré.
Syntaxe
=SUBSTITUE(texte; ancien_texte; nouveau_texte; [no_occurrence])Clique sur un argument pour aller à son explication.
Comprendre chaque paramètre de la fonction SUBSTITUE
« Je remplace partout ou juste à un endroit précis ? » C'est tout l'enjeu du quatrième argument. Les trois premiers se lisent dans l'ordre texte, ancien_texte, nouveau_texte et sont obligatoires : sans eux, SUBSTITUE n'a rien à chercher ni rien à poser à la place. Le [no_occurrence] est le seul facultatif : laisse-le vide et Excel remplace chaque occurrence, écris 2 et il ne touche que la deuxième.
texte
: le texte dans lequel tu veux effectuer le remplacementÇa peut être une référence à une cellule comme A1, une formule qui renvoie du texte comme CONCATENER(A1;B1), ou même un texte direct entre guillemets comme "Bonjour Paris".
ancien_texte
: le texte que tu veux remplacerSUBSTITUE est sensible à la casse : "Paris" ne trouvera pas "paris" ou "PARIS". Si ancien_texte n'existe pas dans le texte source, SUBSTITUE renvoie simplement le texte original sans modification, sans erreur.
nouveau_texte
: le texte qui va remplacer l'ancienSi tu veux supprimer du texte au lieu de le remplacer, utilise une chaîne vide "". C'est très pratique pour nettoyer des caractères indésirables comme les espaces, tirets ou symboles spéciaux.
[no_occurrence]
: spécifie quelle occurrence remplacer : `1` remplace seulement la première occurrence, `2` seulement la deuxième, etc(facultatif)Si tu omets ce paramètre, SUBSTITUE remplace toutes les occurrences trouvées. Parfait quand tu veux modifier seulement une partie spécifique d'un texte répétitif.
Si la valeur que tu indiques dépasse le nombre réel d'occurrences dans le texte, SUBSTITUE renvoie le texte original sans modification (pas d'erreur).
Exemples pratiques pas à pas
Comment supprimer les espaces d'une colonne avec la fonction SUBSTITUE
Tu dois verser 500 contacts dans le CRM avant ce soir, et l'outil recrache tous les numéros de téléphone parce qu'ils contiennent des espaces. Le tableau liste chaque contact avec sa source, sa ville et son téléphone brut tel qu'il est ressorti de l'ancien fichier, et il te faut une colonne de numéros propres, sans le moindre espace. La fonction SUBSTITUE fait ce ménage sur toute la colonne en une seule saisie, sans rechercher-remplacer à la main.
- 1Dans une cellule, écris
=SUBSTITUE(. - 2En 1er argument : clique sur la cellule qui contient le texte à nettoyer,
D2. Elle affiche le téléphone brut06 12 34 56 78, et comme la formule pointe la cellule plutôt que le numéro lui-même, tu pourras la recopier sur toute la colonne sans rien réécrire. - 3En 2ᵉ argument : saisis le caractère à faire disparaître, un espace entre guillemets
" ". Un espace est un caractère comme un autre pour Excel, et les guillemets sont ce qui le désigne comme du texte à chercher. - 4En 3ᵉ argument : saisis ce qui vient à la place de l'espace, une chaîne vide
"". Remplacer par du vide revient à supprimer le caractère au lieu de l'échanger contre un autre, et c'est ce qui fait ressortir les dix chiffres collés. - 5Ferme la parenthèse et appuie sur Entrée.
=SUBSTITUE(D2;" ";"")D2 et retire chaque espace qu'elle croise, sans en oublier un seul (par défaut, le remplacement s'applique à toutes les occurrences). Il reste 0612345678, dix chiffres collés que ton CRM accepte à l'import.
Comment supprimer plusieurs caractères d'un coup avec la fonction SUBSTITUE
Ton fournisseur t'envoie son catalogue la veille de la commande, et ses références ne ressemblent à rien : des espaces, des tirets et des points posés au petit bonheur. Le tableau donne le produit et la référence brute reçue, et tu veux une référence nettoyée que tu puisses comparer à celle de ton ERP. La fonction SUBSTITUE ne vise qu'un caractère à la fois, alors tu en empiles trois pour tout balayer en une seule saisie.
- 1Dans une cellule, écris
=SUBSTITUE(. - 2En 1er argument : écris le texte à nettoyer sous la forme de deux fonctions SUBSTITUE déjà emboîtées,
SUBSTITUE(SUBSTITUE(B2;" ";"");"-";""). La plus profonde retire les espaces deB2et celle qui l'entoure retire les tirets, si bien queREF-2024.001 Aarrive ici sous la formeREF2024.001A. - 3En 2ᵉ argument : saisis le caractère qui reste à faire disparaître, le point
".". Les espaces et les tirets ont déjà sauté dans les deux appels emboîtés, et le point est le dernier caractère parasite que la ligne traîne encore. - 4En 3ᵉ argument : saisis ce qui vient à la place du point, une chaîne vide
"". Comme dans les deux nettoyages précédents, remplacer par du vide efface le caractère sans en poser d'autre. - 5Ferme la parenthèse et appuie sur Entrée.
=SUBSTITUE(SUBSTITUE(SUBSTITUE(B2;" ";"");"-";"");".";"")REF-2024.001 A ressort en REF2024001A, un code que ton ERP reconnaît enfin.
Comment remplacer une seule occurrence de texte avec la fonction SUBSTITUE
Ton logiciel comptable réclame les libellés de facture avec un slash entre l'année et le numéro, alors que ton export sort trois blocs séparés par des tirets, et le rapprochement doit être bouclé demain. Le tableau liste le client et le libellé tel qu'il est exporté, et tu dois obtenir le libellé attendu sans abîmer le premier tiret, celui qui suit le préfixe. La fonction SUBSTITUE accepte un quatrième argument qui lui dit quelle occurrence viser, et rien qu'elle.
- 1Dans une cellule, écris
=SUBSTITUE(. - 2En 1er argument : clique sur la cellule qui contient le libellé exporté,
B2. Elle afficheFACT-2024-0142, où deux tirets séparent le préfixe, l'année et le numéro. - 3En 2ᵉ argument : saisis le caractère à remplacer, le tiret
"-"entre guillemets. La fonction en trouve deux dans la cellule, et c'est le quatrième argument qui tranchera lequel des deux change. - 4En 3ᵉ argument : saisis le caractère qui prend sa place, le slash
"/". C'est le séparateur que ton logiciel comptable attend entre l'année et le numéro. - 5En 4ᵉ argument : saisis le rang de l'occurrence à traiter,
2. Sans ce nombre, la fonction remplacerait les deux tirets et le préfixeFACTse retrouverait lui aussi suivi d'un slash. - 6Ferme la parenthèse et appuie sur Entrée.
=SUBSTITUE(B2;"-";"/";2)FACT-2024-0142 devient FACT-2024/0142, et le tiret du préfixe reste en place alors qu'il aurait sauté lui aussi sans le quatrième argument.
Comment compter les occurrences d'un caractère avec les fonctions SUBSTITUE et NBCAR
Avant de pousser tes fiches produits en ligne, tu veux repérer celles qui ont été bâclées : la règle interne, c'est au moins trois mots-clés par fiche. Le tableau donne le produit et ses mots-clés tapés dans une seule cellule, séparés par des points-virgules, et tu veux le compte exact ligne par ligne. La fonction SUBSTITUE ne compte rien toute seule, mais elle sait faire disparaître les séparateurs, et c'est ce qui permet de les dénombrer.
- 1Dans une cellule, écris
=NBCAR(. - 2En 1er argument : clique sur la cellule qui contient la liste de mots-clés,
B2. La fonction NBCAR en renvoie la longueur totale, points-virgules compris, et c'est cette mesure qui sert de point de comparaison. - 3Ferme la parenthèse, complète par
-NBCAR(SUBSTITUE(B2;";";""))+1, puis appuie sur Entrée. Ce second NBCAR remesure la même cellule une fois que la fonction SUBSTITUE en a effacé les points-virgules, et le+1de la fin fait passer du compte des séparateurs à celui des mots-clés.
=NBCAR(B2)-NBCAR(SUBSTITUE(B2;";";""))+1bureau;ergonomie;antidérapant en contient deux, donc trois mots-clés une fois le +1 ajouté (une liste compte toujours un séparateur de moins que d'éléments).
Les erreurs fréquentes avec la fonction SUBSTITUE
« Pourquoi ma formule ressort exactement le même texte ? » Avec SUBSTITUE, le raté est souvent silencieux : aucun code d'erreur, juste ta chaîne d'origine recrachée intacte, parce qu'un ancien_texte mal casé ("paris" ne trouvera jamais "Paris") ou un no_occurrence qui vise une occurrence inexistante. Les deux autres pièges, eux, se voient : des remplacements en cascade quand tu imbriques mal l'ordre, et un fichier qui rame dès que tu empiles les SUBSTITUE sur des milliers de lignes.
Rien ne se passe : la casse ne correspond pas
SUBSTITUE est sensible à la casse. Si tu cherches "paris" mais que le texte contient "Paris", aucun remplacement n'est effectué. Le résultat sera identique au texte original, sans erreur.
Solution : Utilise MINUSCULE() ou MAJUSCULE() autour de ton texte source et de ton texte à chercher : =SUBSTITUE(MINUSCULE(A1);"paris";"lyon"). Attention, le résultat sera entièrement en minuscules.
La formule retourne le texte original sans modification
Si tu spécifies un no_occurrence plus grand que le nombre réel d'occurrences (par exemple la 5ème occurrence alors qu'il n'y en a que 3), SUBSTITUE ne génère pas d'erreur : elle renvoie simplement le texte original sans modification.
Solution : Compte d'abord les occurrences avec =(NBCAR(A1)-NBCAR(SUBSTITUE(A1;"texte";"")))/NBCAR("texte") pour vérifier combien il y en a avant d'indiquer un numéro d'occurrence.
Remplacements en cascade non désirés
Quand tu imbriques plusieurs SUBSTITUE, l'ordre compte énormément. Si tu remplaces d'abord "A" par "AB" puis "AB" par "ABC", tous les "A" deviendront "ABC", pas seulement les "AB" originaux.
Solution : Planifie l'ordre de tes SUBSTITUE. Remplace toujours les chaînes les plus longues ou les plus spécifiques en premier pour éviter les conflits.
Performance lente sur de gros volumes de données
Si tu appliques SUBSTITUE avec plusieurs imbrications sur des milliers de lignes, Excel peut ralentir considérablement, surtout si les formules sont liées à d'autres calculs complexes.
Solution : Une fois le nettoyage terminé, copie les résultats et colle-les en tant que valeurs (Ctrl+Alt+V puis V) pour supprimer les formules et améliorer les performances.
SUBSTITUE vs REMPLACER vs STXT vs EPURAGE
« Je connais le mot à changer, ou je connais sa position ? » C'est la question qui tranche entre SUBSTITUE et REMPLACER. Tu prends SUBSTITUE quand tu sais quel texte chercher mais pas où il se cache (corriger « Licance » partout, virer tous les espaces) ; tu prends REMPLACER quand tu connais les positions exactes, genre les caractères 3 à 7. Les deux autres ne jouent pas dans la même cour : STXT extrait un morceau sans rien remplacer, et EPURAGE se contente de balayer les caractères non imprimables.
| Critère | SUBSTITUE | REMPLACER | STXT | EPURAGE |
|---|---|---|---|---|
| Cherche du texte | ✅ Oui, partout | ❌ Position fixe | ❌ Extraction seulement | ❌ Non |
| Sensible à la casse | ✅ Oui | ➖ N/A | ➖ N/A | ➖ N/A |
| Remplace toutes les occurrences | ✅ Oui (ou juste une) | ❌ Une seule zone | ❌ Pas de remplacement | ✅ Tous les caractères spéciaux |
| Besoin de connaître la position | ❌ Non | ✅ Oui, obligatoire | ✅ Oui, obligatoire | ❌ Non |
| Cas d'usage principal | Nettoyage, corrections | Format fixe | Extraction de sous-chaîne | Supprimer les caractères non imprimables |
Astuces avancées avec SUBSTITUE
Supprime un caractère en remplaçant par du vide
Pour supprimer des caractères plutôt que les remplacer, utilise une chaîne vide "" comme nouveau_texte : =SUBSTITUE(A1;" ";"") supprime tous les espaces d'un coup. La même logique s'applique à n'importe quel caractère : tirets, parenthèses, symboles monétaires.
C'est la technique la plus rapide pour nettoyer des données importées sans passer par la colonne entière.
Compte les occurrences d'un caractère dans une cellule
Combine SUBSTITUE avec NBCAR pour compter combien de fois un caractère apparaît : =(NBCAR(A1)-NBCAR(SUBSTITUE(A1;",";""))) donne le nombre de virgules dans la cellule A1. La différence entre la longueur originale et la longueur sans les virgules = nombre de virgules.
Utile pour valider la structure d'un texte (nombre de séparateurs dans un CSV, nombre de mots-clés, etc.).
Normalise la casse avant de comparer
Quand tu ne contrôles pas la casse des données sources, combine MINUSCULE et SUBSTITUE : =SUBSTITUE(MINUSCULE(A1);"paris";"lyon") trouvera "Paris", "PARIS" et "paris".
Reste conscient que le résultat sera en minuscules ; si tu veux conserver la casse originale avec remplacement insensible à la casse, c'est un cas VBA.
Questions fréquentes
SUBSTITUE cherche un texte précis dans toute la chaîne et le remplace, peu importe où il se trouve. REMPLACER remplace des caractères à une position fixe que tu définis. Si tu veux remplacer « Paris » par « Lyon », utilise SUBSTITUE. Si tu veux remplacer les caractères 3 à 7, utilise REMPLACER.
Il te permet de remplacer seulement la 1ère, 2ème ou Nième occurrence d'un texte. Si no_occurrence vaut 2, seule la deuxième occurrence sera remplacée. Si tu l'omets, toutes les occurrences sont remplacées. Pratique pour modifier seulement une partie répétée dans un texte structuré.
Oui. Utilise une chaîne vide "" comme nouveau_texte. Par exemple, =SUBSTITUE(A1;" ";"") supprime tous les espaces. C'est la technique la plus rapide pour nettoyer des caractères indésirables comme les espaces, tirets ou symboles.
Pour aller plus loin
Continue sur ta lancée après la fonction SUBSTITUE : la leçon associée, un modèle prêt à l'emploi et le guide pour progresser.
Tout voir
