Si vous êtes un utilisateur régulier du célèbre tableur de Microsoft, vous savez probablement déjà à quel point il peut être utile pour organiser, analyser et présenter des données chiffrées. Mais saviez-vous qu'Excel regorge également de fonctions puissantes pour manipuler du texte ? Ces fonctions, souvent méconnues, peuvent pourtant vous faire gagner un temps précieux et rendre vos tâches quotidiennes bien plus efficaces.
Dans cet article, nous allons explorer ces fonctions pour extraire, combiner, nettoyer et transformer du texte de manière efficace.
Alors, prêt à devenir un expert des fonctions texte d'Excel ? Suivez le guide !
Comment joindre (ou assembler, ou concaténer) du texte ?
Il peut être intéressant d’assembler des chaînes de caractères contenues dans des cellules.
L’exemple le plus courant concerne les nom/prénom stockés dans deux colonnes différentes, alors qu’on a besoin de les regrouper dans une seule et même colonne.
Joindre du texte avec une formule
Le plus simple est d'utiliser le signe &. Si le prénom est présent en B2, et le nom en A2, la formule pour obtenir le prénom suivi du nom est :
=B2&A2
On peut également utiliser la fonction CONCATENER, en indiquant en paramètre les cellules contenant le texte à joindre :
= CONCATENER(B2;A2)
Ces deux méthodes requièrent qu’on sélectionne les cellules une part une. Il est possible d’utiliser la fonction CONCAT, qui permet de sélectionner une plage de cellule. Par exemple :
= CONCAT(A1:B1)
Vous avez remarqué que le prénom et le nom sont collés avec les formules précédentes. Pour insérer un espace entre le prénom et le nom, il suffit d’ajouter " " dans les formules (mais cette méthode ne fonctionne pas avec CONCAT) :
= B2 & " " & A2
Avec la fonction CONCATENER, la formule sera :
= CONCATENER(B2 ; " " ; A2)
Enfin, la fonction JOINDRE.TEXTE combine toutes ces possibilités : elle permet de concaténer plusieurs cellules mais aussi d'insérer un délimiteur entre chacune des données. Vous pouvez également indiquer de ne pas prendre en compte les cellules vides.
Sa syntaxe est la suivante :
= JOINDRE.TEXTE( caractère de délimitation ; ignorer les cellules vides ; plage de cellule à concaténer)
Le paramètre ignorer les cellules vides contiendra VRAI si on ne souhaite pas insérer le caractère de délimitation lorsqu'une cellule est vide, ou FAUX si on souhaite systématiquement insérer le caractère de délimitation. Dans notre exemple, la formule sera :
= JOINDRE.TEXTE( " " ; VRAI ; A2:B2)
Joindre du texte avec le remplissage instantané
Le remplissage instantané renseigne automatiquement les données lorsqu’il détecte un motif (aussi appelé modèle).
Par exemple, si l’on souhaite concaténer les noms et prénoms contenus dans 2 colonnes séparées (colonne A pour les noms, colonne B pour les prénoms), cette fonctionnalité permet d’effectuer l’opération sans formule.
Il suffit de saisir le nom et le prénom dans la colonne C de la première ligne, puis de commencer à saisir le nom sur la ligne suivante. Excel va repérer votre modèle de saisie et vous proposer le remplissage instantané :
Appuyez alors sur Entrée. La saisie est répliquée jusqu’à la fin du tableau !
Le point fort du remplissage instantané, c'est qu'il respecte votre format de saisie :
si vous avez saisi le nom en majuscule et le prénom en minuscules, si vous avez mis le prénom entre parenthèse, si vous avez interverti le nom et le prénom, si vous avez inséré un caractère (ou une chaîne) entre le prénom et le nom.
Toutes les combinaisons sont possibles !
Comment récupérer du texte au début ou à la fin d'une chaîne ? Les fonctions GAUCHE et DROITE
La fonction GAUCHE permet de récupérer la partie gauche d’une chaîne de caractère, en indiquant simplement le nombre de caractères à récupérer. La syntaxe est la suivante :
=GAUCHE( chaine de caractère ; nombre de caractères)
La chaine de caractère peut faire référence à une cellule. Ainsi, si la cellule B2 contient l’expression 32730 BECCAS, la formule suivante permettra de récupérer le code postal seul :
=GAUCHE(B2 ; 5)
Dans cette fonction, B2 fait référence à la cellule contenant le code postal et la localité, et 5 indique le nombre de caractères que l’on souhaite récupérer à partir de la gauche.
La fonction DROITE permet d’effectuer le même travail, mais à partir de la droite…
Comment extraire du texte ? La fonction STXT
La fonction STXT permet d’extraire d’une chaine des caractères présent au milieu de la chaine. La syntaxe est la suivante :
STXT(chaîne ; début ; nombre de caractères)
Par exemple, si un NIR (n° de Sécurité sociale) est présent en A2, la formule suivante permet d’extraire l’année de naissance :
=STXT(A2 ; 2 ; 2)
Comment remplacer un texte par un autre ?
Il est possible de remplacer un caractère ou un groupe de caractère par un autre grâce à deux fonctions Excel : REMPLACER et SUBSTITUE.
REMPLACER substitue un texte à un autre à partir de la position indiquée. Sa syntaxe est la suivante :
REMPLACER(chaîne ; début ; nombre ; nouvelle chaîne)
Par exemple :
=REMPLACER("ABCDEF ;2;3;"XYZ") donne AXYZEF
La fonction SUBSTITUE est plus pratique. Sa syntaxe est très simple :
SUBSTITUE(chaîne ; ancienne chaîne ; nouvelle chaîne)
Elle remplace ancienne chaîne par nouvelle chaîne. Si position est spécifié, seul le texte à la position indiquée est remplacé.
Par exemple, si la cellule B1 contient 17000 LR, la formule suivante :
=SUBSTITUE(B1;"LR ;"LA ROCHELLE")
donne 17000 LA ROCHELLE.
Comment chercher un texte dans un autre ?
Il est parfois nécessaire de savoir si un texte en contient un autre. C’est l’objet des fonctions CHERCHE et TROUVE.
La fonction TROUVE
La fonction TROUVE renvoie la position d’une chaine de caractères dans une autre. Cette position correspond simplement au rang du caractère.
=TROUVE (texte à trouver ; texte principal)
Ainsi, si la cellule B1 contient 17000 LR, la formule suivante :
=TROUVE("LR" ; B1)
donnera 7 (dans l’expression 17000 LR, la sous-chaîne LR est au 7e rang).
Si la fonction ne trouve pas la valeur cherchée, c’est l’erreur #VALEUR! qui est renvoyée à la place.
Attention : la fonction TROUVE respecte la casse. Si on n’a pas respecté les majuscules/minuscules, la valeur cherchée ne sera pas trouvée.
La fonction CHERCHE
La fonction CHERCHE a une syntaxe identique à TROUVE :
=CHERCHER (texte à trouver ; texte principal)
Cependant, la fonction CHERCHE permet d’utiliser les caractères génériques « ? » (pour remplacer un caractère) et « * » (pour remplacer plusieurs caractères).
Si par exemple vous cherchez dans des références une expression du type FACT-0519-GRANDET dans la cellule A1, en faisant abstraction du n° de facture, la syntaxe sera :
=CHERCHER (A1 ; "FACT-*-GRANDET")
Si la fonction ne trouve pas la valeur cherchée, c’est l’erreur #VALEUR! qui est renvoyée à la place.
Comment mettre un texte en majuscules/minuscules ?
Excel dispose des fonctions suivantes pour gérer les majuscules/minuscules :
La fonction MAJUSCULE
Cette fonction met en majuscules l'intégralité du texte spécifié. Par exemple, si la cellule A1 contient la valeur Bordeaux, =MAJUSCULE(A1) renvoie BORDEAUX.
La fonction MINUSCULE
Cette fonction met en minuscules l'intégralité du texte spécifié. Par exemple, si la cellule A1 contient la valeur Bordeaux, =MINUSCULE(A1) renvoie bordeaux.
La fonction NOMPROPRE
NOMPROPRE renvoie le texte spécifié en mettant en majuscule la première lettre de chaque mot du texte, et en minuscule les suivantes. Par exemple, si la cellule A1 contient la valeur LANGUEDOC ROUSSILLON, =MINUSCULE(A1) renvoie Languedoc Roussillon.
Comment supprimer les espaces en trop ?
Il arrive que du texte contienne des espaces superflus entre les mots. La fonction SUPPRESPACE permet de les retirer facilement :
SUPPRESPACE(texte)
Par exemple, si la cellule A1 contient le texte " Place de la Concorde ", =SUPPRESPACE(A1) donnera "Place de la Concorde".
Comment convertir un texte en nombre ?
Excel permet de convertir du texte en nombre (à condition qu’il s’agisse d’une chaîne de caractère correspondant à un nombre…) et inversement.
TEXTE convertit un nombre en texte, et propose des possibilités de formatage :
TEXTE(nombre ; format_texte)
Par exemple, si la cellule A1 contient le nombre 62, la fonction suivante :
=TEXTE(A1;"0 jours") donne 62 jours (si vous avez un doute sur le format, lisez absolument ce cours !).
A l'inverse, CNUM convertit une chaîne en nombre pour pouvoir réaliser ensuite des calculs. Voici la syntaxe :
CNUM(chaîne)
Conclusion : quelles sont les fonctions texte les plus utiles ?
Les fonctions texte sont des outils très puissant mis à notre disposition dans Excel. Pour conclure, nous avons listé les fonctions texte les plus utilisées :
| CHERCHE | Trouve un texte dans un autre texte (sans respecter la casse). |
| CNUM | Convertit un texte en nombre. |
| CONCAT | Assemble le texte d'une plage de cellules pour n'en former qu'un seul. |
| CONCATENER | Assemble plusieurs textes pour n'en former qu'un seul. |
| CTXT | Convertit un nombre au format texte avec un nombre de décimales spécifié. |
| DROITE | Renvoie des caractères situés à la droite d'un texte. |
| EPURAGE | Supprime tous les caractères spéciaux (aussi appelés caractères de contrôle du texte). |
| GAUCHE | Renvoie des caractères situés à la gauche d'une chaîne de caractères. |
| JOINDRE.TEXTE | Assemble le texte de plusieurs cellules, en insérant un séparateur entre chaque élément. |
| MAJUSCULE | Convertit le texte en majuscules. |
| MINUSCULE | Convertit le texte en minuscules. |
| NBCAR | Renvoie le nombre de caractères contenus dans un texte. |
| REMPLACER | Remplace des caractères dans un texte. |
| REPT | Répète un texte un certain nombre de fois. |
| STXT | Renvoie des caractères d'un texte à partir de la position indiquée. |
| SUBSTITUE | Remplace un texte par un autre. |
| SUPPRESPACE | Supprime les espaces inutiles du texte. |
| TEXTE | Convertit un nombre au format texte. |
| TROUVE | Trouve un texte dans une autre, en respectant la casse. |
