Comment remplacer automatiquement des données (mots, lettres, nombres,…) sur Excel

Dans ce tutoriel, je vais vous montrer comment automatiser le remplacement de données dans Excel. Nous verrons notamment comment remplacer des mots par d’autres mots, comment supprimer des caractères dans une feuille de calcul, etc…

 

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

La fonctionnalité de remplacement des données d’Excel est une extension de la fonction de recherche que nous avons déjà eu l’occasion d’explorer de fond en comble dans un précédent tutoriel.

Si ce n’est pas encore fait, je vous invite fortement à consulter ce tutoriel en suivant ce lien, ce qui vous servira en quelques sorte d’introduction au tutoriel que vous vous apprêtez à lire ici.

En effet, pour effectuer un remplacement dans une feuille de calcul, il convient tout d’abord d’être capable de trouver les données que nous souhaitons modifier.

Et étant donné que nous avions vu les méthodes avancées qui permettent de réaliser des recherches complexes, nous ne reviendrons que partiellement dessus dans le cadre de cet article.

Pour réaliser une opération de remplacement, rendez-vous dans le menu Accueil, puis dans le groupe Edition (tout à droite) déroulez le menu Rechercher et sélectionner puis choisissez l’option Remplacer

Excel formation - Remplacer des données - 01

Il est également possible de lancer la fenêtre de remplacement en utilisant la combinaison de touches [Ctrl]+[h]

Excel formation - Remplacer des données - 02

La fenêtre est composée de deux zones de textes :

  • Le champ Rechercher : il s’agit ici de saisir la chaîne de caractères (une lettre simple ou une série de mots) que nous souhaitons rechercher pour effectuer le remplacement
  • Le champ Remplacer par : il s’agit ici de saisir la chaîne de caractères par laquelle le texte recherché va être remplacé

 

2. Remplacer une chaîne de caractères

Commençons à découvrir cette super fonctionnalité par un exemple très simple.

Nous disposons ici par exemple d’un tableau qui reprend une liste du personnel, dans lequel nous retrouvons un certain nombre d’information (fictives) à propos de chaque personne :

Excel formation - Remplacer des données - 03

Pour illustrer le fonctionnement de la fonction de remplacement, nous allons commencer par nous intéresser au sexe des personnes.

Ici la base de données nous retourne un « F » lorsqu’il s’agit d’une femme et un « H » pour les hommes.

Pour plus de clarté, nous voudrions que le sexe de la personne soit écrit en toutes lettres.

Il suffit alors de lancer l’outil de remplacement, puis de demander à Excel de remplacer les lettres « F » par le mot « Femme » :

Excel formation - Remplacer des données - 04

Nous validons ensuite en appuyant sur le bouton Remplacer tout pour confirmer le remplacement.

Excel nous confirme alors avoir effectué tous les remplacements :

Excel formation - Remplacer des données - 05

Et effectivement, les lettres « F » de la colonne Sexe ont bien été remplacées par le mot « Femme » :

Excel formation - Remplacer des données - 06

Seulement, ce n’est pas tout, en effet, toutes les lettres « F » présentent sur la feuille ont également subi le même sort !

Excel formation - Remplacer des données - 07

En réalité, comme nous pouvons le constater ici, lorsqu’une seule cellule est active sur la feuille de calcul, alors tous les termes correspondants au champ « Rechercher » vont être concernés par le remplacement !

En revanche, dès lors qu’une plage d’au moins deux cellule est sélectionnée, alors seules les cellules contenues dans cette plage vont être affectées.

Cette notion est très importante, pour éviter de modifier des données par inadvertance.

Dans notre cas, il n’est pas trop tard, nous pouvons en effet annuler les remplacements en appuyant sur les touches [Ctrl]+[Z].

Puis pour limiter les transformations aux seules cellules de la colonne Sexe, nous sélectionnons celle-ci avant de relancer la commande :

Excel formation - Remplacer des données - 08

Cette fois-ci seules les données à modifier ont été impactées :

Excel formation - Remplacer des données - 09

Il est également possible d’effectuer les remplacements un par un.

