Comment extraire un mot situé à la n-ième position d'une cellule dans Excel ?

Dans ce tutoriel, nous allons apprendre à extraire un mot situé à la n-ième position d'une cellule en utilisant Excel. Nous aborderons plusieurs méthodes, dont les formules et les fonctions personnalisées.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Présentation

Pour illustrer ce tutoriel, nous allons utiliser ce tableau contenant des informations sur différentes personnes, notamment leur nom, adresse, région et adresse e-mail. Les données sont présentées sous la forme d'une liste, chaque ligne représentant une personne et les informations les concernant. Les éléments d'information sont séparés par des espaces :

2. Séparer les colonnes avec l'espace et extraire un mot

Pour commencer, nous allons utiliser la fonctionnalité de séparation des colonnes d'Excel. Celle-ci consiste en effet à diviser le contenu d'une cellule en plusieurs colonnes en fonction d'un séparateur spécifique. Dans notre cas, nous utiliserons l'espace comme séparateur pour séparer les différents éléments d'information de chaque personne, tels que le nom, le prénom, le code postal, la ville, la région et l'adresse e-mail.

LE but sera alors d’extraire le prénom (situé en première position) ainsi que la ville (en quatrième position).

Pour cela, nous procédons de la manière suivante :

Pour commencer, nous sélectionnons les cellules dans lesquelles se trouvent les données à extraire, ici les cellules situées aux coordonnées A7 jusqu’à A16 :

Ensuite, nous allons nous rendre dans le menu « Données » afin de cliquer sur le bouton « Convertir » du groupe « Outils de données ». Nous pouvons lancer directement la fonctionnalité en utilisant la séquence de touches [Alt], [ É], [O].

Sur la boîte de dialogue qui s’affiche à l’écran, nous choisissons d’extraire les données en fonction d’un délimiteur :

Nous sélectionnons alors l’espace comme séparateur de colonne :

Nous constatons qu’Excel sépare bien les données en fonction des espaces dans l’aperçu des données situés en bas de la boîte de dialogue. Nous pouvons alors passer à l’étape suivante.

Ici, nous pouvons constater que par défaut, Excel va saisir les données séparées dans la cellule A7, laquelle correspond à la première des cellules de la sélection. Si nous validons la conversion à ce moment-là, les données d’origine seront alors écrasées. Si nous souhaitons extraire les données juste à côté, nous cliquons sur la flèche noire afin de sélectionner les coordonnées de la cellule B7 :

Maintenant, chaque mot est séparé dans une colonne différente :

Il ne reste plus qu’à supprimer les colonnes inutiles en effectuant un clic droit sur les en-têtes correspondants et en choisissant « Supprimer » :

Notez qu’il est également possible de supprimer une colonne en utilisant le raccourci clavier [Ctrl]+[-], lorsque celle-ci est sélectionnée.

Il ne reste plus qu’à mettre en forme les informations extraites de la base :

3. Utilisation des formules Excel STXT(), TROUVE(), etc.

Maintenant, découvrons une autre méthode, dans laquelle nous allons utiliser les formules Excel pour extraire le mot à la n-ième position.

Comme dans la partie précédent, nous allons souhaiter extraire le prénom et la ville :

Pour le prénom, c'est assez simple, étant donné que celui-ci se trouve au tout début de la chaîne, nous allons pouvoir utiliser la fonction GAUCHE() qui permet de récupérer un certain nombre de caractères à partir du début de la chaîne.

Cette fonction attend les arguments suivants :

 =GAUCHE(texte;no_car) 

Où :

• Texte : c’est le texte ou la cellule contenant la chaîne depuis laquelle nous allons souhaiter effectuer l’extraction,
• No_car : c’est le nombre de caractères à extraire.

Pour déterminer le nombre de caractères à utiliser pour ce deuxième argument, nous , nous allons devoir rechercher à quelle place se trouve le premier espace de la chaîne.

