Tutoriel Excel : comment extraire du texte d'une cellule

Tutoriel Excel

Ce tutoriel montre comment utiliser les fonctions de sous-chaîne dans Excel pour extraire du texte d'une cellule, obtenir une sous-chaîne avant ou après un caractère spécifié, rechercher des cellules contenant une partie d'une chaîne, etc.

Avant de commencer à discuter des différentes techniques pour manipuler des sous-chaînes dans Excel, prenons un moment pour définir le terme. Alors, qu'est-ce qu'une sous-chaîne ? Simplement, cela fait partie d'une entrée de texte. Par exemple, si vous tapez quelque chose comme « ID-123 » dans une cellule, vous l'appellerez une chaîne alphanumérique, et n'importe quelle partie de la chaîne, par exemple « ID », est une sous-chaîne.

Bien qu'il n'existe pas de fonction de sous-chaîne dans Excel, il existe trois fonctions de texte (GAUCHE, DROITE et STXT) pour extraire une sous-chaîne d'une longueur donnée. En outre, il existe des fonctions « TROUVE » et « CHERCHE » pour obtenir une sous-chaîne avant ou après un caractère spécifique. Et, il existe une poignée d'autres fonctions pour effectuer des opérations plus complexes telles que l'extraction de nombres d'une chaîne, le remplacement d'une sous-chaîne par une autre, la recherche d'une correspondance de texte partielle, etc. Ci-dessous, vous trouverez des exemples de formules pour faire tout cela et bien plus encore !

Table des matières

Comment extraire une sous-chaîne d'une certaine longueur ?

Extraire la sous-chaîne du début de la chaîne (GAUCHE)

Récupèrer la sous-chaîne à la fin de la chaîne de texte (DROITE)

Extraire le texte du milieu de la chaîne (STXT)

Extraire la sous-chaîne avant ou après un caractère donné

Comment extraire du texte avant un caractère spécifique ?

Comment extraire du texte après un caractère ?

Comment extraire du texte entre deux instances d'un caractère ?

Exemple 1 - Extraire le nom d'utilisateur à partir d'ID de messagerie

Exemple 2 - Extraire le nom de domaine à partir d'ID de messagerie

Exemple 3 - Extraire le nom de domaine à partir d'ID de messagerie (sans .com)

Utilisation de la fonctionnalité « Convertir » pour extraire une sous-chaîne dans Excel

Utilisation de RECHERCHER et REMPLACER pour extraire du texte d'une cellule dans Excel

Exemple 1 - Extraire le nom d'utilisateur à partir d'ID de messagerie

Exemple 2 - Extraire le nom de domaine à partir d'ID de messagerie

Comment trouver une sous-chaîne dans Excel ?

Comment extraire une sous-chaîne d'une certaine longueur ?

Microsoft Excel propose trois fonctions différentes pour extraire du texte d'une longueur spécifiée d'une cellule. Selon l'endroit où vous souhaitez démarrer une extraction, utilisez l'une de ces formules:

  • Fonction GAUCHE - pour extraire une sous-chaîne de gauche.
  • Fonction DROITE - pour extraire le texte de droite.
  • Fonction STXT - pour extraire une sous-chaîne du milieu d'une chaîne de texte, en commençant d’un point que vous spécifiez.

Comme c'est le cas avec d'autres formules, les fonctions de sous-chaîne Excel sont mieux à apprendre d'un exemple, alors examinons-en quelques-unes.



Extraire la sous-chaîne du début de la chaîne (GAUCHE)

Pour extraire du texte à gauche d'une chaîne, utilisez la fonction Excel « GAUCHE »:

GAUCHE(texte;[no_car])

Où « texte » est l'adresse de la cellule contenant la chaîne source et No_car est le nombre de caractères que vous souhaitez extraire.

Par exemple, pour obtenir les 3 premiers caractères du début d'une chaîne de texte, utilisez cette formule:

=GAUCHE(A2; 3)

 

Récupérer la sous-chaîne à la fin de la chaîne de texte (DROITE)

Pour obtenir une sous-chaîne à partir de la partie droite d'une chaîne de texte, utilisez la fonction Excel « DROITE »:

DROITE(texte; [no_car])

Par exemple, pour obtenir les 6 derniers caractères de la fin d'une chaîne, utilisez cette formule:

=DROITE(A2; 6)

 

Extraire le texte du milieu de la chaîne (STXT)

Si vous cherchez à extraire une sous-chaîne qui se trouve au milieu d'une chaîne, à la position que vous spécifiez, alors STXT est la fonction sur laquelle vous pouvez compter.

