Accueil › Fonctions Excel › SUBSTITUE
Fonction SUBSTITUE dans Excel : remplacer du texte avec une formule
SUBSTITUE remplace un texte précis d'une cellule par un autre texte. Supprimez les tirets des numéros de téléphone, changez des points en virgules ou corrigez une faute qui se répète, le tout sans toucher aux données d'origine. Dans l'Excel en anglais, la fonction s'appelle SUBSTITUTE.
SUBSTITUE · Dans ce guide
Syntaxe de SUBSTITUE
=SUBSTITUE(texte; ancien_texte; nouveau_texte; [no_position])| Argument | Signification |
|---|---|
| texte | Le texte ou la cellule à modifier. |
| ancien_texte | Le texte à chercher. SUBSTITUE distingue les majuscules des minuscules. |
| nouveau_texte | Le texte de remplacement. Utilisez "" pour supprimer l'ancien texte. |
| no_position (facultatif) | Quelle occurrence remplacer. Si vous l'omettez, toutes sont remplacées. |
Exemples
| Objectif | Formule | Résultat |
|---|---|---|
| Supprimer des tirets | =SUBSTITUE("06-12-34-56"; "-"; "") | 06123456 |
| Remplacer un mot | =SUBSTITUE("Rapport T1"; "T1"; "T2") | Rapport T2 |
| Seulement la 2e occurrence | =SUBSTITUE("a-b-c"; "-"; "/"; 2) | a-b/c |
| Supprimer tous les espaces | =SUBSTITUE(A2; " "; "") | Aucun espace |
S'exercer avec SUBSTITUE : essayez vous-même
Lire est une chose, taper la formule en est une autre. Construisez la formule ci-dessous pas à pas : les indications vous disent quoi taper, et vous pouvez cliquer sur les cellules ou faire glisser dessus pour les ajouter, comme dans Excel.
Exercice: Remplacez en B2 les tirets du numéro de téléphone par des espaces: =SUBSTITUE(A2;"-";" ")
Plusieurs remplacements d'un coup
Imbriquez SUBSTITUE dans elle-même, un niveau par remplacement. Pour supprimer les parenthèses et les tirets d'un numéro de téléphone :
=SUBSTITUE(SUBSTITUE(SUBSTITUE(A2; "("; ""); ")"; ""); "-"; "")« (01) 2345-6789 » devient « 01 23456789 ».
Compter combien de fois un caractère apparaît
Comparez la longueur avant et après la suppression du caractère :
=NBCAR(A2) - NBCAR(SUBSTITUE(A2; ","; ""))Pour « pomme,banane,cerise », elle renvoie 2 virgules : il y a donc 3 éléments. Plus d'informations sur NBCAR dans le guide de NBCAR.
Corriger des nombres avec le mauvais séparateur
Les nombres importés au format « 1,234.50 » (style américain) sont du texte dans un Excel réglé avec la virgule comme séparateur décimal. Changez les séparateurs et convertissez :
=CNUM(SUBSTITUE(SUBSTITUE(A2; ","; ""); "."; ","))renvoie le nombre 1234,5. Lisez aussi convertir du texte en nombre.
SUBSTITUE, REMPLACER ou Rechercher et remplacer
- SUBSTITUE remplace un texte d'après ce qu'il est : tous les « - » de la cellule.
- REMPLACER remplace un texte d'après sa position :
=REMPLACER(A2; 1; 3; "")supprime les trois premiers caractères. - Rechercher et remplacer (Ctrl+H) modifie les cellules elles-mêmes, sans formule. C'est plus rapide pour un nettoyage ponctuel.
Questions fréquentes
SUBSTITUE distingue-t-elle les majuscules des minuscules ?
Oui. SUBSTITUE(A2; "abc"; "x") ne remplace pas « ABC ». Entourez d'abord le texte de MINUSCULE si la casse ne doit pas compter.
SUBSTITUE accepte-t-elle les caractères génériques ?
Non. Elle ne remplace que du texte exact. Utilisez Rechercher et remplacer pour des remplacements avec caractères génériques.
Comment remplacer les retours à la ligne ?
Les retours à la ligne sont CAR(10) : =SUBSTITUE(A2; CAR(10); ", ") les transforme en virgules.