Cette information nous sera retournée par la fonction TROUVE().

En effet, celle-ci permet de localiser la première occurrence d'un caractère ou d'une chaîne de caractères spécifiques dans une autre chaîne. Elle attend les arguments suivants :

 =TROUVE(texte_cherché;texte;[no_départ]) 

Où :

• Texte_cherché : c’est le texte à rechercher à l’intérieur d’un texte donné, et pour lequel nous souhaitons obtenir la position,
• Texte : c’est le texte ou la cellule contenant la chaîne depuis laquelle nous allons souhaiter identifier la position d’un texte donné
• No_départ : c’est un argument facultatif correspondant à la position de départ de la recherche.

Pour extraire le prénom des personnes, nous utiliserons donc la formule suivante :

 =GAUCHE(A7;TROUVE(" ";A7)-1) 

Nous n’oublions pas de retirer un caractère au résultat obtenu avec la fonction TROUVE() pour se placer avant l’espace.

Par contre, pour identifier un mot situé à l'intérieur de la chaîne, c'est un petit peu plus compliqué. Pour cela, nous allons en effet devoir combiner plusieurs fonctions Excel. Dans notre exemple, nous souhaitons extraire la ville, qui se trouve entre le code postal et la région, en troisième position.

Tout d'abord, nous utiliserons la fonction TROUVE() pour localiser la position du premier espace après le code postal. Nous soustrayons ensuite 1 pour obtenir la position du dernier caractère du code postal.

 =TROUVE(" ";A7) 

Ensuite, nous utiliserons à nouveau la fonction TROUVE() pour localiser la position de l’espace suivant, en utilisant le résultat obtenu précédemment en tant qu’argument facultatif « no_depart », auquel nous ajoutons 1 caractère :

 =TROUVE(" ";A7;TROUVE("  ";A7)+1) 

Nous répétons une fois de plus l’opération pour obtenir la position du troisième mot :

 =TROUVE(" ";A7;TROUVE("  ";A7;TROUVE(" ";A7)+1)+1) 

Cela nous permet donc de connaître la position de l’espace situé juste avant la ville.

Comme vous pouvez l’imaginer, encapsuler ce résultat dans une quatrième fonction TROUVE() permettra de connaître la position de l’espace situé juste après.

À ce stade, nous aurons les positions des espaces qui entourent la ville.

Pour extraire la ville, nous utiliserons la fonction STXT() qui permet de récupérer une sous-chaîne à partir d'une chaîne de caractères en spécifiant la position de début et le nombre de caractères à extraire.

La fonction STXT accepte trois arguments :

• texte : La cellule contenant la chaîne de caractères à partir de laquelle vous souhaitez extraire une sous-chaîne.
• num_debut : La position du premier caractère à extraire dans la chaîne de caractères. La première position dans une chaîne de caractères est 1.
• nb_car : Le nombre de caractères à extraire à partir de la position spécifiée.

En combinant ces étapes dans une formule Excel, nous pourrons extraire la ville située à l'intérieur de la chaîne de caractères.

 =STXT(A7;TROUVE(" ";A7;TROUVE("  ";A7;TROUVE(" ";A7)+1)+1)+1;TROUVE("  ";A7;TROUVE(" ";A7;TROUVE(" ";A7;TROUVE("  ";A7)+1)+1)+1)-TROUVE(" ";A7;TROUVE("  ";A7;TROUVE(" ";A7)+1)+1)-1) 

Comme vous pouvez le constater, cette fonction est non seulement compliquée à mettre en place, car il ne faut pas nous tromper dans le nombre d’utilisation de la fonction TORUVE(), mais en plus elle est figée, et ne permettra pas par exemple de modifier rapidement la position du mot à extraire…

4. Création d'une fonction personnalisée EXTRAIREMOT

Pour éviter d’avoir à créer à chaque fois cette formule à rallonge, nous allons préférer mettre en place une fonction personnalisée, que nous pourrons ensuite appeler à la demande.