Pour cela, nous appuyons sur le bouton Suivant pour rechercher le premier élément, puis lorsque la cellule est sélectionnée, nous pouvons choisir de réaliser la modification en appuyant sur le bouton Remplacer :

Excel formation - Remplacer des données - 10

Cela permet alors valider ou non le remplacement

Excel formation - Remplacer des données - 11

Pour ne pas effectuer le remplacement et passer directement sur la recherche suivante, nous pouvons appuyer une nouvelle fois sur le bouton Suivant.

 

3. Supprimer des caractères

Maintenant, attaquons à la colonne salaires annuel.

En cellule J4, nous souhaitons récupérer le montant total de l’ensemble des salaires distribués, or le résultat retourné est de 0€.

En effet, les montants correspondants, et qui ont été extrait d’un logiciel de paie sont saisis sous la forme anglo-saxonne :

  • Le séparateur de milliers utilisé est la virgule,
  • Et le séparateur de décimal est le point

Excel n’interprète donc pas les données figurant dans la colonne comme étant des données numériques.

Pour nous en rendre compte, il suffit de constater que ces valeurs sont alignées sur la gauche des cellules (alors que des données numériques seraient sur la droite).

Nous pouvons par exemple modifier la première entrée (en supprimant simplement la virgule et en remplaçant le point par une virgule) :

Excel formation - Remplacer des données - 12

À ce moment-là, effectivement Excel transforme le contenu de la cellule en une valeur numérique.

Celle-ci est maintenant alignée sur la droite de la cellule et le montant retourné en cellule J4 reprend le montant du salaire versé à Aceline.

Nous pourrions donc effectuer la même opération sur l’ensemble des valeurs de la colonne.

Seulement la base de données comprend plus de 1400 personnes, je vous laisse imaginer le temps que cela prendrait !

Heureusement, la fonction de remplacement que nous découvrons ici va pouvoir nous être d’un grand secours !

Pour commencer, nous allons supprimer les virgules, en laissant simplement la zone « Remplacer » par un vide :

(Attention de bien sélectionner la colonne J avant) :

Excel formation - Remplacer des données - 13

Cela étant fait, il ne reste plus qu’à remplacer les points par des virgules :

Excel formation - Remplacer des données - 14

Excel formation - Remplacer des données - 15

 

 

4. Les options de remplacements avancés

Bien entendu, presque toutes options de recherche avancée que nous avons découvert dans le tutoriel dédié vont également être accessibles depuis l’outil de recherche.

Pour éviter que ces deux tutoriels ne soient redondants, nous n’allons ici les aborder que très rapidement.

 

   4.1. Étendre la recherche à tout le classeur

 

Par défaut, lorsque nous allons demander à Excel de lancer un remplacement, celui-ci va effectuer l’opération sur l’ensemble de la feuille de calcul.

(Sauf bien sûr si nous avons au préalable demandé à Excel de restreindre le remplacement en sélectionnant des cellules)

Mais il est également possible d’étendre ce remplacement à toutes les feuilles du classeur !

Pour cela, il suffit de dérouler le menu d’options avancées (en cliquant sur le bouton « Options >> ») :

Excel formation - Remplacer des données - 16

Puis en choisissant d’effectuer la recherche dans tout le Classeur depuis l’option « Dans : » :

Excel formation - Remplacer des données - 17

Ici, nous allons remplacer toutes les références à la marque « Volkswagen » par « VW »

Excel formation - Remplacer des données - 18

Les remplacements ont bien été effectués sur l’ensemble du classeur et non pas uniquement sur la feuille active !

Note : contrairement à l’outil de recherche classique, les fonctions de recherches se limitent uniquement au contenu des formules :

Excel formation - Remplacer des données - 19

Il n’est donc pas possible de modifier des termes issus du résultat d’une formule, ni d’un commentaire.

Les champs sont tout simplement laissés blanc dans le menu déroulant correspondant.

 

   4.2. Respect de la casse

 

Il est possible de demander à Excel de respecter scrupuleusement la casse saisie dans le champ Recherche.

Dans ce cas-là, les majuscules et les minuscules seront distinguées :

Excel formation - Remplacer des données - 20

 

   4.3. Recherche sur la totalité du contenu de la cellule

 

