Préparez votre certification
Microsoft Office Specialist

Les fonctions Textes

Recevez ce cours au format PDF en envoyant un message ici

Les informations recueillies via un formulaire informatisé, sont souvent des données qui ne sont pas immédiatement exploitables vu qu'elles ne respectent pas toujours les formats convenables (des espaces intempestifs, informations non atomiques, etc.)

Le fichier de travail, téléchargeable ici contient un extrait de données collectées par l'intermédiaire d'un formulaire informatique rempli par des clients fictifs. Ces données qui se trouvent sur la feuille Clients nécessitent des opérations de traitement pour les rendre exploitables, des manipulations que l'on va faire au cours de ce chapitre.

Les différentes manipulations des chaines de caractères sont faites à l'aide de fonctions spécialisées dans le traitement des données textuelles. Ces fonctions textes sont groupées dans la catégorie Texte du groupe Bibliothèque des fonctions du ruban Formules.

Chaque fonction a une syntaxe spéciale que l'on peut écrire au clavier ou bien l'insérer en utilisant l'assistant.

Suppression des espaces superflus

La fonction SUPPRESPACE()

On commence par la colonne Nom Client dans laquelle on remarque une utilisation abusive des espaces entre les mots, chose qui n'est pas conforme aux règles typographiques. Pour corriger ces fautes de frappe, Excel possède la fonction SUPPRESPACE qui permet de purifier les chaines de caractères de tous ces espaces inappropriés.

La fonction SUPPRESPACE supprime tous les espaces d'une chaine de caractères sauf les espaces simples entre les mots. Elle requière un seul argument, c'est le texte à purifier qui peut être une chaine de caractères mise entre guillemets, ou bien une référence à une cellule contenant ce texte.

Pour mettre les fonctions qui seront abordées dans ce chapitre, je vous propose de recopier le tableau au-dessous et vous le videz sauf des en-têtes des colonnes. C'est dans ce deuxième tableau que l'on va saisir les fonctions en faisant référence au premier tableau.

Dans la cellule A15 on va donc saisir l'expression suivante : =SUPPRESPACE(A2)

On valide la saisie puis on recopie la fonction vers les cellules du bas. Résultat : tous les espaces de trop sont supprimés et par conséquent des chaines de caractères propres.

Au lieu de saisir la fonction à la main, on peut utiliser l'assistant comme le montre l'animation qui suit.

La fonction SUBSTITUE()

Dans la deuxième colonne Email, on trouve les emails saisis par les utilisateurs lors du remplissage du formulaire. Remarquez que ces écritures contiennent des espaces alors qu'une adresse email ne doit pas en contenir. La fonction SUPPRESPACE ne fera pas l'affaire ici puisqu'elle va laisser un espace entre les mots.

Pour supprimer tous les espaces dans une chaine de caractères, on va utiliser la fonction SUBSTITUE. Cette fonction permet de remplacer toutes les occurrences d'une sous-chaine par une autre dans un texte.

La syntaxe de la fonction SUBSTITUE est la suivante :

La fonction nécessite trois paramètres obligatoires et un facultatif.

• texte (obligatoire) : c'est le texte dans lequel on va effectuer la substitution
• ancien_texte (obligatoire) : c'est la sous-chaine qu'on cherche à remplacer
• nouveau_texte (obligatoire) : le texte de substitution, c'est lui qui va prendre la place de l'ancien texte
• no_position (facultatif) : paramètre qu'on peut omettre. Si cet argument est signalé, uniquement la chaine de caractères (ancien_texte) qui commence à cette position sera remplacée par la nouvelle chaine (nouveau_texte), sinon, toutes les occurrences de ancien_texte seront remplacées.

Adaptons la syntaxe de cette fonction à notre cas. Pour que ces adresses email soient valables, on doit supprimer tous les espaces qu'elles contiennent en les remplaçant par des chaines vides.

La première adresse email se trouve dans la cellule B2, les autres sont dans les cellules d'en bas.

• L'argument texte c'est la cellule B2
• L'argument ancien_texte c'est l'espace, " " un espace entre les guillemets
• L'argument nouveau_texte c'est une chaine vide, "" des guillemets avec rien à l'intérieur (même pas un espace)
• Le quatrième argument (facultatif), on ne va pas le mettre puisqu'on veut remplacer toutes les occurrences de espace " " par une chaine vide

On va donc saisir dans la cellule B15 l'expression telle qu'elle est mentionnée dans la capture suivante :

Modification de la casse

Modifier la casse c'est mettre un texte tout en majuscule, ou bien tout en minuscule ou bien encore uniquement la première lettre de chaque mot en majuscule.

