TEXTE.AVANT est une fonction EXCEL qui va permettre de retourner le texte se trouvant avant un caractère ou une chaine de caractères.
Cette fonction va donc permettre d’extraire des éléments contenus dans du texte se trouvant dans une cellule. Elle permet d’avoir plus facilement un résultat équivalent à une utilisation combinée des fonctions GAUCHE et CHERCHE / TROUVE.
Dans cet article, nous allons vous présenter les arguments que peut prendre la fonction TEXTE.AVANT, et vous proposer plusieurs exemples concrets d’utilisation.
Quand c’est possible, nous vous donnerons également une formule permettant d’avoir un résultat équivalent en utilisant GAUCHE / CHERCHE / TROUVE car TEXTE.AVANT n’est pas disponible dans toutes les versions de Microsoft Office.
TEXTE.AVANT n’est disponible qu’avec Office 365.
Table des matières :
- Syntaxe et arguments de la fonction
- Exemple 1 : Extraction de base
- Exemple 2 : Utilisation de l’argument [Instance_num]
- Exemple 3 : Utilisation d’un argument [Instance_num] négatif
- Exemple 4 : Utilisation d’un délimiteur multiple
- Exemple 5 : Utilisation des arguments [Match_mode] et [Match_end]
- Exemple 6 : Utilisation de l’argument [If_not_found]
La fonction TEXTE.AVANT est à saisir de la façon suivante :
=TEXTE.AVANT(Texte; Délimiteur; [Instance_num]; [Match_mode]; [Match_end]; [If_not_found])
Liste des arguments de la fonction et description :
- Texte : Il s’agit de la cellule qui contient le texte dans lequel vous souhaitez extraire du contenu. Cet argument est obligatoire.
- Délimiteur : Cet argument obligatoire correspond au texte qui va déterminer l’emplacement à partir duquel nous allons extraire tout le texte se trouvant avant.
- [Instance_num] : Correspond à l’instance du délimiteur à partir duquel nous souhaitons extraire le texte. Il s’agit d’un argument facultatif qui prend la valeur 1 lorsqu’il est omis. Lorsqu’il est positif, la recherche de l’argument [Instance_num] se fait à partir du début de l’argument Texte. Lorsqu’il est négatif, la recherche de fait à partir de la fin.
- [Match_mode] : Argument facultatif permettant de définir si la recherche du Délimiteur doit respecter la casse ou non. Lorsqu’il est omis, la recherche respecte la casse. L’argument peut prendre les valeurs suivantes :
- 0 (ou omis) : Respecte la casse.
- 1 : Ne respecte pas la casse.
- [Match_end] : Lorsque le Délimiteur n’est pas trouvé et que cet argument est activé, il permet de considérer la fin du texte comme délimiteur. L’argument peut prendre les valeurs suivantes :
- 0 (ou omis) : Ne pas considérer la fin du texte comme délimiteur.
- 1 : La fin du texte sera considérée comme un délimiteur.
- [If_not_found] : Argument facultatif qui permet de renvoyer une valeur si aucune correspondance n’est trouvée. Par défaut, #N/A sera renvoyé.
Ce premier exemple présente l’utilisation la plus basique de TEXTE.AVANT. Nous allons utiliser les deux arguments obligatoires de la fonction : Texte et Délimiteur. Les autres arguments seront omis.
Description de notre tableau de données : En colonne A, l’identité de clients au format Nom et Prénom séparés d’un espace.
Objectif : Extraire le nom vers la colonne B.
A noter : Nous avons affiché en colonne C la formule qu’il faut utiliser pour obtenir le résultat attendu.
Nos données sont au format Nom Prénom, séparés par un espace. Le nom se trouve à gauche et le séparateur est un espace. Nous allons donc utiliser la fonction pour extraire le texte se trouvant avant cet espace.
Aperçu du tableau :
Dans la cellule B2, nous allons saisir la formule suivante :
=TEXTE.AVANT(A2;" ")
- Texte : A2 : Correspond à la cellule qui contient le texte à extraire.
- Délimiteur : » « : Le caractère délimiteur à utiliser dans notre cas est l’espace.
Aperçu du tableau avec le résultat :
Résultat : Nous avons extrait tout le texte qui se trouvait avant le premier espace trouvé la cellule A2.
A noter : La qualité des données brutes (avant traitement) est essentielle lors de l’utilisation de ce type de formule. En effet, nous avons utilisé l’espace comme délimiteur car c’est celui qui était présent dans nos données brutes. Pour des noms prénoms, il est préférable que les données soient séparées par un autre caractère (un point virgule par exemple) car avec un espace, les cas des prénoms composés seront mal gérés. C’est un point à prendre en compte lorsque vous ferez des extractions de données pour les traiter.
Dans cet exemple, nous allons utiliser l’argument facultatif [Instance_num] qui va nous permettre d’extraire le texte se trouvant avant la deuxième occurrence trouvée du délimiteur.
Description de notre tableau de données : En colonne A, les coordonnées postales de clients. Le séparateur est l’espace.
Objectif : Extraire le nom et le prénom vers la colonne B.
A noter : Nous avons affiché en colonne C la formule qu’il faut utiliser pour obtenir le résultat attendu.
Si l’argument [Instance_num] n’est pas renseigné, l’extraction se fait dès le premier espace détecté. Pour extraire le nom et le prénom, il faut extraire le texte se trouvant avant le deuxième espace.
Aperçu du tableau :
Dans la cellule B2, nous allons saisir la formule suivante :
=TEXTE.AVANT(A2;" ";2)
- Texte : A2 : Correspond à la cellule qui contient le texte à extraire.
- Délimiteur : » « : Le caractère délimiteur à utiliser dans notre cas est l’espace.
- [Instance_num] : 2 : On recherche le deuxième espace dans le texte.
Aperçu du tableau avec le résultat :
Résultat : Nous avons extrait tout le texte qui se trouvait avant le deuxième espace trouvé dans la cellule A2.
Autre méthode :
Il aurait été possible d’avoir le même résultat en utilisant une combinaison des fonctions GAUCHE et CHERCHE. La saisie est dans ce cas beaucoup plus complexe.
=GAUCHE(A2;CHERCHE(" ";A2;CHERCHE(" ";A2;1)+1))Avec l’imbrication de fonctions CHERCHE, on récupère la position du deuxième espace et on extrait le contenu à gauche de cette position avec la fonction GAUCHE.
A la place de CHERCHE, on peut utiliser TROUVE. Le résultat sera identique.
=GAUCHE(A2;TROUVE(" ";A2;TROUVE(" ";A2)+1))La différence entre CHERCHE et TROUVE est que TROUVE est sensible à la casse (ce qui n’a aucun impact dans notre exemple). TEXTE.AVANT utilise l’argument [Match_mode] pour gérer la casse.
Aperçu :
Dans l’exemple 2, nous avons utilisé un argument [Instance_num] positif pour commencer la recherche du délimiteur à partir du début du texte. Dans ce nouvel exemple, nous allons utiliser un argument [Instance_num] négatif pour commencer la recherche du délimiteur à partir de la fin.
Description de notre tableau de données : En colonne A, les coordonnées postales de clients. Les données sont structurées de la façon suivante : Nom et prénom, adresse, code postal, ville. Chaque bloc de données est séparé par un point-virgule.
Objectif : Extraire le nom et le prénom vers la colonne B de notre tableau, mais en utilisant un argument [Instance_num] négatif. Dans notre cas, c’est possible car nos données sont correctement structurées. On dispose d’un caractère spécifique dédié à la séparation des blocs (le point-virgule) et chaque bloc se trouve à la même position.
Aperçu du tableau :
Dans la cellule E2, nous allons saisir la formule suivante :
=TEXTE.AVANT(A2;";";-3)
- Texte : A2 : Correspond à la cellule qui contient le texte à extraire.
- Délimiteur : « ; » : Le caractère délimiteur à utiliser dans notre cas est le point-virgule.
- [Instance_num] : -3 : On recherche le troisième point-virgule en partant de la fin.
Aperçu du tableau avec le résultat :
Résultat : Nous avons extrait le nom et le prénom.
Dans cet exemple, nous allons utiliser plusieurs délimiteurs pour extraire du texte.
Description de notre tableau : En colonne A, nous avons le nom et le prénom d’un salarié, ainsi que son poste.
Objectif : Extraire le nom et le prénom vers la colonne B. Nous allons utiliser le poste comme délimiteur.
Aperçu du tableau :
Deux postes sont référencés dans le tableau : Employé et Manager. Nous allons renseigner ces deux éléments en tant que Délimiteur dans notre formule.
Dans la cellule B2, nous allons saisir la formule suivante :
=TEXTE.AVANT(A2;{"Employé";"Manager"})
- Texte : A2 : Correspond à la cellule qui contient le texte à extraire.
- Délimiteur : {« Employé »; »Manager »} : Il s’agit de nos deux délimiteurs. Chaque délimiteur doit être séparé par un point-virgule, et tout le groupe doit être entre crochets.
Les autres arguments sont omis.
Aperçu du résultat :
Résultat : Nous avons pu extraire tous les noms et prénoms se trouvant avant les mots « Employé » et « Manager ».
Remarque : On peut observer dans le résultat qu’il y a des erreurs d’extraction. Elles sont identifiables par le résultat #N/A qui signifie qu’Excel n’a pas été en mesure d’extraire les éléments souhaités. Les lignes concernées sont les 6, 11, 15 et 21. Ces lignes sortent en erreur pour les raison suivantes :
- Les lignes 6 et 8 n’ont pas de postes renseignés, donc pas de délimiteur à exploiter
- Les lignes 15 et 21 ont un délimiteur mais la casse n’est pas respectée (il manque des majuscules).
Nous allons voir dans les exemples suivants comment contourner ces erreurs.
Dans l’exemple 4, nous avons constaté que certaines extractions n’ont pas pu être réalisées et ont retourné un #N/A. Les causes de ces erreurs étaient un problème de majuscule dans le délimiteur et un problème d’absence de délimiteur. La formule était la suivante :
=TEXTE.AVANT(A2;{"Employé";"Manager"})Seuls les arguments Texte et Délimiteur avaient été renseignés, les autres omis.
Les arguments [Match_mode] et [Match_end] peuvent nous aider corriger ces erreurs.
Correction des problèmes de casse (minuscule / majuscule) :
Lorsqu’on ne renseigne pas l’argument [Match_mode], c’est la valeur par défaut qui s’applique, à savoir le 0, qui signifie qu’on respecte la casse lors de la recherche du délimiteur.
Aperçu de la formule qui respecte la casse :
=TEXTE.AVANT(A2;{"Employé";"Manager"};;0)Aperçu du résultat :
On constate les erreurs sur les lignes 15 et 21. En effet, Excel cherche « Manager » ou « Employé », alors qu’on a « manager » et « employé » dans le texte.
Pour régler le problème, il suffit d’activer l’argument [Match_mode] pour ne plus respecter la casse.
La formule à utiliser est la suivante (pour la cellule B2) :
=TEXTE.AVANT(A2;{"Employé";"Manager"};;1)
- Texte : A2 : Correspond à la cellule qui contient le texte à extraire.
- Délimiteur : {« Employé »; »Manager »} : Il s’agit de nos deux délimiteurs.
- [Instance_num] : L’argument est omis, la recherche commencera dès le premier délimiteur trouvé.
- [Match_mode] : 1 pour ne plus respecter la casse.
Aperçu du résultat :
Les lignes 15 et 21 n’ont plus d’erreur, les noms et prénoms ont été extraits.
Il reste toujours deux erreurs sur les lignes 6 et 11, elles vont pouvoir être corrigées avec [Match_end].
Correction des problèmes d’absence de délimiteur :
Lorsqu’on ne renseigne pas l’argument [Match_end], c’est la valeur par défaut qui s’applique, à savoir le 0, qui signifie que si le délimiteur n’est pas trouvé, une erreur est retournée.
Aperçu de la formule avec [Match_end] désactivé :
=TEXTE.AVANT(A2;{"Employé";"Manager"};;1;0)Aperçu du résultat :
On constate les erreurs sur les lignes 6 et 11. En effet, Excel cherche « Manager », « manager », « Employé » ou « employé » alors qu’aucun de ces éléments n’est présent dans le texte.
Pour régler le problème, il suffit d’activer l’argument [Match_end] pour considérer la fin du texte comme délimiteur lorsqu’aucun délimiteur n’a été trouvé.
La formule à utiliser est la suivante (pour la cellule B2) :
=TEXTE.AVANT(A2;{"Employé";"Manager"};;1;1)
- Texte : A2 : Correspond à la cellule qui contient le texte à extraire.
- Délimiteur : {« Employé »; »Manager »} : Il s’agit de nos deux délimiteurs.
- [Instance_num] : L’argument est omis, la recherche commencera dès le premier délimiteur trouvé.
- [Match_mode] : 1 pour ne plus respecter la casse.
- [Match_end] : 1 pour considérer la fin du texte comme délimiteur.
Aperçu du résultat :
Les lignes 6 et 11 n’ont plus d’erreur, les noms et prénoms ont été extraits.
Considérer la fin du texte comme un délimiteur avec [Match_end] peut ne pas convenir à toutes les situations. Dans notre exemple 5, on peut l’utiliser car le délimiteur est à la fin de notre texte. Si ça n’avait pas été le cas, tout le texte, à savoir le nom le prénom et tout ce qui aurait été présent derrière aurait été extrait.
Il est parfois préférable de sortir une erreur pour montrer que la donnée n’est pas fiable. L’argument [If_not_found] va nous permettre de personnaliser cette erreur.
En cas d’erreur, TEXTE.AVANT va renvoyer #N/A par défaut.
L’argument [If_not_found] peut vous permettre d’effectuer une autre action en cas d’erreur.
Dans notre exemple, nous allons afficher une ligne de texte pour montrer l’erreur.
La formule à utiliser est la suivante (pour la cellule B2) :
=TEXTE.AVANT(A2;{"Employé";"Manager"};;1;0;"<--!--> Pas de fonction renseignée <--!-->")
- Texte : A2 : Correspond à la cellule qui contient le texte à extraire.
- Délimiteur : {« Employé »; »Manager »} : Il s’agit de nos deux délimiteurs.
- [Instance_num] : L’argument est omis, la recherche commencera dès le premier délimiteur trouvé.
- [Match_mode] : 1 pour ne plus respecter la casse.
- [Match_end] : 0 pour retourner une erreur si on ne trouve pas le délimiteur.
- [If_not_found] : « <–!–> Pas de fonction renseignée <–!–> » : Ce texte sera affiché en cas d’erreur.
Aperçu du résultat :
Résultat : Les lignes 6 et 11 affichent le texte saisi dans l’argument [If_not_found].