Cette fonction sera plus flexible et facile à utiliser que les formules Excel.

Une fonction personnalisée est un type de fonction développée en VBA (Visual Basic for Applications), le langage de programmation intégré aux applications de la suite Office pour créer des macros, des fonctions personnalisées et d'automatiser des tâches.

Pour créer une fonction personnalisée, nous commençons par ouvrir l'éditeur VBA en appuyant sur Alt + F11.

Une fois dans VBE (Visual Basic Editor), nous insérons un nouveau module en allant dans le menu "Insertion" et en sélectionnant "Module".

Puis, nous pouvons créer notre fonction en écrivant le code VBA correspondant à la fonction souhaitée, en commençant par la déclaration "Function", suivie du nom de la fonction, des arguments et du code qui définit le comportement de la fonction.

Function EXTRAIREMOT()
End Function

Lorsque nous validons en appuyant sur la touche [Entrée], VBE ajoute deux choses :

• La parenthèse à la suite du nom de la fonction, dans lesquelles nous allons venir saisir les arguments de la fonction juste après
• La ligne « End Sub », qui vient marquer la fin de la fonction. Tout ce que nous saisirons entre ces deux lignes sera exécuté lorsque nous appellerons la fonction EXTRAIREMOT().

Maintenant que la fonction personnalisée est créée, la première chose à faire va être d’y insérer les arguments qui vont permettre à l’utilisateur de spécifier comment celle-ci doit fonctionner.

La fonction va alors attendre trois arguments :

• texte : la chaîne de caractères dans laquelle le mot doit être extrait,
• position : la position du mot à extraire dans l’ensemble du texte
• séparateur : le séparateur utilisé pour diviser la chaîne de caractères en mots. Cet argument est facultatif, et sa valeur par défaut en cas d’omission est l'espace.

Function EXTRAIREMOT(texte As String, position  As Integer, Optional separateur As String = " ") As String

Ensuite, nous déclarons un tableau de chaînes de caractères nommé mots. Ce tableau sera utilisé pour stocker les mots individuels obtenus en divisant la chaîne de caractères texte.

Dim mots() As String

Pour alimenter ce tableau avec les mots du texte, nous utilisons la fonction VBA Split. Celle-ci permet de diviser la chaîne de caractères texte en un tableau de mots, en utilisant le séparateur spécifié (séparateur).

mots = Split(texte, séparateur)

Il ne reste plus qu’à retourner le mot situé à la position spécifiée (en ajustant l'index avec - 1, car les indices de tableau en VBA commencent à 0) à la valeur de retour de la fonction EXTRAIREMOT.

extraireMot = mots(position - 1)

Il faut alors savoir que si la position demandée n’existe pas dans le tableau « mot », alors la fonction EXTRAIREMOT retournera une erreur.

Cela sera par exemple le cas si l’argument position est inférieur à 1, ou supérieur au nombre de mots présent dans la chaîne texte.

Nous pouvons maintenant appuyer sur les touches [Ctrl]+[S] pour sauvegarder le classeur en tant que fichier Excel Macro-activé (.xlsm), ce qui permettra de conserver la fonction personnalisée une fois le classeur fermé.

Ensuite, nous pouvons retourner sur la feuille de calcul, pour tester la fonction.

Il ne reste alors qu’à utiliser la fonction EXTRAIREMOT() pour extraire le mot situé à la n-ième position, comme nous le ferions pour n’importe quelle autre fonction d’Excel :

 =extraireMot(A7;4) 

Nous pouvons également une cellule pour spécifier la position du mot, ce qui permettra de modifier plusieurs cellules d’un seul coup :

Si vous souhaitez utiliser un autre séparateur que l'espace, vous pouvez le spécifier comme troisième argument de la fonction, par exemple :

=extraireMot(A7;$E$6;";")