Excel dans sa bibliothèque de fonctions, propose trois fonctions qui permettent ces différentes opérations.

• MAJUSCULE() : qui convertit une chaine de caractères en majuscule.
• MINUSCULE() : convertit une chaine de caractères en minuscule.
• NOMPROPRE() : met uniquement la première lettre de chaque mot en majuscule et tous les autres caractères en minuscule.

Chacune de ces fonctions, requière un seul paramètre de type texte qui peut être un texte entre guillemets ou bien une référence à une cellule qui contient du texte, ou encore le résultat d'une autre fonction qui renvoie du texte. Dans ce dernier cas on utilise des fonctions imbriquées c'est à dire l'une à l'intérieur de l'autre.

Extraction de sous-chaines

L'une des fonctionnalités les plus utilisées dans le cadre du traitement des informations recueillies de la part des personnes avec qui on communique, est l'extraction des informations atomiques à partir des données composées récoltées. Dans l'exemple que nous avons, l'information adresse doit être décomposée en plusieurs sous-informations : Pays, Ville et Code postal.

Il y a plusieurs fonctions qui permettent les différentes opérations d'extraction et qui ont des syntaxes relativement simples, mais lorsqu'il faut jongler avec ces fonctions pour aboutir à un résultat, parfois les expressions deviennent un peu compliquées.

Extraction des caractères les plus à droite

Commençons en douceur et essayons d'extraire le code postal. On remarque que tous les codes postaux se trouvent à la fin de la chaine de caractères (à droite), et qu'ils sont tous formés de cinq caractères, ce qui facilite la tâche.

On va utiliser la fonction DROITE qui renvoie les n caractères les plus à droite d'une chaine. Cette fonction va donc avoir besoin de deux paramètres, un premier de type texte, c'est la chaine de caractères sur laquelle elle va travailler, et un deuxième de type nombre entier qui indique le nombre de caractères qu'elle va extraire.

On va demander à Excel de faire cette extraction, par le biais de la fonction DROITE en utilisant la syntaxe suivante :

• Le premier paramètre texte est obligatoire, c'est le texte à partir duquel va se faire l'extraction
• Le deuxième paramètre nombre_de_caracteres indique le nombre de caractères à extraire à partir de la droite, il est facultatif ; s'il est omis, c'est le caractère le plus à droite qui sera envoyé.

Le texte d'extraction étant dans la cellule C15 et le nombre de caractères à extraire étant 5, on va donc écrire dans la cellule F15 l'expression suivante : =DROITE(C15;5), que l'on va recopier vers les cellules du bas.

Extraction des caractères les plus à gauche

Avec la fonction GAUCHE, on va essayer d'extraire le nom du pays qui se trouve au début de la chaine de caractères (à gauche). La syntaxe de la fonction GAUCHE est semblable à celle de la fonction DROITE, c'est donc le nom de la fonction qui change et le rendu.

• Le premier paramètre texte est obligatoire, c'est le texte à partir duquel va se faire l'extraction
• Le deuxième paramètre nombre_de_caracteres indique le nombre de caractères à extraire à partir de la gauche, il est facultatif ; s'il est omis, c'est le caractère le plus à gauche qui sera envoyé (un seul caractère).

Dans la cellule C15, le pays est Maroc, il est formé de 5 caractères. On va donc essayer d'extraire ce nom dans la cellule D15 en écrivant =GAUCHE(C15;5)

Après validation et recopie de la fonction vers les cellules d'en bas, on remarque que pour la cellule D15, le nom du pays est extrait correctement (Maroc) alors que pour la cellule D16 le nom extrait est incomplet (Franc, il manque le "e"). Chose tout à fait logique puisqu'on a demandé à Excel d'extraire les cinq premiers caractères à partir de la gauche au moment où les noms des pays n'ont pas tous la même longueur (5 caractères pour Maroc et 6 pour France).

Il va falloir trouver un repère pour indiquer à la fonction GAUCHE jusqu'à où extraire. Ce repère c'est le caractère espace qui vient juste après le nom de chaque pays ; pour cela on va utiliser le fonction CHERCHE qui retourne la position d'un caractère dans une chaine.

La syntaxe de la fonction contient trois paramètres dont deux sont obligatoires et le troisième facultatif.

• texte_cherché dans notre cas c'est l'espace qu'on va mettre entre guillemets
• texte c'est la chaine de caractères dans laquelle on va chercher, ici c'est le contenu de la cellule C15
• no_départ facultatif, indique la position à partir de laquelle va commencer la recherche, ce paramètre ne sera pas utilisé pour l'instant parce qu'on va commencer la recherche depuis le début de la chaine.

Résultat de cette opération après validation de l'expression et recopie vers les cellules d'en bas est montré par la capture suivante :

