Dans cet article et dans cette vidéo, je vous montre comment extraire du texte d’une chaîne de caractères grâce à des formules Excel.
Le fichier support de ce tutoriel est disponible en téléchargement en cliquant sur le lien sous cet article, juste avant la zone de commentaires. Je vous encourage vivement à le télécharger pour reproduire l’ensemble des manipulations afin d’intégrer l’ensemble de ces concepts.
4 EXEMPLE 3 : Extraire des données d’un fichier structuré
Les fonctions GAUCHE et DROITE permettent d’extraire des caractères à partir de la gauche ou de la droite d’une chaîne de caractères.
Ici, la fonction GAUCHE fait référence à la cellule E3, on lui demande de retourner 6 caractères à partir de la gauche. Le résultat obtenu est : « Geralt ».
La fonction DROITE quant à elle fonctionne dans le sens inverse. Ici, on lui demande de retourner les 3 derniers caractères de la chaîne. Le résultat obtenu est « Riv ».
La fonction STXT permet d’extraire le nombre de caractères souhaités à partir d’un caractère précisé par son emplacement.
Dans cet exemple, on se positionne à la huitième position dans la chaîne et on retourne 2 caractères, la fonction retourne « De ».
Pour l’autre exemple, juste à droite, on retourne trois caractères à partir de la 11ème position, la fonction retourne « Riv ».
La fonction NBCAR permet d’obtenir le nombre de caractères de l’ensemble d’une chaîne de caractères.
NBCAR de E3 retourne 13 puisque dans la cellule E3, on a 13 caractères.
C’est une fonction très utile lorsqu’elle est imbriquée avec d’autres fonctions.
La particularité de la fonction TROUVE réside dans le fait qu’elle est sensible à la casse. Elle est capable de distinguer les majuscules des minuscules, ce qui n’est pas le cas de la fonction CHERCHE.
Lorsque l’on demande où se trouve le « R » majuscule dans la chaîne en E3 à la fonction TROUVE, elle retourne le chiffre 11.
En effet le « R » majuscule se trouve à la 11ème position dans cette chaîne.
Lorsque l’on fait exactement la même chose avec la fonction CHERCHE, celle-ci retourne le chiffre 3 parce qu’elle n’est pas sensible à la casse.
Le premier « r » qu’elle trouve, qu’il soit en majuscule ou en minuscule se trouve à la 3ème position dans notre chaîne de caractères.
Autre grosse différence entre ces deux fonctions TROUVE et CHERCHE : la fonction TROUVE ne permet pas d’utiliser des caractères génériques, alors que la fonction CHERCHE le permet.
On verra cela dans le 2ème exemple et ça va nous être vraiment très utile!
C’est le premier espace trouvé dans la chaîne en partant de la gauche qui permet de séparer le prénom du nom. Que ce soit pour extraire le prénom ou le nom, il nous faut tout d’abord trouver où se situe cet espace dans la chaîne.
Pour cela on va utiliser la fonction CHERCHE de cette façon :
On demande à la fonction de nous dire où se trouve le premier espace de la chaîne en saisissant simplement cet espace entre des guillemets, puis en lui demandant d’effectuer cette recherche dans la chaîne qui se trouve en A6 et de le faire à partir de la première position dans la chaîne de caractères.
Pour la chaîne en A6, on trouve cet espace à la 7ème position, en A7, à la 5ème position,…
Pour la chaîne en A6, on a donc la formule suivante :
Nous ne voulons pas récupérer l’espace qui est à la 7ème position, donc on soustrait 1 au résultat retourné par la fonction CHERCHE, ce qui donne 6, et donc « Geralt ».
Pour A7 et les celulles suivantes cela fonctionne de la même façon!
Pour connaître le nombre de caractères à récupérer à partir de la droite, il nous suffit de soustraire la position de l’espace au nombre total de caractères de la chaîne.
Et pour connaître le nombre total de caractères, et bien on va utiliser tout simplement la fonction NBCAR.
Utilisez sur la cellule A6, la fonction NBCAR retourne le chiffre 13, puisque cette cellule contient 13 caractères.
Sur A7 elle retourne 10, car la chaîne « Lara Croft » contient 10 caractères.
Nous avons donc maintenant tout ce qu’il faut pour extraire le nom de façon dynamique de nos chaînes de caractères.
Pour cela, le premier argument de la fonction DROITE est comme d’habitude notre chaîne de caractères, et le deuxième correspond au résultat de la fonction NBCAR, le nombre de caractères moins la position du caractère espace retourné par la fonction CHERCHE :
Pour A6, 13 – 7 = 6, ce qui correspond à « De Riv », les six caractères en partant de la droite.
Et sur A7, 10 – 5 = 5, ce qui correspond à « Croft », les cinq caractères en partant de la droite, etc… pour les autres cellules qui se trouvent juste en dessous.
Vous trouverez n colonne H une autre combinaison de fonctions en utilisant STXT et NBCAR pour vous montrer qu’il y a tout simplement plusieurs façons de faire avec des fonctions.
Dans ce contexte, cette fonction est un peu plus complexe mais comme elle peut vous être utile, j’ai souhaité la mettre dans le fichier. Je vous laisse en prendre connaissance dans le fichier pour ne pas trop alourdir le tuto.
Une date est systématiquement structurée de un jour sur 2 positions + un « / », un mois sur 2 positions + un « / » et une année sur quatre positions.
On va utiliser des caractères génériques afin de modéliser cette date dans notre recherche que l’on va effectuer avec la fonction CHERCHE.
Notre objectif tout d’abord sera de rechercher cette structure de données dans la chaîne afin de savoir exactement où commence la date au sein de la chaîne de caractères.
La modélisation de cette date avec des caractères génériques prend la forme suivante « ??/??/???? » et la recherche de ce modèle de données dans la chaîne présente en A6 se fait grâce à cette formule :
Le résultat de cette formule retourne 25 pour A6.
Pour A7, la formule retourne 18, etc…
Une fois que l’on a cette information, il suffit d’utiliser la fonction STXT afin de demander à Excel de retourner 10 caractères (une date est toujours sur 10 caractères) à partir de l’endroit où il a trouvé la structure de date dans la chaîne.
Pour A6, cela donne la formule suivante :
Avec la fonction TROUVE, on n’aurait pas pu faire ça.
L’utilisation des caractères génériques avec CHERCHE est très puissante!
Je vous encourage à faire des tests car une fois maîtrisés vous pourrez résoudre des cas complexes que vous n’auriez jamais imaginés!
On va faire cela avec la fonctionnalité « Convertir » d’Excel et grâce aux fonctionnalités d’importation de fichiers dans Excel.
Dans notre exemple, on voit qu’en colonne A, on a des données qui sont séparées par des points virgules et que les chaînes de caractères sont entre des guillemets.
On a souvent des données sous ce format lorsque l’on exporte des données à partir d’un système tiers (ERP et autres logiciels…).
Cette opération se fait en plusieurs étapes :
1 – On sélectionne l’ensemble des cellules.
2 – On clique sur l’outil « Convertir » qui se trouve dans le ruban « Données ».
3 – L’assistant de conversion s’ouvre.
On choisit l’option « Délimité » pour ce type de fichier puisque ici on a des données délimitées par des points virgules.
4 – Choix du séparateur dans l’assistant de conversion.
On choisit le séparateur « Point-virgule ».
On garde les guillemets en « identificateur de texte ».
Ensuite on clique sur suivant pour arriver à la dernière étape.
5 – Choix de la destination des données.
Avant de cliquer sur terminer, on choisit C3 en destination, sinon cela va écraser les données dans la zone A3 à A10 et ce n’est pas ce que nous souhaitons.
On clique ensuite sur « Terminer » et les données arrivent dans quatre colonnes distinctes avec le personnage en premier, le lieu de naissance en deuxième colonne, la date de naissance en 3ème, puis le jeu.
La majorité des logiciels (ERP, logiciels dans le cloud,…) ont des fonctionnalités d’export de données, que ce soit en CSV, en TXT ou en XML. Dans notre exemple, on va importer dans Excel un fichier que l’on aura préalablement exporté à partir d’un logiciel tiers.
Donc pour ça, il suffit de sélectionner le fichier préalablement exporté en allant dans « Fichier » Ouvrir.
Dans mon cas, je vais le chercher directement dans parcourir.
Attention, très important ici, il faut que vous sélectionniez « tous les fichiers » car par défaut on a souvent uniquement les fichiers Excel. Hors, les fichiers TXT, CSV, XML,… ne seront pas visibles si vous ne sélectionnez pas « Tous les fichiers ».
Maintenant, que j’ai sélectionné « Tous les fichiers », je sélectionne mon fichier TXT et je clique sur « Ouvrir ». Un assistant d’importation de texte ressemblant très fortement à l’assistant de conversion s’ouvre.
1 – Etape 1
Dans cette première étape, on précise que les données sont « Délimitées ».
On précise aussi que les données ont des en-têtes afin qu’Excel reconnaisse automatiquement le fait que les noms de colonnes sont dans la ligne 1.
Les étapes suivantes sont identiques à celles de l’exemple de la fonctionnalité « Convertir » : nous précisons que le type de fichier est « Délimité », que le séparateur est le point virgule et que les identificateurs de texte sont des guillemets.
Nous n’avons ensuite plus qu’à cliquer sur « Terminer », et les données contenues dans le fichier TXT apparaissent dans une fenêtre Excel à part avec un résultat similaire à ce que l’on avait dans l’exemple précédent, sauf qu’ici les données proviennent directement d’un fichier texte structuré.
Tout, sauf peut être… la pratique! Alors pour bien intégrer tout cela, téléchargez le fichier exemple et étudiez plus en détail ces formules, testez par vous même! Il n’y a rien de mieux que la pratique pour progresser!
Et pour finir, je voudrais vous demander votre avis concernant ce tutoriel. Comme vous l’avez vu il y a une vidéo, un article complet et un fichier joint. Je vais conserver le format vidéo pour les prochains tutos, mais avez vous vraiment besoin d’un article comme celui-ci en complément? Est ce que finalement la vidéo plus le fichier joint ne serait pas suffisant? J’attends vos retours dans la zone commentaire, ci-dessous. Merci d’avance
Le fichier support de ce tutoriel est disponible en téléchargement en cliquant sur le lien sous cet article, juste avant la zone de commentaires. Je vous encourage vivement à le télécharger pour reproduire l’ensemble des manipulations afin d’intégrer l’ensemble de ces concepts.
SOMMAIRE
1 Les 6 fonctions de texte incontournables1.1 Les fonctions GAUCHE() et DROITE()
1.2 La fonction STXT()
1.3 La fonction NBCAR()
1.4 Les fonctions TROUVE() et CHERCHE()
2 EXEMPLE 1 : Extraction du nom et du prénom2.1 Extraction du prénom
2.2 Extraction du nom
3 EXEMPLE 2 : Extraire une date de naissance au milieu d’une chaîne4 EXEMPLE 3 : Extraire des données d’un fichier structuré
4.1 Utilisation de la fonctionnalité « Convertir »
4.2 Importation d’un fichier en provenance d’un système tiers
5 Pour conclure…Les 6 fonctions de texte incontournables
L’ensemble des exemples de ces 6 fonctions s’appuie sur la chaîne de caractères « Geralt De Riv » qui se trouve en E3 dans notre fichier de démo :Les fonctions GAUCHE() et DROITE()
Les fonctions GAUCHE et DROITE permettent d’extraire des caractères à partir de la gauche ou de la droite d’une chaîne de caractères.
Ici, la fonction GAUCHE fait référence à la cellule E3, on lui demande de retourner 6 caractères à partir de la gauche. Le résultat obtenu est : « Geralt ».
La fonction DROITE quant à elle fonctionne dans le sens inverse. Ici, on lui demande de retourner les 3 derniers caractères de la chaîne. Le résultat obtenu est « Riv ».
La fonction STXT()
La fonction STXT permet d’extraire le nombre de caractères souhaités à partir d’un caractère précisé par son emplacement.
Dans cet exemple, on se positionne à la huitième position dans la chaîne et on retourne 2 caractères, la fonction retourne « De ».
Pour l’autre exemple, juste à droite, on retourne trois caractères à partir de la 11ème position, la fonction retourne « Riv ».
La fonction NBCAR()
La fonction NBCAR permet d’obtenir le nombre de caractères de l’ensemble d’une chaîne de caractères.
NBCAR de E3 retourne 13 puisque dans la cellule E3, on a 13 caractères.
C’est une fonction très utile lorsqu’elle est imbriquée avec d’autres fonctions.
Les fonctions TROUVE() et CHERCHE()
TROUVE et CHERCHE permettent de retourner la position d’une chaîne de caractères dans une autre chaîne de caractères.La particularité de la fonction TROUVE réside dans le fait qu’elle est sensible à la casse. Elle est capable de distinguer les majuscules des minuscules, ce qui n’est pas le cas de la fonction CHERCHE.
Lorsque l’on demande où se trouve le « R » majuscule dans la chaîne en E3 à la fonction TROUVE, elle retourne le chiffre 11.
En effet le « R » majuscule se trouve à la 11ème position dans cette chaîne.
Lorsque l’on fait exactement la même chose avec la fonction CHERCHE, celle-ci retourne le chiffre 3 parce qu’elle n’est pas sensible à la casse.
Le premier « r » qu’elle trouve, qu’il soit en majuscule ou en minuscule se trouve à la 3ème position dans notre chaîne de caractères.
Autre grosse différence entre ces deux fonctions TROUVE et CHERCHE : la fonction TROUVE ne permet pas d’utiliser des caractères génériques, alors que la fonction CHERCHE le permet.
On verra cela dans le 2ème exemple et ça va nous être vraiment très utile!
EXEMPLE 1 : Extraction du nom et du prénom
L’objectif de cet exemple consiste à séparer les noms des prénoms dans une chaîne de caractères.C’est le premier espace trouvé dans la chaîne en partant de la gauche qui permet de séparer le prénom du nom. Que ce soit pour extraire le prénom ou le nom, il nous faut tout d’abord trouver où se situe cet espace dans la chaîne.
Pour cela on va utiliser la fonction CHERCHE de cette façon :
=CHERCHE(" ";A6;1)On demande à la fonction de nous dire où se trouve le premier espace de la chaîne en saisissant simplement cet espace entre des guillemets, puis en lui demandant d’effectuer cette recherche dans la chaîne qui se trouve en A6 et de le faire à partir de la première position dans la chaîne de caractères.
Pour la chaîne en A6, on trouve cet espace à la 7ème position, en A7, à la 5ème position,…
Extraction du prénom
La position exacte de cet espace étant maintenant connue quelle que soit la chaîne de caractères, il nous suffit d’imbriquer le résultat de cette recherche dans la fonction GAUCHE pour extraire le prénom.Pour la chaîne en A6, on a donc la formule suivante :
=GAUCHE(A6;CHERCHE(" ";A6;1)-1)Nous ne voulons pas récupérer l’espace qui est à la 7ème position, donc on soustrait 1 au résultat retourné par la fonction CHERCHE, ce qui donne 6, et donc « Geralt ».
Pour A7 et les celulles suivantes cela fonctionne de la même façon!
Extraction du nom
Passons maintenant à l’extraction du nom. Nous allons le faire à l’aide de la fonction DROITE.Pour connaître le nombre de caractères à récupérer à partir de la droite, il nous suffit de soustraire la position de l’espace au nombre total de caractères de la chaîne.
Et pour connaître le nombre total de caractères, et bien on va utiliser tout simplement la fonction NBCAR.
Utilisez sur la cellule A6, la fonction NBCAR retourne le chiffre 13, puisque cette cellule contient 13 caractères.
Sur A7 elle retourne 10, car la chaîne « Lara Croft » contient 10 caractères.
Nous avons donc maintenant tout ce qu’il faut pour extraire le nom de façon dynamique de nos chaînes de caractères.
Pour cela, le premier argument de la fonction DROITE est comme d’habitude notre chaîne de caractères, et le deuxième correspond au résultat de la fonction NBCAR, le nombre de caractères moins la position du caractère espace retourné par la fonction CHERCHE :
=DROITE(A6;NBCAR(A6)-CHERCHE(" ";A6;1))Pour A6, 13 – 7 = 6, ce qui correspond à « De Riv », les six caractères en partant de la droite.
Et sur A7, 10 – 5 = 5, ce qui correspond à « Croft », les cinq caractères en partant de la droite, etc… pour les autres cellules qui se trouvent juste en dessous.
Vous trouverez n colonne H une autre combinaison de fonctions en utilisant STXT et NBCAR pour vous montrer qu’il y a tout simplement plusieurs façons de faire avec des fonctions.
Dans ce contexte, cette fonction est un peu plus complexe mais comme elle peut vous être utile, j’ai souhaité la mettre dans le fichier. Je vous laisse en prendre connaissance dans le fichier pour ne pas trop alourdir le tuto.
EXEMPLE 2 : Extraire une date de naissance au milieu d’une chaîne
Dans ce deuxième exemple nous allons chercher à extraire une date de naissance au milieu de chaînes de caractères sans délimiteurs.Une date est systématiquement structurée de un jour sur 2 positions + un « / », un mois sur 2 positions + un « / » et une année sur quatre positions.
On va utiliser des caractères génériques afin de modéliser cette date dans notre recherche que l’on va effectuer avec la fonction CHERCHE.
Notre objectif tout d’abord sera de rechercher cette structure de données dans la chaîne afin de savoir exactement où commence la date au sein de la chaîne de caractères.
La modélisation de cette date avec des caractères génériques prend la forme suivante « ??/??/???? » et la recherche de ce modèle de données dans la chaîne présente en A6 se fait grâce à cette formule :
=CHERCHE("??/??/????";A6)Le résultat de cette formule retourne 25 pour A6.
Pour A7, la formule retourne 18, etc…
Une fois que l’on a cette information, il suffit d’utiliser la fonction STXT afin de demander à Excel de retourner 10 caractères (une date est toujours sur 10 caractères) à partir de l’endroit où il a trouvé la structure de date dans la chaîne.
Pour A6, cela donne la formule suivante :
=STXT(A6;CHERCHE("??/??/????";A6);10)Avec la fonction TROUVE, on n’aurait pas pu faire ça.
L’utilisation des caractères génériques avec CHERCHE est très puissante!
Je vous encourage à faire des tests car une fois maîtrisés vous pourrez résoudre des cas complexes que vous n’auriez jamais imaginés!
EXEMPLE 3 : Extraire des données d’un fichier structuré
Ici l’objectif est d’extraire des données en provenance d’un fichier structuré.On va faire cela avec la fonctionnalité « Convertir » d’Excel et grâce aux fonctionnalités d’importation de fichiers dans Excel.
Utilisation de la fonctionnalité « Convertir »
Cette fonctionnalité permet de dispatcher les données d’une colonne dans plusieurs colonnes en fonction de critères prédéfinis.Dans notre exemple, on voit qu’en colonne A, on a des données qui sont séparées par des points virgules et que les chaînes de caractères sont entre des guillemets.
On a souvent des données sous ce format lorsque l’on exporte des données à partir d’un système tiers (ERP et autres logiciels…).
Cette opération se fait en plusieurs étapes :
1 – On sélectionne l’ensemble des cellules.
2 – On clique sur l’outil « Convertir » qui se trouve dans le ruban « Données ».
3 – L’assistant de conversion s’ouvre.
On choisit l’option « Délimité » pour ce type de fichier puisque ici on a des données délimitées par des points virgules.
4 – Choix du séparateur dans l’assistant de conversion.
On choisit le séparateur « Point-virgule ».
On garde les guillemets en « identificateur de texte ».
Ensuite on clique sur suivant pour arriver à la dernière étape.
5 – Choix de la destination des données.
Avant de cliquer sur terminer, on choisit C3 en destination, sinon cela va écraser les données dans la zone A3 à A10 et ce n’est pas ce que nous souhaitons.
On clique ensuite sur « Terminer » et les données arrivent dans quatre colonnes distinctes avec le personnage en premier, le lieu de naissance en deuxième colonne, la date de naissance en 3ème, puis le jeu.
Importation d’un fichier en provenance d’un système tiers
Pour terminer, je vais vous montrer comment importer des données à partir d’un fichier brut généré à partir d’un logiciel tiers.La majorité des logiciels (ERP, logiciels dans le cloud,…) ont des fonctionnalités d’export de données, que ce soit en CSV, en TXT ou en XML. Dans notre exemple, on va importer dans Excel un fichier que l’on aura préalablement exporté à partir d’un logiciel tiers.
Donc pour ça, il suffit de sélectionner le fichier préalablement exporté en allant dans « Fichier » Ouvrir.
Dans mon cas, je vais le chercher directement dans parcourir.
Attention, très important ici, il faut que vous sélectionniez « tous les fichiers » car par défaut on a souvent uniquement les fichiers Excel. Hors, les fichiers TXT, CSV, XML,… ne seront pas visibles si vous ne sélectionnez pas « Tous les fichiers ».
Maintenant, que j’ai sélectionné « Tous les fichiers », je sélectionne mon fichier TXT et je clique sur « Ouvrir ». Un assistant d’importation de texte ressemblant très fortement à l’assistant de conversion s’ouvre.
1 – Etape 1
Dans cette première étape, on précise que les données sont « Délimitées ».
On précise aussi que les données ont des en-têtes afin qu’Excel reconnaisse automatiquement le fait que les noms de colonnes sont dans la ligne 1.
Les étapes suivantes sont identiques à celles de l’exemple de la fonctionnalité « Convertir » : nous précisons que le type de fichier est « Délimité », que le séparateur est le point virgule et que les identificateurs de texte sont des guillemets.
Nous n’avons ensuite plus qu’à cliquer sur « Terminer », et les données contenues dans le fichier TXT apparaissent dans une fenêtre Excel à part avec un résultat similaire à ce que l’on avait dans l’exemple précédent, sauf qu’ici les données proviennent directement d’un fichier texte structuré.
Pour conclure…
Vous avez maintenant tout pour devenir un expert de l’utilisation des formules en lien avec des chaînes de caractères!Tout, sauf peut être… la pratique! Alors pour bien intégrer tout cela, téléchargez le fichier exemple et étudiez plus en détail ces formules, testez par vous même! Il n’y a rien de mieux que la pratique pour progresser!
Et pour finir, je voudrais vous demander votre avis concernant ce tutoriel. Comme vous l’avez vu il y a une vidéo, un article complet et un fichier joint. Je vais conserver le format vidéo pour les prochains tutos, mais avez vous vraiment besoin d’un article comme celui-ci en complément? Est ce que finalement la vidéo plus le fichier joint ne serait pas suffisant? J’attends vos retours dans la zone commentaire, ci-dessous. Merci d’avance
Dernière édition:
