Chaînes de caractères (Partie 2)
- 01. Supprimer des espaces (Ctrl+H, SUPPRESPACE)
- 02. Chercher un caractère dans une chaîne (TROUVE, CHERCHE, Caractères génériques)
- 03. Remplacer un caractère par un autre (REMPLACER, SUBSTITUE, Ctrl+H)
- 04. Prendre une partie de la chaîne (GAUCHE, DROITE, STXT, NBCAR)
- 05. Concaténation (&, CONCATENER, TEXTE)
- PETIT PLUS – ACTIONS DE CONVERSION (CNUM, CTXT)
1. Supprimer des espaces
Dans les extractions de données, il reste souvent des espaces à la fin des caractères dans la cellule. Aussi, il est possible de vouloir supprimer des espaces intempestifs.
Voici deux solutions possible pour supprimer les espaces.
Solution 1:
La plus simple est de sélectionner les cellules à modifier puis de faire CTRL + H (Ruban: Accueil -> Edition -> Rechercher et sélectionner -> Remplacer). Il suffit de mettre un espace dans la première zone et laisser la seconde zone vide, puis cliquer sur Remplacer Tout.
Solution 2:
Il existe une fonction qui permet de supprimer les espaces:
SUPPRESPACE(cellule_source)
En anglais: TRIM
Attention, si la cellule ne contient que des chiffres et des espaces.
2. Chercher un caractère dans une chaîne
Il existe deux fonctions pour retrouver le n° de la place du caractère (ou chaîne) cherchée. Pour ces deux fonctions, si je cherche « r » dans « Jérémy », elles retourneront la valeur 3 (3ème caractère de la chaîne).
Ces fonctions fonctionnent pour des cellules de type Nombre ou Texte mais pas pour les dates.
Pour que cela fonctionne sur une date, il faudra utiliser la fonction TEXTE que nous verrons un peu plus loin.
CHERCHE(texte cherché; texte; n° de départ)
En anglais: SEARCH
- Texte cherché est la chaîne de caractères que l’on veut trouver.
- Texte est le texte ou la référence à une cellule contenant le texte cherché le nombre de caractères à extraire.
- N° de départ indique la position du caractère à partir duquel la recherche doit débuter (facultatif).
TROUVE(texte cherché; texte; n° de départ)
En anglais: FIND
- Texte cherché est la chaîne de caractères que l’on veut trouver.
- Texte est le texte ou la référence à une cellule contenant le texte cherché le nombre de caractères à extraire.
- N° de départ indique la position du caractère à partir duquel la recherche doit débuter (facultatif).
Différences entre les deux fonctions:
CHERCHE() n’est pas sensible à la casse alors que TROUVE() y est sensible.
Contrairement à CHERCHE(), TROUVE() ne permet pas d’utiliser de caractères génériques ( ? ou * ).
Exemples:
| Chaîne: » | Jérémy LAPLAINE -*- www.modelisation-excel.com » | |
| =TROUVE(« a »;Chaîne) | 32 | |
| =TROUVE(« A »;Chaîne) | 9 | |
| =CHERCHE(« a »;Chaîne) | 9 | |
| =CHERCHE(« A »;Chaîne) | 9 | |
| =CHERCHE(« ?AI? »;Chaîne) | 11 | |
| =TROUVE(« ?AI? »;Chaîne) | #VALEUR! | |
| =CHERCHE(« ~* »;Chaîne) | 18 | |
| =TROUVE(« * »;Chaîne) | 18 | |
Caractères génériques:
|
UTILISEZ
|
POUR RECHERCHER
|
|
? (point d’interrogation)
|
un seul caractère
Par exemple, p?rt trouve « port » et « part ».
|
|
* (astérisque)
|
un nombre quelconque de caractères
Par exemple, *Est trouve « Nord-Est » et « Sud-Est ».
|
|
~ (tilde) suivi de ?, *, ou ~
|
un point d’interrogation, un astérisque ou un tilde
Par exemple, fy91~? trouve « fy91? ».
|
Extrait du site office
3. Remplacer un caractère par un autre
Je vous propose 3 solutions possibles, 2 par fonction et une à faire manuellement.
Fonction 1: REMPLACER
En anglais: REPLACE
La fonction REMPLACER() permet l’échange d’une chaîne de caractères par une autre, sur base d’un nombre de caractères spécifiés.
REMPLACER(ancien_texte;n°départ;nb_car;nouveau_texte)
- ancien_texte est le texte dont vous voulez remplacer un nombre donné de caractères.
- n°départ représente la place du premier caractère de la chaîne de l’ancien_texte où le remplacement doit commencer.
- nb_car représente le nombre de caractères d’ancien_texte que nouveau_texte doit remplacer.
- nouveau_texte est le texte qui doit remplacer les caractères de l’ancien_texte.
Fonction 2: SUBSTITUE
En anglais: SUBSTITUTE
La fonction SUBSTITUE() permet de remplacer un texte par un autre dans une chaîne de texte.
SUBSTITUE(texte; ancien texte; nouveau texte; position)
- texte représente le texte ou la référence à une cellule cellule dont on veut remplacer certains caractères,
- ancien texte est le texte à remplacer,
- nouveau texte est le texte qui doit remplacer l’ancien,
- position est un argument facultatif, il indique quelle occurrence de l’ancien texte on souhaite remplacer par le nouveau texte. Si cet argument n’est pas indiqué, toutes les occurrences de l’ancien texte sont remplacées par le nouveau texte, sinon seule l’occurrence indiquée est remplacée.
Remarques
- Les arguments de la fonction peuvent être des références à des cellules, des formules, ou du texte saisi. En cas de saisie, chiffres ou textes, les arguments doivent être notés entre guillemets.
- La fonction SUBSTITUE() est sensible à la casse
- SUSTITUE() renvoie des résultats au format texte, (les suites de chiffres sont alignées à gauche); pour obtenir un format numérique vous pouvez encadrer la fonction SUBSTITUE() par la fonction CNUM()
Différences entre les deux fonctions:
- La fonction REMPLACER() n’effectue qu’un seul remplacement, la chaîne à remplacer est identifiée par sa position et sa longueur. SUBSTITUE(), quant à elle, remplace une ou plusieurs occurrences d’un texte spécifique.
- La fonction SUBSTITUE() est différente de la fonction REMPLACER() qui remplace n’importe quel texte dont on indique simplement la position et la longueur, tandis que SUBSTITUE() remplace un texte spécifique à l’intérieur d’une chaîne de texte.
- Selon le paramétrage du dernier argument, SUBSTITUE() est capable de remplacer soit une occurrence spécifique soit toutes les occurrences d’un texte, tandis que la fonction REMPLACER() ne remplace qu’une seule occurrence.
CTRL+H (REMPLACER)
La dernière solution consiste à sélectionner la plage de cellules concernées (comme pour supprimer les espaces) et de remplacer par le raccourci CTRL+H.
4. Prendre une partie de la chaîne
Pour prendre des parties de chaînes, nous allons utiliser 3 fonctions principales auxquelles nous pouvons combiner d’autres fonctions comme NBCAR (Len):
- GAUCHE (LEFT)
- DROITE (RIGHT)
- STXT (MID)
GAUCHE
=GAUCHE(Chaîne, n)
Cette fonction permet de récupérer les n caractères de gauche de Chaîne.
DROITE
=DROITE(Chaîne, n)
Cette fonction permet de récupérer les n caractères de droite de Chaîne.
STXT
=STXT(Chaîne, no_départ, no_car)
STXT renvoie un nombre donné de caractères extraits d’une chaîne de texte à partir de la position que vous avez spécifiée, en fonction du nombre de caractères spécifiés.
- Chaîne: Obligatoire. Représente la chaîne de texte contenant les caractères à extraire.
- no_départ : Obligatoire. Représente la position dans le texte du premier caractère à extraire. Le premier caractère de texte a un no_départ égal à 1, et ainsi de suite.
- no_car : Obligatoire. Indique le nombre de caractères à extraire du texte à l’aide de STXT.
ASTUCE
Si je souhaite enlever les 3 derniers caractères d’une cellule, il est possible de combiner deux fonctions: GAUCHE (LEFT) et NBCAR (LEN):
=GAUCHE(Chaîne;NBCAR(Chaîne)-3)
NBCAR permet de compter le nombre de caractères de la cellule donc cette formule permet de prendre le nombre de caractères de la cellule -3, sur la gauche.
Le principe est le même avec DROITE.
5. Concaténation
Pour concaténer des chaînes, il existe tout d’abord la fonction CONCATENER (CONCATENATE) et l’utilisation du caractère esperluette (&). Aussi, l’autre fonction indispensable est TEXTE (TEXT). Nous verrons des exemple avec et sans cette fonction et vous verrez qu’elle est indispensable.
La fonction CONCATENER relie jusqu’à 255 chaînes de texte en une seule chaîne. Les éléments joints peuvent être du texte, des nombres, des références de cellules, ou une combinaison de ces éléments.
Vous pouvez également utiliser l’esperluette (&) comme opérateur de calcul à la place de la fonction CONCATENER pour relier les éléments de texte. Par exemple, =A1 & B1 renvoie la même valeur que =CONCATENATE(A1, B1).
Il est aussi très important d’associer la fonction TEXTE à la concaténation.
Voici un exemple d’utilisation qui permet de bien comprendre l’intérêt de cette fonction:
Pour compléter, vous pouvez approfondir en cliquant sur le lien suivant qui recense l’ensemble des formats qu’on peut utiliser pour la fonction TEXTE: Les formats pour la fonction TEXTE
PETIT PLUS – ACTIONS DE CONVERSION
CNUM
En anglais: VALUE
Convertit en nombre une chaîne de caractères représentant un nombre.
CNUM(texte)
texte représente le texte placé entre guillemets ou une référence à une cellule contenant le texte que vous voulez convertir.
- L’argument texte peut avoir l’un des formats constants (numérique, de date ou d’heure) reconnus par Microsoft Excel. Si l’argument texte ne correspond à aucun de ces formats, CNUM renvoie la valeur d’erreur #VALEUR!.
- Il n’est généralement pas nécessaire d’utiliser la fonction CNUM dans une formule, car Microsoft Excel convertit automatiquement le texte en nombres si nécessaire. Cette fonction permet d’assurer la compatibilité avec d’autres tableurs.
CTXT
En anglais: FIXED
Arrondit un nombre au nombre de décimales spécifié, lui applique le format décimal, à l’aide d’une virgule et d’espaces, et renvoie le résultat sous forme de texte.
CTXT(nombre;décimales;no_séparateur)
nombre représente le nombre que vous voulez arrondir et convertir en texte.
décimales représente le nombre de chiffres après la virgule.
no_séparateur représente une valeur logique qui, lorsqu’elle est VRAI, permet d’éviter que des espaces soient insérés dans le texte renvoyé par CTXT.
- Dans Microsoft Excel, les nombres ne peuvent jamais comporter plus de 15 chiffres significatifs, mais décimales peut en compter jusqu’à 127.
- Si décimales est négatif, nombre est arrondi à gauche de la virgule.
- Si décimales est omis, le nombre de décimales par défaut est 2.
- Si no_séparateur est FAUX ou omis, le texte renvoyé comprend des espaces.
- Il existe une différence fondamentale entre attribuer un format à une cellule contenant un nombre à l’aide de la commande Cellule du menu Format et le faire directement à l’aide de la fonction CTXT. En effet, le résultat renvoyé par la fonction CTXT est converti en texte, alors qu’un nombre mis en forme au moyen de la commandeCellule reste un nombre.