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 :
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
Il est également possible de lancer la fenêtre de remplacement en utilisant la combinaison de touches [Ctrl]+[h]
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 :
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 » :
Nous validons ensuite en appuyant sur le bouton Remplacer tout pour confirmer le remplacement.
Excel nous confirme alors avoir effectué tous les remplacements :
Et effectivement, les lettres « F » de la colonne Sexe ont bien été remplacées par le mot « Femme » :
Seulement, ce n’est pas tout, en effet, toutes les lettres « F » présentent sur la feuille ont également subi le même sort !
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 :
Cette fois-ci seules les données à modifier ont été impactées :
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 :
Cela permet alors valider ou non le remplacement
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) :
À 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) :
Cela étant fait, il ne reste plus qu’à remplacer les points par des virgules :
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 >> ») :
Puis en choisissant d’effectuer la recherche dans tout le Classeur depuis l’option « Dans : » :
Ici, nous allons remplacer toutes les références à la marque « Volkswagen » par « VW »
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 :
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 :
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 :
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) :
- Utiliser l’étoile (« * ») pour remplacer un ou plusieurs caractères inconnus (une étoile pour un nombre non défini de caractères) :
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 : » :
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 !
Nous ne pourrons ici pas nous contenter d’effectuer le remplacement sur les points d’interrogation :
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] :
Puis pour faire apparaître le symbole correspondant, il ne reste qu’à saisir le caractère à échapper (ici le point d’interrogation) :
Les adresses mails sont maintenant correctement insérées dans la feuille de calcul :
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 :
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.
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) :
