Les fonctions de texte dans Excel
Excel dans ses dernières versions compte plus de 500 fonctions Excel. Chacune d’entre elles a sa propre utilité et sa propre syntaxe. Pas évident alors de savoir quelle fonction utiliser dans quel cas.
Toutes ces fonctions sont séparées en différentes catégories en fonction de ce qu’elles permettent de manipuler (textes, dates, valeurs booléennes, etc.) :
- Effectuer des calculs
- Manipuler les textes
- Manipuler les dates
- Vérifier des conditions et utiliser les valeurs VRAI et FAUX
- Rechercher des valeurs en fonctions de différents critères
- Etc.
Dans cet article, nous nous focaliserons sur les différentes fonctions spécialisées dans les manipulations des textes.
Les différents types de fonctions de texte
Pour comprendre l’utilité de ces différentes fonctions, nous pensons que le mieux est de les séparer en 6 types différents :
- Celles qui permettent de standardiser un texte
- Celles qui permettent d’extraire (ou de fractionner) du texte
- Celles qui permettent de joindre des textes
- Celles qui permettent de modifier du texte
- Celles qui permettent de rechercher dans du texte
- Les autres
Bien entendu, même si toutes ces fonctions ont leur utilité propre, elles sont encore plus puissantes combinées entre elles ou à d’autres fonctions.
Les 5 fonctions permettant de standardiser un texte
Bien qu’elles soient très simples (un seul argument, le texte à standardiser), ces 5 fonctions sont très utiles pour éviter les erreurs ou les quiproquos dans Excel. En effet, Excel est « sensible à la casse » et sensible aux espaces et aux caractères spéciaux.
« Client A » n’est pas égal à « CLIENT A » ni à « client A » ou à « Client a ». Il est donc important, avant d’utiliser une fonction de recherche ou une fonction logique de bien standardiser les arguments pour qu’ils correspondent exactement.
Les fonctions MAJUSCULE, MINUSCULE et NOMPROPRE ont pour objectif de standardiser la « casse » (l’alternance de majuscules et de minuscules).
La fonction MAJUSCULE
Utilité : Mettre un texte entièrement en majuscules
Syntaxe : =MAJUSCULE( texte )
Formule : =MAJUSCULE( "Client A" ) >>> Résultat : "CLIENT A"
La fonction MINUSCULE
Utilité : Mettre un texte entièrement en minuscules
Syntaxe : =MINUSCULE( texte )
Formule : =MINUSCULE( "Client 347" ) >>> Résultat : "client 347"
La fonction NOMPROPRE
Utilité : Mettre la première lettre de chaque mot d’un texte en majuscule
Syntaxe : =NOMPROPRE( texte )
Formule : =NOMPROPRE( "ALLAN DELPIERO" ) >>> Résultat : "Allan Delpiero"
Les fonctions SUPPRESPACE et EPURAGE permettent aussi de standardiser un texte, sans prendre en compte les majuscules et les minuscules.
La fonction SUPPRESPACE
Utilité : Supprimer les espaces superflus d’un texte
Syntaxe : =SUPPRESPACE( texte )
Formule : =SUPPRESPACE( " Client 347 " ) >>> Résultat : "Client 347"
La fonction EPURAGE
Utilité : Supprimer les caractères spéciaux superflus d’un texte
Syntaxe : =EPURAGE( texte )
Formule : =EPURAGE( "Client 347
Restaurant" ) >>> Résultat : "Client 347Restaurant"
Les 6 fonctions permettant d’extraire (ou de fractionner) du texte
Les fonctions GAUCHE, DROITE et STXT permettent de récupérer uniquement le début, la fin ou le milieu d’un texte en fonction de sa position. Par exemple la fonction GAUCHE permet de récupérer les X premiers caractères à gauche d’un texte.
La fonction GAUCHE
Utilité : Récupérer les X caractères à gauche (au début) d’un texte
Syntaxe : =GAUCHE( texte ; [no_car] )
Formule : =GAUCHE( "75009" ; 2 ) >>> Résultat : "75"
La fonction DROITE
Utilité : Récupérer les X caractères à droite (à la fin) d’un texte
Syntaxe : =DROITE( texte ; [no_car] )
Formule : =DROITE( "Client 00347" ; 5 ) >>> Résultat : "00347"
La fonction STXT
Utilité : Récupérer les X caractères à partir du Ye caractère d’un texte
Syntaxe : =STXT( texte ; no_départ ; no_car )
Formule : =STXT( "1 85 05 78 006 084 36" ; 3 ; 2 ) >>> Résultat : "85" (car les chiffres 8 et 5 sont les 2 caractères à partir du 3e caractère de ce numéro de sécurité social)
Formule : =STXT( "1 85 05 78 006 08436" ; 5 ; 2 ) >>> Résultat : "05" (car les chiffres 0 et 5 sont les 2 caractères à partir du 3ecaractère de ce numéro de sécurité social)
Les fonctions TEXTE.AVANT, TEXTE.APRES et FRACTIONNER.TEXTE sont apparues dans Excel avec la version 365 de Microsoft. Contrairement aux fonctions d’avant qui nécessitent de spécifier un nombre X de caractères à récupérer, elles permettent de récupérer tous les caractères figurant avant ou après un délimiteur.
La fonction TEXTE.AVANT
Utilité : Récupérer tous les caractères avant un délimiteur dans un texte
Syntaxe simplifiée : =TEXTE.AVANT( texte ; délimiteur )
Syntaxe complète* : =TEXTE.AVANT( texte ; délimiteur ; [instance_num] ; [match_mode] ; [match_end] ; if_not_found] )
*Seuls les 2 premiers arguments sont obligatoires. Les arguments entre crochets sont facultatifs et permettent d’activer/préciser certaines options de la fonction.
Formule : =TEXTE.AVANT( "Othman BENAMI" ; " " ) >>> Résultat : "Othman" ( car on a extraie tout le texte situé avant l'espace ( " " ) à savoir le prénom )
La fonction TEXTE.APRES
Utilité : Récupérer tous les caractères après un délimiteur dans un texte
Syntaxe simplifiée : =TEXTE.APRES( texte ; délimiteur )
Syntaxe complète* : =TEXTE.APRES( texte ; délimiteur ; [instance_num] ; [match_mode] ; [match_end] ; if_not_found] )
*Seuls les 2 premiers arguments sont obligatoires. Les arguments entre crochets sont facultatifs et permettent d’activer/préciser certaines options de la fonction.
Formule : =TEXTE.APRES( "Othman BENAMI" ; " " ) >>> Résultat : "BENAMI" ( car on a extraie tout le texte situé après l'espace ( " " ) à savoir le nom de famille )
La fonction FRACTIONNER.TEXTE
Utilité : Séparer un texte en plusieurs textes, en fonction d’un délimiteur.
Syntaxe simplifiée : =FRACTIONNER.TEXTE( texte ; col_délimiteur )
Syntaxe complète* : =FRACTIONNER.TEXTE( texte ; col_delimiter ; [row_delimiter] ; [ignore_empty] ; [match_mode] ; [pad_with] )
*Seuls les 2 premiers arguments sont obligatoires (ou le 1eret le 3e). Les arguments entre crochets sont facultatifs et permettent d’activer/préciser certaines options de la fonction.
Formule : =FRACTIONNER.TEXTE( "M;Othman;BENAMI" ; ";" ) >>>
Résultat colonne 1 : "M"
Résultat colonne 2 : "Othman"
Résultat colonne 3 : "BENAMI"
Car 3 colonnes se seront créées avec les données à chaque fois qu'un ";" sera détecté. Et chaque cellule des colonnes contiendra la donnée correspondante située après chaque ";".
Les 4 fonctions permettant de joindre des textes
Les 6 fonctions précédentes permettaient d’extraire et de fractionner du texte. Les 4 suivantes font l’inverse : elles combinent différents textes entre eux afin de créer un autre texte, plus complexe.
.png)
La fonction JOINDRE.TEXTE
Utilité : Combiner plusieurs textes entre eux en spécifiant un délimiteur
Syntaxe : =JOINDRE.TEXTE(délimiteur ; ignorer_vide ; texte1 ; [texte2] ; … )
Formule : =JOINDRE.TEXTE( " " ; FAUX ; "Allan" ; "Delpierro" ) >>> Résultat : "Allan Delpiero"
La fonction CONCAT
Utilité : Combiner plusieurs textes entre eux (cette fonction est une version améliorée de la fonction CONCATENER)
Syntaxe : =CONCAT( texte1 ; [texte2] ; … )
Formule : =CONCAT( "Allan" ; " " ; "Delpiero" ) >>> Résultat : "AllanDelpiero"
La fonction CONCATENER
Utilité : Combiner plusieurs textes entre eux (cette fonction est aujourd’hui obsolète, elle doit être remplacée par la fonction CONCAT ou par la fonction JOINDRE.TEXTE)
Syntaxe : =CONCATENER( texte1 ; [texte2] ; … )
Formule : =CONCATENER( "Allan" ; " " ; "Delpiero" ) >>> Résultat : "Allan Delpiero"
CONCAT et CONCATENER, quelles différences ?
La fonction CONCAT est une version améliorée de la fonction CONCATENER. Ces 2 fonctions fonctionnent de la même manière mais la fonction CONCAT accepte une plage de cellules comme arguments, ce qui dans certains cas la rend largement plus efficace que la fonction CONCATENER.
La fonction TABLEAU.EN.TEXTE
Utilité : Transformer un tableau en un seul texte en spécifiant un format
Syntaxe : =TABLEAU.EN.TEXTE( matrice ; [format] )
Formule : nous n'avons à ce jour, pas trouvé de réelle utilité connue à cette fonction et pas de quoi en ressortir un exemple pertinent.
Bonus - Le symbole &
Utilité : Combiner plusieurs textes entre eux
Syntaxe : = "texte1" & "texte2"
Formule : = "Allan" & " " & "Delpiero" >>> Résultat : "Allan Delpiero"
JOINDRE.TEXTE, CONCAT, CONCATENER et &, comment choisir ?
Faisons simple : JOINDRE.TEXTE > CONCAT > CONCATENER
Et le symbole & ? Parfois, c’est plus rapide de l’utiliser. Parfois non. A vous de voir !
Les 7 fonctions permettant de modifier du texte
On distingue plusieurs types de fonctions qui modifient du texte. Celles qui remplacent et/ou suppriment du texte, et celles qui transforment le texte.
Les fonctions SUBSTITUE et REMPLACER vont remplacer/supprimer des caractères d'un texte.
.png)
La fonction SUBSTITUE
Utilité : Remplacer les occurrences d'un texte par un autre texte.
Syntaxe : =SUBSTITUE( texte ; ancien_texte ; nouveau_texte ; [ no_position ] )
Formule : =SUBSTITUE( "06-70-22-00-99" ; "-" ; "" ) >>> Résultat : "0670220099" : j'ai remplacé les tirets ("-") par "rien" (symbolisé par deux guillemets qui se suivent. Donc j'ai supprimé les tirets.
La fonction REMPLACER
Utilité : Remplacer une partie d'un texte par un autre (selon sa position).
Syntaxe : =REMPLACER( ancien_texte ; no_départ ; no_car ; nouveau-texte )
Formule : =REMPLACER( "+33670220099" ; 1 ; 3 ; "0") >>> Résultat : "0670220099" : j'ai remplacé les 3 caractères en partant de la position 1 (donc les 3 premiers caractères : "+33") par le texte "0".
La fonction TEXTE permet elle, non pas de remplacer du texte mais de transformer un nombre en texte en lui choisissant un format d'affichage. La fonction CNUM va, elle, transformer du texte en nombre (dans la mesure du possible).
La fonction TEXTE
Utilité : Transformer un nombre en texte dans un format choisi.
Syntaxe : =TEXTE( valeur ; format )
Formule : =TEXTE( 66 ; "##€" ) >>> Résultat : "66 €" : j'ai transformer le nombre 66 en texte et lui ai choisi un format avec le sigle €.
Formule : =TEXTE( "01/01/2024" ; "mmmm" ) >>> Résultat : "janvier" : on peut aussi utiliser les codes de format des dates pour transformer une date en texte et lui extraire des informations précises. Dans les codes de format, quatre "m" à la suite va extraire le mois en toute lettre de la date indiquée.
Voici un exemple des codes formats les plus connus/utilisés.
La fonction CNUM
Utilité : Transformer un texte en nombre quand cela est possible.
Syntaxe : =CNUM( texte )
Formule : =CNUM( "1000 €" ) >>> Résultat : "1000" : "1000 €" a été saisi à la main et est donc considéré comme du texte. CNUM va transformer ce texte en nombre avec le format choisi dans les formats de nombre (ici format Standard).
La fonction CTXT
Utilité : Arrondir un nombre au nombre de décimales spécifié et renvoyer le résultat sous forme de texte.
Syntaxe : =CTXT( nombre ; [ décimales ] ; [ no_séparateur] )
Formule : nous n'avons à ce jour, pas trouvé de réelle utilité connue à cette fonction et pas de quoi en ressortir un exemple pertinent.
La fonction VALEURNOMBRE
Utilité : Convertir un nombre de fonction de paramètres régionaux.
Syntaxe : =VALEURNOMBRE( texte ; [ séparateur_décimal ] ; [ séparateur_groupe ] )
Formule : nous n'avons à ce jour, pas trouvé de réelle utilité connue à cette fonction et pas de quoi en ressortir un exemple pertinent.
La fonction VALEUR.EN.TEXTE
Utilité : Renvoyer le texte d’une valeur spécifiée. Transférer les valeurs de texte inchangées et convertir les valeurs non textuelles en texte.
Syntaxe : =VALEUR.EN.TEXTE( valeur ; [ format ] )
Formule : nous n'avons à ce jour, pas trouvé de réelle utilité connue à cette fonction et pas de quoi en ressortir un exemple pertinent.
Les 2 fonctions permettant de rechercher dans du texte
Les fonctions CHERCHE et TROUVE vont permettre de rechercher des caractères dans du texte.
.png)
La fonction CHERCHE
Utilité : Rechercher le numéro de position d'un caractère dans du texte.
Syntaxe : =CHERCHE( texte_cherché ; texte ; [ no_départ ] )
Formule : =CHERCHE( "-" ; "Fact0001 - 02/02/2023" ) >>> Résultat : "10" : le tiret "-" est le 10ème caractère de ce texte.
La fonction TROUVE
Utilité : Rechercher le numéro de position d'un caractère dans du texte.
Syntaxe : =TROUVE( texte_cherché ; texte ; [ no_départ ] )
Formule : =TROUVE( "-" ; "Fact0001 - 02/02/2023" ) >>> Résultat : "10" : le tiret "-" est le 10ème caractère de ce texte.
Les fonctions CHERCHE et TROUVE sont assez similaires. La fonction TROUVE prend elle en compte la "CASSE" et les erreurs, mais leur utilité reste assez équivalente.
Les 7 autres fonctions de texte
Il existe d'autres fonctions de texte plus difficiles à catégoriser qui pourtant ont chacune une utilité unique à faire valoir.
.png)
La fonction NBCAR
Utilité : Indiquer le nombre de caractère.s d'un texte.
Syntaxe : =NBCAR( texte )
Formule : =NBCAR( "75019 - Paris" ) >>> Résultat : "13" : Il y a effectivement 13 caractère dans le texte saisi. Les espaces et les tirets sont considérés comme du texte
et sont donc comptés en tant que caractère.
La fonction REPT
Utilité : Répéter X fois un ou plusieurs caractères.
Syntaxe : =REPT( texte ; no_fois )
Formule : =REPT( "0" ; 7 ) >>> Résultat : "0000000" : le caractère "0" a été répété 7 fois, le résultat sera donc composé de 7 "0" qui se suivent.
La fonction EXACT
Utilité : Vérifier si 2 textes sont strictement identiques (en étant sensible aux majuscules/minuscules). La fonction renvoie VRAI si c'est le cas et FAUX si ça ne l'est pas (en format booléen)
Syntaxe : =EXACT( texte1 ; texte2 )
Formule : =EXACT( "Othman" ; "othman" ) >>> Résultat : FAUX : Dans ce cas précis, le texte est le même mais l'un possède une majuscule au début quand l'autre n'en possède pas.
Les 2 textes ne sont donc pas STRICTEMENT identiques et le résultat est donc FAUX.
La fonction CAR
Utilité : Renvoyer le caractère correspondant au numéro de code indiqué. Chaque caractère dans Excel correspond à un nombre, il s'agit ici de générer le caractère en fonction du nombre équivalent à celui-ci.
Syntaxe : =CAR( nombre )
Formule : =CAR( 145 ) >>> Résultat : "ë" : Car le caractère correspondant au code numéroté 145 est le "ë".
Il est à noter que les caractères comprennent les chiffres, les lettres et également les caractères spéciaux.
Pour trouver une vraie utilité à la fonction CAR et aux fonctions ci-dessous, nous allons les combiner à d'autres fonctions. Par exemple, on souhaite extraire tout le texte situé avant un saut de ligne et pour cela on va donc utiliser la fonction TEXTE.AVANT. Le saut de ligne étant un caractère compliqué à saisir à la main, on va donc intégrer une fonction CAR(10) (le caractère 10 correspond au saut de ligne) dans la fonction TEXTE.AVANT pour que le résultat nous renvoie tout le texte situé avant le saut de ligne.
La fonction UNICAR
Utilité : Renvoyer le caractère correspondant au numéro Unicode indiqué (code UTF-8 ou UTF-16).
Syntaxe : =UNICAR( nombre )
Formule : =UNICAR( 123 ) >>> Résultat : "{" : Car le caractère correspondant au numéro Unicode 123 est le "{".
La fonction CODE
Utilité : Renvoyer le numéro de code correspondant au premier caractère du texte. Elle est l'inverse de la fonction CAR qui elle va chercher le caractère et non son numéro de code.
Syntaxe : =CODE( texte )
Formule : =CODE( "ë" ) >>> Résultat : 145 : Car le numéro de code correspondant au caractère "ë" est le 145.
La fonction UNICODE
Utilité : Renvoyer le numéro Unicode (code UTF-8 ou UTF-16) correspondant au premier caractère d'un texte. Elle est l'inverse de la fonction UNICAR qui elle va chercher le caractère et non son numéro Unicode.
Syntaxe : =UNICODE( texte )
Formule : =UNICODE( "{" ) >>> Résultat : 123 : Car le numéro Unicode correspondant au caractère "{" est le 123.
Voici donc les 31 fonctions de texte que proposent Excel, l'enjeu désormais est de bien savoir quand / comment / avec quoi / pourquoi les utiliser.
Comme pour les fonctions de date, la vraie puissance de ce type de fonctions est de bien savoir les utiliser individuellement mais aussi de les combiner pour obtenir les meilleurs résultats possibles.
Et c'est pour savoir au mieux comment s'en servir que nous avons structuré tout ça en 6 grands types de fonctions de texte.
Ce qui permet une meilleure compréhension du rôle et des avantages de chacune d'entre elle pour pouvoir mieux déterminer quelle fonction utiliser dans un contexte C.
Et pour encore mieux maitriser et comprendre les fonctions de texte, vous pouvez aussi vous former vous inscrire à l'une de nos formations. On a tout ce qu'il faut pour vous transformer en king de la date et bien plus encore.
Vous êtes un particulier, rendez-vous sur la page de nos formations individuelles !
Vous êtes une entreprise, rendez-vous sur la page de nos offres entreprises !
Voici le retour de Jordan sur sa formation individuelle !