De la même manière, nous pouvons demander à Excel de réaliser le remplacement que lorsque que tout le contenu de la cellule correspond à la valeur du champ Rechercher :

Excel formation - Remplacer des données - 21

 

   4.4. Utilisation des caractères joker

 

Bien entendu, il est tout à fait possible d’utiliser les caractères joker pour effectuer un remplacement !

Ainsi, nous pouvons par exemple :

  • Utiliser un ou des points d’interrogation (« ? ») pour remplacer un ou plusieurs caractères inconnus (dont nous connaissons le nombre, en effet un point d’interrogation permet le remplacement d’un seul caractère) :

Excel formation - Remplacer des données - 22

  • Utiliser l’étoile (« * ») pour remplacer un ou plusieurs caractères inconnus (une étoile pour un nombre non défini de caractères) :

Excel formation - Remplacer des données - 23

Attention, les caractères jokers sont à utiliser avec précaution.

En effet, dans le cas le plus extrême, si nous saisissons une simple étoile dans le champ « Rechercher », Excel va tout simplement remplacer toutes les cellules au sein desquelles ne se trouverait ne serait-ce qu’un seul caractère par la valeur du champ « Remplacer par : » :

Excel formation - Remplacer des données - 24

 

   4.5. Échapper un caractère joker

 

Bien entendu, il est également possible de souhaiter remplacer un caractère utilisé en tant que caractère joker (un point d’interrogation ou une étoile).

Pour illustrer cet exemple, revenons sur notre exemple.

Comme nous l’avons vu un peu plus tôt, cette base de données a été extraite d’une d’un logiciel de gestion du personnel, et celui-ci n’est pas capable de générer correctement certains signes, tel que l’arobase.

Lors de la génération de la base, il a donc remplacé tous les arobases « @ » par des points d’interrogations !

Excel formation - Remplacer des données - 25

Nous ne pourrons ici pas nous contenter d’effectuer le remplacement sur les points d’interrogation :

Excel formation - Remplacer des données - 26

Heureusement, il existe une petite astuce qui consiste à utiliser un tilde juste avant le point d’interrogation, afin qu’Excel recherche ce caractère en particulier, et non pas le caractère joker correspondant.

Le tilde est le symbole en forme de vague que nous retrouvons en principe sur la touche « 2 » du clavier et que nous insérons en appuyant en simultanée sur la touche [Alt Gr] :

Excel formation - Remplacer des données - 27

Puis pour faire apparaître le symbole correspondant, il ne reste qu’à saisir le caractère à échapper (ici le point d’interrogation) :

Excel formation - Remplacer des données - 28

Les adresses mails sont maintenant correctement insérées dans la feuille de calcul :

Excel formation - Remplacer des données - 29

 

   4.6. Remplacer en fonction du format

 

Et pour finir, nous pouvons encore demander à Excel de remplacer des éléments en fonction seulement de leur format !

Imaginons par exemple, que nous changions la couleur du fond de trois cellules qui correspondent à des personnes sans véhicule.

Nous pourrons alors remplacer les noms de ces véhicules :

Excel formation - Remplacer des données - 30

Pour cela, après avoir déroulé le menu des options avancées, nous cliquons simplement sur le bouton Format… afin de définir le format à rechercher.

Excel formation - Remplacer des données - 31

Cette fonctionnalité peut également permettre de modifier le format d’une cellule qui contiendrait un terme en particulier (ici toutes les cellules contenant le terme Volkswagen vont voir leur police de caractères passer en gras) :

Excel formation - Remplacer des données - 32

 

 

 



Articles qui pourraient vous intéresser

Comment remplacer automatiquement des données (mots, lettres, nombres,…) sur Excel

MISE EN FORME CONDITIONNELLE SUR UN GRAPHIQUE EXCEL

COMMENT DUPLIQUER 500 FOIS (OU PLUS !) UNE FEUILLE DE CALCUL EXCEL EN 1 CLIC

COMMENT CONVERTIR DES COORDONNÉES GPS DEGRÉS MINUTES SECONDES (DMS) EN DEGRÉS DÉCIMAUX (DD) EXCEL

Commentaires

Il n'y a aucun commentaire...

Poster un commentaire

Pseudo
Commentaire

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.