Excel formation - Page de vente TCd - 11 Découvrez la méthode complète pour analyser les données efficacement et sans effort avec les tableaux croisés dynamiques, en cliquant ici

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 :

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 

 

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().

Excel formation - Modifier ou supprimer des caractères - 01

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 :

Excel formation - Modifier ou supprimer des caractères - 02

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() :

Excel formation - Modifier ou supprimer des caractères - 03

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;"_";" ") 

Excel formation - Modifier ou supprimer des caractères - 04

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) :

Excel formation - Modifier ou supprimer des caractères - 05

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

Excel formation - Modifier ou supprimer des caractères - 06

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) 

Excel formation - Modifier ou supprimer des caractères - 07

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) 

Excel formation - Modifier ou supprimer des caractères - 08

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 ") 

Excel formation - Modifier ou supprimer des caractères - 09

 

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  ");"[";"") 

Excel formation - Modifier ou supprimer des caractères - 10

Puis le crocher de fermeture :

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

Excel formation - Modifier ou supprimer des caractères - 11

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 :

Excel formation - Modifier ou supprimer des caractères - 12

  • 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 :

Excel formation - Modifier ou supprimer des caractères - 13

 

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 ! :

Excel formation - Modifier ou supprimer des caractères - 14

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

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

Excel formation - Modifier ou supprimer des caractères - 15

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 » :

Excel formation - Modifier ou supprimer des caractères - 16

 

 

 



Articles qui pourraient vous intéresser

Comment modifier ou supprimer des caractères dans un texte avec la fonction SUBSTITUE d'Excel
Déterminer la rentabilité d’un projet avec la fonction VAN() d’Excel
LA FONCTION CHOISIR() POUR ECRIRE UN JOUR EN LETTRES OU REMPLACER DES FONCTIONS SI IMBRIQUEES SUR EXCEL
SOMME.SI.ENS() : Réaliser une somme en fonction de plusieurs critères de filtres
La fonction SOMME.SI
Comment calculer une différence entre deux dates avec la formule DATEDIF (années, mois ou jours) sur EXCEL
Comment utiliser les formules DATE(), JOUR(), MOIS() et ANNEE() pour (dé)composer une date sur EXCEL
Comment vérifier que deux cellules sont identiques et forcer la saisie en MAJUSCULES sur Excel
Comment utiliser la formule SOMMEPROD d’Excel
Les formules logiques ET(), OU() et OUX() d’Excel pour combiner des tests
Comment arrondir un nombre sur Excel (mise-en-forme ou formule : la bonne méthode)
Comment utiliser la formule MOD() pour calculer le reste d'une division euclidienne sur Excel

Contact - Plan du site - À propos - Contact

© Excel-Formation 2018 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.