Au lieu d'utiliser une donnée fixe (5) pour le deuxième argument de la fonction GAUCHE, on va mettre une donnée variable qui est le résultat de la fonction CHERCHE, mais on va retrancher 1 pour reculer d'une position afin de na pas extraire l'espace qui vient juste après le nom du pays.

Le résultat de cette manipulation est l'extraction du nom du pays quel que soit son nombre de caractères.

Extraction des caractères au milieu du texte

L'extraction d'une chaine de caractères au milieu d'une autre chaine, se fait à l'aide de la fonction STXT abrégé de Sous Texte ou bien Sous Chaine. Elle permet de détacher un "morceau" de texte à partir d'une position et pour une certaine longueur.

Syntaxe : =STXT(texte;no_départ;nb_caractères)

Les trois paramètres entre parenthèses sont tous obligatoires

• texte : c'est le texte à partir duquel va se faire l'extraction
• no_départ : c'est la position à partir de laquelle va commencer l'extraction
• nb_caractères : c'est le nombre de caractères à extraire

Par exemple, pour extraire la ville (Nice) à partir de l'adresse qui se trouve dans la cellule C17 on doit écrire : =STXT(C17;8;4)

La chaine de caractères Nice commence à partir de la position 8 et compte 4 caractères.

Si cette expression est valide pour extraire Nice, elle ne l'est pas pour les autres villes, pour la simple raison que les noms de toutes les villes ne commencent pas tous à la même position et n'ont pas tous le même nombre de caractères.

Toutefois, une chose est à remarquer, c'est que le nom d'une ville est borné de deux espaces, un espace juste avant et l'autre juste après (les espaces sont indiqués en vert dans l'illustration ci-dessus). C'est une remarque très intéressante puisqu'on peut l'exploiter pour aboutir à un résultat.

En fait, si on connait la position du l'espace qui précède un mot, on peut en déduire la position du début de ce mot puisqu'il suffit d'ajouter 1 ; et si on connait la position du l'espace qui suit un mot, on peut en déduire la longueur du mot en faisant la différence entre la position de ce deuxième espace avec celle du premier caractère du mot.

Exemples :

• La longueur du mot Casablanca est 17 - 7 = 10
• La longueur du mot Nice est 12 - 8 = 4

La position d'un caractère, c'est déjà vu, on peut la connaitre grâce à la fonction CHERCHE, ce qui fait qu'on va jongler un peu avec les deux fonctions STXT et CHERCHE pour essayer d'extraire les noms des villes qui sont incorporés dans les adresses.

