Comment modifier ou supprimer des caractères dans un texte avec la fonction SUBSTITUE d'Excel

Dans ce petit tutoriel, nous allons découvrir une formule qui va nous permettre de remplacer simplement un ou plusieurs caractères contenus dans un texte. Nous y découvrirons également une astuce qui va nous permettre cette fois-ci de supprimer des caractères indésirables.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Présentation

Comme vous l’aurez deviné à la vue du titre de ce tutoriel, pour effectuer le remplacement d’un caractère au sein d’un texte, Excel propose la fonction SUBSTITUE().

Cette fonction attend quatre arguments (dont le dernier est facultatif) :

• Texte : Il s’agit de la chaîne de caractères sur laquelle nous souhaitons effectuer la transformation,
• Ancien_texte : il s’agit du texte (constitué d’un ou plusieurs caractères) que nous souhaitons retrouver au sein de la chaîne principale afin de pouvoir le modifier,
• Nouveau_texte : il s’agit du texte (qui peut également être composé d’un ou plusieurs caractères, voir même être une chaîne vide, comme nous le verrons un peu plus tard durant ce tutoriel)
• No_position : il s’agit ici d’un argument facultatif (que nous pouvons omettre) et qui vient préciser quel occurrence d’Ancien_texte nous souhaitons modifier en partant du début du texte. Lorsque cet argument est omis, l’ensemble du texte va être analysée et toutes les occurrences seront modifiées.

Maintenant que nous avons fait une rapide présentation de la fonction SUBSTITUE(), voyons comment l’utiliser avec un exemple simple :

Il s’agit ici d’une liste des principales villes françaises, pour lesquelles nous allons retrouver le nombre d’habitants relevé au cours de l’année 2015, et la superficie occupée par la ville.

Nous allons alors vouloir retraiter rapidement cette liste afin de pouvoir en calculer chaque densité correspondante.

2. Modifier des caractères dans un texte

Dans un premier temps, nous allons utiliser la fonction SUBSTITUE() pour travailler les noms de villes.

En effet, comme nous pouvons nous en rendre compte, ces noms nécessites quelques légers retraitements.

Par exemple, nous allons souhaiter retirer les tirets pour les remplacer par des espaces.

Ainsi nous souhaiterons transformer la chaîne « [Ville_ :_Paris] » par « [Ville : Paris] ».

Pour cela, nous allons nous placer sur une cellule située dans une zone vide de la feuille de calcul, et appeler la fonction SUBSTITUE() :

Pour commencer, nous allons traiter la première cellule de la liste, nous pourrons ensuite simplement étendre la formule sur les autres cellules situées en dessous.

Nous sélectionnons donc la cellule A7 en tant que premier argument de la fonction SUBSTITUE().

 =SUBSTITUE(A7; 

Ensuite, étant donné que nous souhaitons remplacer tous les tirets se trouvant sur cette cellule, nous tapons simplement un tiret « _ » en tant que second argument.

Celui-ci étant saisi entre guillemets afin qu’Excel comprenne bien qu’il s’agit effectivement d’une chaîne de caractères.

 =SUBSTITUE(A7;"_"; 

Et pour finir, nous remplaçons tous les tirets par des espaces, que nous venons simplement saisir en tant que troisième argument, également saisi entre guillemets :

 =SUBSTITUE(A7;"_";" ") 

Une fois la formule validée, nous pouvons simplement l’étendre sur les cellules du dessous en utilisant la poignée de recopie (le petit carré noir située en bas à droite) :

À présent, comme nous pouvons le constater, tous les tirets ont bien été remplacés par des espaces :

Mais maintenant, imaginons, que nous n’ayons souhaité ne remplacer que le premier tiret de chaque cellule et laisser le second intact.

Pour cela, nous allons simplement utiliser l’argument facultatif No_position et lui donner pour valeur « 1 » :

 =SUBSTITUE(A7;"_";" ";1) 

Pour effectuer l’opération inverse (conserver le premier tiret et ne modifier que le second), nous allons cette fois-ci donner pour valeur à cet argument « 2 » :

 =SUBSTITUE(A7;"_";" ";2) 

Bien entendu, il est également possible de modifier directement plusieurs caractères.

Ici, nous allons remplacer les trois caractères « _ :_ » par le texte « de » (espace / « de » / espace) :

 =SUBSTITUE(A7;"_:_";" de ") 

3. Supprimer des caractères dans une chaîne

Maintenant que le nom de la ville est inséré dans une chaîne qui nous convient, nous allons vouloir faire disparaître les crochets en début et fin de chaîne, tout en conservant la modification déjà effectuée.

Nous cela, nous allons simplement encapsuler la fonction SUBSTITUE() avec les arguments tels que nous venons de les définir en tant qu’argument d’une nouvelle fonction SUBSTITUE() !

Et pour faire disparaître des caractères, il suffit simplement des les remplacer par une chaîne vide, c’est-à-dire des doubles guillemets « "" »

Faire disparaître le crochet d’ouverture :

 =SUBSTITUE(SUBSTITUE(A7;"_:_";" de  ");"[";"") 

Puis le crocher de fermeture :

  =SUBSTITUE(SUBSTITUE(SUBSTITUE(A7;"_:_";" de  ");"[";"");"]";"") 

Et voilà, les noms de villes correspondent maintenant au résultat attendu.

Nous pouvons remplacer les cellules d’origine en effectuant un copier-coller :

• Nous sélectionnons les cellules modifiées, puis nous les copions :

• Ensuite, nous sélectionnons les cellules de destinations, et cette fois-ci nous choisissons d’effectuer un collage spécial n’insérant que les valeurs des cellules préalablement copiées :

4. Effectuer un calcul avec des chaînes de caractères

Pour finir sur la découverte de la fonction SUBSTITUE(), nous allons vouloir déterminer la densité au km² de chacune de ces villes.

Or les cellules contenant les informations nécessaires à la réalisation de ce calcul ne sont pas de cellules numériques qui nous permettraient de pouvoir réaliser simplement ce calcul, mais il s’agit de chaînes de caractères.

En effet, en plus des données numériques qui vont nous intéresser, celles-ci contiennent des données textuelles (« habitants », « km² »).

Ce qui fait que si nous effectuons ici une simple division, nous allons forcément générer une erreur de calcul de type #VALEUR ! :

Nous allons devoir au préalable retraiter les valeurs de ces cellules, en utilisant la fonction SUBSTITUE() :

 =SUBSTITUE(B7;"  habitants";"")/SUBSTITUE(C7;"km²";"") 

Enfin, pour finir, nous allons à nouveau étendre la formule sur les cellules situées en dessous.

Seulement, comme vous allons le constater, à ce moment-là, Excel va y appliquer tous les paramètres de la cellule, y compris le formatage.

Pour limiter la recopie à la simple formule, déroulez simplement le menu contextuel qui se trouve à proximité de la dernière cellule générée afin de choisir de « Recopier les valeurs sans la mise en forme » :