Par rapport aux deux autres fonctions précédentes, STXT a une syntaxe légèrement différente:

STXT(texte; no_départ; no_car)

Outre le texte (la chaîne de texte d'origine) et no_car (le nombre de caractères à extraire), vous indiquez également no_départ (le point de départ).

Dans cet exemple de jeu de données, pour obtenir quatre caractères à partir du milieu d'une chaîne commençant par le 5e caractère, vous utilisez la formule suivante:

=STXT(A2; 5; 4)

Extraire la sous-chaîne avant ou après un caractère donné

Comme le montrent les exemples ci-dessus, les fonctions Gauche, Droite et STXT s'adaptent bien aux chaînes uniformes. Lorsque vous travaillez avec des chaînes de texte de longueur variable, des manipulations plus complexes seront nécessaires.

Remarque : dans tous les exemples ci-dessous, j’utiliserai la fonction CHERCHE insensible à la casse pour obtenir la position d'un caractère. Si vous souhaitez une formule sensible à la casse, utilisez plutôt la fonction TROUVE.

Comment extraire du texte avant un caractère spécifique ?

Pour obtenir une sous-chaîne précédant un caractère donné, deux choses doivent être faites ; D'abord, vous déterminez la position du caractère d'intérêt, puis vous tirez tous les caractères devant. Plus précisément, vous utilisez la fonction CHERCHE pour trouver la position du caractère et soustrayez 1 du résultat, car vous ne voulez pas inclure le caractère lui-même dans la sortie. Et puis, vous envoyez le numéro retourné directement à l'argument no_car de la fonction GAUCHE:

GAUCHE(cellule; RECHERCHE("car"; cellule) -1)

Par exemple, pour extraire une sous-chaîne avant le trait d'union (-) de la cellule A2, utilisez cette formule:

=GAUCHE(A2; CHERCHE("-"; A2) -1)

Quel que soit le nombre de caractères contenus dans votre chaîne Excel, la formule extrait uniquement le texte avant le premier tiret:

Comment extraire du texte après un caractère ?

Pour obtenir du texte après un caractère spécifique, vous utilisez une approche légèrement différente ; Obtenez la position du caractère avec CHERCHE ou TROUVE, soustrayez ce nombre de la longueur totale de la chaîne renvoyée par la fonction NBCAR et extrayez autant de caractères de la fin du chaîne.

DROITE(cellule; NBCAR(cellule) - CHERCHE("car"; cellule))

Dans cet exemple, je vais utiliser la formule suivante pour extraire une sous-chaîne après le premier tiret:

=DROITE(A2; NBCAR(A2) -CHERCHE("-"; A2))

 

Comment extraire du texte entre deux instances d'un caractère ?

Pour obtenir une sous-chaîne entre deux occurrences d'un certain caractère, utilisez la formule générique suivante:

STXT(cellule; CHERCHE("car"; cellule) +1; CHERCHE("car"; cellule; CHERCHE("car" ; cellule) +1) - CHERCHE("car"; cellule) -1)

Les deux premiers arguments de cette formule STXT sont limpides:



  • ·Le texte est la cellule contenant la chaîne de texte d'origine.
  • ·No_départ (point de départ) - une simple formule de CHERCHE renvoie la position du caractère souhaité, à laquelle vous ajoutez 1 car vous voulez démarrer l'extraction avec le caractère suivant.
  • ·No_car (nombre de caractères à extraire) est la partie la plus délicate ; Tout d'abord, vous déterminez la position de la deuxième occurrence du caractère en imbriquant une fonction de recherche dans une autre. Après cela, vous soustrayez la position de la 1ère occurrence de la position de la 2ème occurrence, et soustrayez 1 du résultat car vous ne voulez pas inclure le caractère délimiteur dans la sous-chaîne résultante.

Par exemple, pour extraire du texte entouré de deux tirets, vous utiliseriez cette formule:

=STXT(A2; CHERCHE("-"; A2) + 1; CHERCHE("-"; A2; CHERCHE("-"; A2) +1) - CHERCHE("-"; A2) - 1)

La capture d'écran ci-dessous montre le résultat:

Si vous cherchez à extraire du texte entre les 2e et 3e ou 3e et 4e occurrences du même caractère, vous pouvez utiliser une combinaison de CHERCHE SUBSTITUE plus compacte pour obtenir la position du caractère:

TROUVE(CAR(1); SUBSTITUE(cellule; caractère; CAR(1); Nième occurrence))

Dans ce cas, nous pourrions extraire une sous-chaîne entre les 1e et 2e tirets avec la formule suivante:

=STXT(A2; TROUVE(CAR(1); SUBSTITUE(A2; "-"; CAR(1); 1)) + 1; TROUVE(CAR(1); SUBSTITUE(A2; "-"; CAR(1); 2)) - TROUVE(CAR(1); SUBSTITUE(A2; "-"; CAR(1); 1)) - 1)

Exemple 1 - Extraire le nom d'utilisateur à partir d'ID de messagerie

Lors de l'utilisation des fonctions de texte, il est important d'identifier un modèle (le cas échéant). Cela facilite vraiment la construction d'une formule. Dans le cas ci-dessous, le modèle est le signe @ entre le nom d'utilisateur et le nom de domaine, et je vais l’utiliser comme référence pour obtenir les noms d'utilisateur.

Voici la formule pour obtenir le nom d'utilisateur :

=GAUCHE(A2; TROUVE("@"; A2) -1)

La formule ci-dessus utilise la fonction GAUCHE pour extraire le nom d'utilisateur en identifiant la position du signe @ dans l'id. Cela se fait à l'aide de la fonction TROUVE, qui renvoie la position du @.

Par exemple, dans le cas de [email protected], TROUVE("@"; A2) renverrait 11, ce qui correspond à sa position dans la chaîne de texte. Nous utilisons maintenant la fonction GAUCHE pour extraire 10 caractères à gauche de la chaîne (un de moins que la valeur renvoyée par la fonction TROUVE).

Exemple 2 - Extraire le nom de domaine à partir d'ID de messagerie

La même logique utilisée dans l'exemple ci-dessus peut être utilisée pour obtenir le nom de domaine. Une différence mineure ici est que nous devons extraire les caractères à droite de la chaîne de texte.

Voici la formule qui fera ce travail :

=DROITE(A2; NBCAR(A2) -TROUVE("@"; A2))

Dans la formule ci-dessus, utilisez la même logique, mais ajustez-la pour vous assurer d'obtenir la bonne chaîne.

Reprenons l'exemple de [email protected] La fonction TROUVE renvoie la position du signe @, qui est 11 dans ce cas. Maintenant, nous devons extraire tous les caractères après le @. Nous identifions donc la longueur totale de la chaîne et soustrayons le nombre de caractères jusqu'au @. Il nous donne le nombre de caractères qui couvrent le nom de domaine à droite.

Maintenant, nous pouvons simplement utiliser la fonction DROITE pour obtenir le nom de domaine.

Exemple 3 - Extraire le nom de domaine à partir d'ID de messagerie (sans .com)

Pour extraire une sous-chaîne du milieu d'une chaîne de texte, vous devez identifier la position du marqueur juste avant et après la sous-chaîne.

Par exemple, dans l'exemple ci-dessous, pour obtenir le nom de domaine sans la partie .com, le marqueur serait @ (qui est juste avant le nom de domaine).

Voici la formule qui extrait uniquement le nom de domaine :

=STXT(A2; TROUVE("@"; A2) + 1; TROUVE("."; A2) - TROUVE("@"; A2) -1)

La fonction Excel STXT extrait le nombre de caractères spécifié de la position de départ spécifiée. Dans cet exemple ci-dessus, TROUVE("@"; A2) + 1  spécifie la position de départ (juste après le @) et TROUVE("."; A2) - TROUVE("@"; A2) -1  identifie le nombre de caractères entre le '@' et le '.'

Utilisation de la fonctionnalité « Convertir » pour extraire une sous-chaîne dans Excel

L'utilisation de fonctions pour extraire une sous-chaîne dans Excel présente l'avantage d'être dynamique. Si vous modifiez le texte d'origine, la formule mettra automatiquement à jour les résultats.



Si c'est quelque chose dont vous n'avez peut-être pas besoin, l'utilisation de la fonction « Convertir » peut être un moyen rapide et facile de diviser le texte en sous-chaînes en fonction des marqueurs spécifiés.

Voici comment procéder :

  1. Sélectionnez les cellules où vous avez le texte.
  2. Allez dans Données -> Outils de données -> Convertir.
  3. Dans l'étape 1 de l'Assistant Conversion, sélectionnez « Délimité » et appuyez sur « Suivant ».
  4. À l'étape 2, cochez l'option « Autre » et entrez @ dans la case à droite. Ce sera notre délimiteur qu'Excel utilisera pour diviser le texte en sous-chaînes. Cliquez ensuite sur Suivant. Vous pouvez voir l'aperçu des données ci-dessous.
  5. À l'étape 3, le paramètre standard fonctionne correctement dans ce cas. Vous pouvez cependant choisir un format différent si vous divisez des nombres/dates. Par défaut, la cellule de destination est l'endroit où vous avez les données d'origine. Si vous souhaitez conserver les données d'origine intactes, remplacez-les par une autre cellule.
  6. Cliquez sur Terminer.

Cela vous donnera instantanément deux ensembles de sous-chaînes pour l’identifiant de messagerie utilisé dans cet exemple.

Si vous souhaitez diviser davantage le texte (par exemple, diviser domaine.com en domaine et com), répétez le même processus avec la cellule C2.

Utilisation de RECHERCHER et REMPLACER pour extraire du texte d'une cellule dans Excel

RECHERCHER et REMPLACER peut être une technique puissante lorsque vous travaillez avec du texte dans Excel. Dans les exemples ci-dessous, vous apprendrez à utiliser cette fonctionnalité avec des caractères génériques pour faire des choses incroyables dans Excel.

Prenons les mêmes exemples d'ID de messagerie.

Exemple 1 - Extraire le nom d'utilisateur à partir d'ID de messagerie

Voici les étapes pour extraire les noms d'utilisateur à partir des ID de messagerie à l'aide de la fonctionnalité Rechercher et remplacer :

  1. Copiez et collez les données d'origine. Étant donné que Rechercher et remplacer fonctionnent et modifient les données sur lesquelles elles sont appliquées, il est préférable d'avoir une sauvegarde des données d'origine.
  2. Sélectionnez les données et accédez à Accueil -> Groupe Édition  -> Rechercher et sélectionner et choisissez l’option Remplacer (ou utilisez le raccourci clavier Ctrl+H).
  3. Dans la boîte de dialogue Rechercher et remplacer, entrez les informations suivantes:

¾     Rechercher : @*

¾     Remplacez par : laissez ce champ vide

  1. Cliquez sur Remplacer tout.

Cela supprimera instantanément tout le texte après le @ dans les identifiants de messagerie. Vous obtiendrez le résultat comme indiqué ci-dessous:

Comment ça marche ? - Dans l'exemple ci-dessus, j’ai utilisé une combinaison de @ et *. Un astérisque (*) est un caractère générique qui représente n'importe quel nombre de caractères. Par conséquent, @* signifierait une chaîne de texte qui commence par @ et peut avoir n'importe quel nombre de caractères après. Par exemple, dans [email protected], @* c’est @domaine.com. Lorsque nous remplaçons @* par un blanc, il supprime tous les caractères après @ (y compris @).

Exemple 2 - Extraire le nom de domaine à partir d'ID de messagerie

En utilisant la même logique, vous pouvez modifier les critères "Rechercher " pour obtenir le nom de domaine. Voici les étapes:

  1. Sélectionnez les données
  2. Allez dans Accueil -> Édition -> Rechercher et sélectionner -> Remplacer
  3. Dans la boîte de dialogue Rechercher et remplacer, entrez les informations suivantes:
  • Rechercher: *@
  • Remplacez par: (laissez ce champ vide)
  1. Cliquez sur Remplacer tout

Cela supprimera instantanément tout le texte avant le @ dans les identifiants de messagerie. Vous obtiendrez le résultat comme indiqué ci-dessous:

 

Comment trouver une sous-chaîne dans Excel ?

Dans les situations où vous ne voulez pas extraire une sous-chaîne et souhaitez uniquement trouver les cellules qui la contiennent, vous utilisez la fonction CHERCHE ou TROUVE, mais effectuez la recherche dans la fonction ESTNUM. Si une cellule contient la sous-chaîne, la fonction de recherche renvoie la position du premier caractère, et tant que ESTNUM obtient un nombre, elle renvoie VRAI. Si la sous-chaîne n'est pas trouvée, la recherche entraîne une erreur, forçant ESTNUM à renvoyer FAUX.



ESTNUM(CHERCHE("sous-chaîne";cellule))

Supposons que vous ayez une liste de codes produits dans la colonne A et que vous souhaitiez trouver ceux qui contiennent la sous-chaîne "A2X". Pour le faire, utilisez cette formule :

=ESTNUM(CHERCHE("A2X";A2))

Les résultats ressembleront à ceci :

Comme vous vous en souvenez (peut-être), la fonction Excel CHERCHE ne respecte pas la casse. Vous l'utilisez donc lorsque la casse des caractères n'a pas d'importance. Pour obtenir une formule qui distingue les caractères majuscules et minuscules, optez pour la fonction TROUVE sensible à la casse.

Télécharger le fichier Excel de ce tutoriel