À la place du deuxième paramètre de la fonction STXT qui est une valeur fixe (8), on va utiliser une donnée variable qui est le rendu de la fonction CHERCHE qui va chercher la position du premier caractère espace à partir du début de la chaine (c'est pour cette raison qu'on n'a pas donné une valeur au troisième argument qui est facultatif) ; à cette valeur rendue par la fonction CHERCHE on ajoute 1 pour avancer d'une position et ainsi tomber sur le premier caractère du nom de la ville.

L'expression devient alors à ce stade, comme suit :

=STXT(C15;CHERCHE(" ";C15)+1;4)

Chose qui a donné des affichages tronqués pour les noms de villes qui ont plus de quatre caractères, comme le montre la capture ci-après :

Cet affichage inachevé pour certaines villes, est causé par le dernier argument de la fonction STXT qui est figé (4). Ce résultat est donc tout à fait normal puisque c'est nous qui avons demandé de n'afficher que 4 caractères.

Pour adapter ce paramètre à la longueur de chaque nom de ville, on doit le calculer à partir de la position des deux caractères espace qui bornent ces noms, cette longueur étant équivalente à la différence entre la position du deuxième espace et celle du premier.

La position du deuxième espace est trouvée grâce à la fonction CHERCHE avec le troisième paramètre qui l'oblige à commencer la recherche à partir du premier caractère du deuxième mot (le nom de la ville). Le +1 écrit en rouge permet d'avancer d'une position pour commencer la recherche à partir de la première lettre du nom de la ville.

La position du premier caractère espace est donnée par la syntaxe déjà vue CHERCHE(" ";C15)

La longueur de la sous-chaine (nom de la ville) est calculée en faisant la différence entre les deux positions :

CHERCHE(" ";C15;CHERCHE(" ";C15)+1) - CHERCHE(" ";C15)-1

Le - 1 qui se trouve à la fin de l'expression c'est pour reculer d'une position afin de ne pas prendre le dernier espace en compte.

L'expression complète pour extraire les noms des villes à partir de l'adresse est :

C'est une écriture qui a l'air un peu complexe, qu'il faut construire pas à pas. J'ai utilisé des retours à la ligne dans la formule pour des raisons de clarté

En récompense à tous ces efforts, une écriture qui s'adapte à toutes les adresses donnant un résultat impeccable montré par la capture ci-après :

Concaténation des chaines de caractères

Concaténer des chaines de caractères c'est les enchainer une après l'autre pour former une chaine plus longue. Pour ce faire, Excel dispose de plusieurs moyens qui permettent de concaténer des chaines de caractères mises entre guillemets, des textes contenus dans des cellules ou bien de chaines résultants de diverses fonctions textes.

Concaténer en utilisant l'opérateur &

La méthode la plus simple pour mettre bout à bout des chaines de caractères est l'utilisation de l'opérateur & nommé esperluette ou bien Et commercial.

La syntaxe de l'expression est toute simple puisqu'il suffit d'écrire dans la cellule qui va recevoir le texte concaténé, les différentes valeurs textuelles séparées par l'opérateur de concaténation &.

Exemple, pour assembler le mot Maroc et le mot Rabat, on écrit : ="Maroc"&"Rabat". Les valeurs textes à mettre ensemble peuvent être des chaines fixes, comme c'est le cas ici, on doit alors les mettre entre guillemets, ou bien des valeurs variables contenues dans des cellules, dans ce cas on va utiliser les références de ces cellules.

Les deux mots sont bien mis côte à côte mais avec une petite imperfection, ils sont collés ; on doit donc les séparer en introduisant un espace entre les deux mots.

La fonction CONCATENER

Le même résultat obtenu avec l'opérateur & on peut l'avoir comme rendu de la fonction CONCATENER. Cette fonction permet elle aussi d'assembler des morceaux de texte pour en former un seul. Ces arguments sont des données textes mises entre parenthèses ou bien contenues dans des cellules ou encore résultant d'autres fonctions textes, séparées par des points-virgules, qu'elle va mettre bout à bout.

Exemple :

Comme toutes les autres fonctions, on peut insérer la fonction CONCATENER en utilisant l'assistant.

Le point faible de cette fonction c'est qu'il faut énumérer une à une, toutes les références des cellules contenant les textes à juxtaposer, même si ces cellules sont adjacentes ; elle n'accepte pas qu'on lui demande de concaténer les contenus d'une plage de cellules.

La fonction CONCAT

La fonction CONCAT apparue avec certaines éditions de la version 2016 de Excel, est venue pour améliorer la fonctionnalité de la fonction CONCATENER puisqu'elle accepte les plages de cellules comme paramètres et ainsi concaténer leur contenu.

On peut donc écrire =CONCAT(D15:F15) au lieu de =CONCATENER(D15;E15;F15), ce qui est beaucoup plus pratique, surtout dans le cas de grandes plages.

Bien que cette nouvelle fonction est meilleure que CONCATENER, elle présente elle aussi un petit moins car elle ne permet pas l'intégration d'un caractère de séparation pour séparer les valeurs des différentes cellules.

La fonction JOINDRE.TEXTE

Nouveauté de la version Excel 2019, JOINDRE.TEXTE est une fonction de concaténation complète dans la mesure où elle permet de préciser un (ou des) caractère de séparation et demande même si elle doit prendre les cellules vides en considération ou non. Les éléments de syntaxe de la fonction sont décrits ci-dessous.

• Séparateur : un ou plusieurs caractères de séparation, si aucun séparateur on doit quand même mettre des guillemets vides
• Ignorer vides : prend la valeur VRAI ou FAUX. Si c'est VRAI, les cellules vides seront ignorées, si c'est FAUX, les cellules vides seront considérées et un séparateur indiquera leur place.
• Plage à concaténer : ce sont les cellules qui verront leurs valeurs assemblées et séparées par le séparateur

La capture suivante montre le résultat dans le cas d'une cellule vide et le deuxième argument avec la valeur FAUX

Le remplissage automatique

La fonctionnalité Remplissage instantané ou Remplissage automatique est intégrée au logiciel depuis sa version 2013. C'est une fonctionnalité fort intéressante qui permet de faire des extractions et des concaténations sans la moindre formule ou fonction.

Avec cette fonctionnalité, Excel montre son haut niveau d'intelligence puisqu'il suffit que l'utilisateur remplisse deux ou trois cellules pour que Excel détecte le motif de remplissage et propose immédiatement la suite de la liste, il suffit alors d'appuyer sur la touche Entrée pour la valider.

L'animation suivante donne une démonstration des différentes manières de faire un remplissage instantané :

Un clic droit sur l'animation puis cliquer sur Afficher l'image pour agrandir.

Recevez ce cours au format PDF en envoyant un message ici