Extraire du texte d’une chaîne de caractères avec des fonctions

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.

1_Extraire_Texte_Cellules_Fonctions_MINIATURES.jpg
Bouton-sabonner_youtube_150.png

SOMMAIRE​

1 Les 6 fonctions de texte incontournables
1.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énom
2.1 Extraction du prénom​
2.2 Extraction du nom​
3 EXEMPLE 2 : Extraire une date de naissance au milieu d’une chaîne
4 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 :
image-2.png


Les fonctions GAUCHE() et DROITE()​

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()​

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()​

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.

Fonction Trouve et Cherche

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.

Extraction Noms et Prénoms

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.

image-8-2048x1013v2.jpg


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.

image-9-2048x529.png

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.

Assistant de conversion étape 1


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 ».

Assistant de conversion étape 2

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.

Assistant de conversion étape 3

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.

image-15-2048x529.png

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 ».

image-13.png


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.

image-14.png

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 😉
 

Pièces jointes

  • 1_Extraire Texte de Cellules.zip
    26.1 KB · Affichages: 94
Dernière édition:

eriiic

XLDnaute Barbatruc
Bonjour,

personnellement je préfère nettement une lecture à une vidéo que je lâche en général au bout de 15s
Mais je pense que tu auras les 2 types de public, certains djeuns n'ont l'air de connaitre que ça ;-)
eric
 

ChTi160

XLDnaute Barbatruc
Bonjour ,
Je pense que les deux formats sont complémentaires !
Beau travail que je vais suivre ,car nul en Formules Lol
Merci!
Bonne journée
jean marie
 
Dernière édition:

patricktoulon

XLDnaute Barbatruc
Bonjour David
c'est vraiment pas mal
pour l'importation de fichier txt,csv ,etc... parler du format aurait été un plus
en effet des fois les formats de fichiers ne sont pas en UTF-8 et on a des surprise
excellent pour les débutants
on est complètement dans le e-learning
a suivre
 

CB60

XLDnaute Barbatruc
Bonjour David et le Forum
J'ai regardé le fichier et dans ta formule complexe tu pourrais aussi faire voir cela:
VB:
=STXT(A6;CHERCHE("??/??/????";A6);10)*1
avec format de cellule date
mais cela n'enlève rien a l'intérêt et la clarté de ton fichier.
Merci beaucoup

EDIT oui, pour l'article et le fichier (Génial)
 

Webperegrino

XLDnaute Impliqué
Supporter XLD
Le Forum, David,
Bonjour,
Votre tuto est excellent : belle initiative.
La présentation est parfaite, nette.
Ce tuto m'a permis de découvrir les possibilités de la fonction CONVERTIR.
Merci et bon courage pour des prochains tutos, certainement tout aussi réussis que votre premier essai.
Bravo pour votre partage de connaissances.
Cordialement,
Webperegrino
 

soan

XLDnaute Barbatruc
Inactif
Bonjour David, le fil,

Gwynbleidd m'a écrit et demande si tu veux bien éviter de mentionner son nom
Geralt De Riv, au titre du RGPD (Règlement de la Guilde Protectrice des Donjons, châteaux,
et autres lieux d'habitation des super-héros)
. 🏰 🏯 🏡 🏕️

Lara Croft préférerait que tu la mentionne sous son pseudo Tomb Raider ;
elle demande la permission d'effectuer des fouilles archéologiques dans
les archives d'XLD ... mais quels trésors caches-tu donc ? 🤑 🤑 🤑

soan
 
Dernière édition:

Alain 06

XLDnaute Nouveau
Bonjour
Impossible de charger le fichier en entrant mon pseudo et mon mot de passe que faire.
Éviter, si possible de multiplier les inscriptions et mots de passe, il y en a beaucoup trop.
Très bon tuto, j'en apprends tous les jours et j’espère que cela va durer longtemps.
 

Carole Léaud

XLDnaute Nouveau
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.


SOMMAIRE​

1 Les 6 fonctions de texte incontournables
1.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énom
2.1 Extraction du prénom​
2.2 Extraction du nom​
3 EXEMPLE 2 : Extraire une date de naissance au milieu d’une chaîne
4 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 :
Regarde la pièce jointe 1148182

Les fonctions GAUCHE() et DROITE()​

Regarde la pièce jointe 1148183
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()​

Regarde la pièce jointe 1148184

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()​

Regarde la pièce jointe 1148185
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.

Regarde la pièce jointe 1148186
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.

Regarde la pièce jointe 1148187
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.

Regarde la pièce jointe 1148197

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.

Regarde la pièce jointe 1148188
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.

Regarde la pièce jointe 1148189

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 ».

Regarde la pièce jointe 1148190
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.

Regarde la pièce jointe 1148191
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.

Regarde la pièce jointe 1148192

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 ».

Regarde la pièce jointe 1148193

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.

Regarde la pièce jointe 1148194
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 😉
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.


SOMMAIRE​

1 Les 6 fonctions de texte incontournables
1.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énom
2.1 Extraction du prénom​
2.2 Extraction du nom​
3 EXEMPLE 2 : Extraire une date de naissance au milieu d’une chaîne
4 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 :
Regarde la pièce jointe 1148182

Les fonctions GAUCHE() et DROITE()​

Regarde la pièce jointe 1148183
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()​

Regarde la pièce jointe 1148184

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()​

Regarde la pièce jointe 1148185
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.

Regarde la pièce jointe 1148186
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.

Regarde la pièce jointe 1148187
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.

Regarde la pièce jointe 1148197

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.

Regarde la pièce jointe 1148188
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.

Regarde la pièce jointe 1148189

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 ».

Regarde la pièce jointe 1148190
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.

Regarde la pièce jointe 1148191
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.

Regarde la pièce jointe 1148192

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 ».

Regarde la pièce jointe 1148193

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.

Regarde la pièce jointe 1148194
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 😉
 

Discussions similaires

Statistiques des forums

Discussions
311 720
Messages
2 081 915
Membres
101 838
dernier inscrit
Christelle.B86