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

Guide « 108 astuces pour grands débutants sur Excel ! » Offert
Booster votre productivité en téléchargeant gratuitement votre ebook PDF des meilleures astuces pour les débutants et les pros d'Excel
Excel formation - 00 Astuces - 100
 

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

Percez les secrets de la fonction SOMME (SOMME.SI/SOMMEPROD) et des ces déclinaisons pour boostez votre productivité sur Excel ?
TOP3 des méthodes pour extraire des données par date dans Excel
Comment identifier la date la plus ancienne ou la plus récente en fonction de critères sur Excel ?
Comment calculer une moyenne sans tenir compte des zéros sur Excel ?
Comment fusionner des tableaux, les trier et supprimer les doubles avec une seule formule Excel ? (ASSEMB.V et ASSEMB.H)
Arrêtez d'imbriquer des fonctions SI() sur Excel ! Découvrez SI.MULTIPLE() et SI.CONDITIONS()
Comment obtenir efficacement le numéro de semaine d’une date donnée sur Excel ?
Comment identifier la cellule qui se répète le plus de fois sur Excel ? (Fonction MODE)
Comment améliorer votre gestion de données avec les fonctions base de données d’Excel ?
Créer une somme intelligente et dynamique sur Excel sans VBA
Quelle fonction permet de lister le nom des feuilles de calcul Excel sans VBA ?
Comment créer un décompte entre deux dates (jours, heures, minutes et secondes) sur Excel ?

Contact - Plan du site - À propos - Contact

© Excelformation.fr 2018-2024 - 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.