Excel a un ensemble de fonctions TEXTE qui peuvent faire des merveilles. Vous pouvez effectuer toutes sortes d'opérations de découpage de texte et de dés en utilisant ces fonctions.
L'une des tâches courantes pour les personnes travaillant avec des données textuelles consiste à extraire une sous-chaîne dans Excel (c'est-à-dire obtenir psrt du texte à partir d'une cellule).
Malheureusement, il n'y a pas de fonction de sous-chaîne dans Excel qui puisse le faire facilement. Cependant, cela peut toujours être fait en utilisant des formules de texte ainsi que d'autres fonctionnalités Excel intégrées.
Jetons d'abord un coup d'œil à certaines des fonctions de texte que nous utiliserons dans ce didacticiel.
Fonctions de texte Excel
Excel propose une gamme de fonctions de texte qui facilitent l'extraction d'une sous-chaîne du texte d'origine dans Excel. Voici les fonctions Excel Text que nous utiliserons dans ce tutoriel :
- Fonction RIGHT : extrait les nombres de caractères spécifiés à droite de la chaîne de texte.
- Fonction LEFT : extrait les nombres de caractères spécifiés à partir de la gauche de la chaîne de texte.
- Fonction MID : extrait les nombres de caractères spécifiés à partir de la position de départ spécifiée dans une chaîne de texte.
- Fonction FIND : Recherche la position de départ du texte spécifié dans la chaîne de texte.
- Fonction LEN : Renvoie le nombre de caractères dans la chaîne de texte.
Extraire une sous-chaîne dans Excel à l'aide de fonctions
Supposons que vous ayez un ensemble de données comme indiqué ci-dessous :
Ce sont des identifiants de messagerie aléatoires (mais super-héros) (sauf le mien), et dans les exemples ci-dessous, je vais vous montrer comment extraire le nom d'utilisateur et le nom de domaine à l'aide des fonctions de texte dans Excel.
Exemple 1 - Extraction des noms d'utilisateur à partir des identifiants de messagerie
Lors de l'utilisation des fonctions de texte, il est important d'identifier un modèle (le cas échéant). Cela rend très facile la construction d'une formule. Dans le cas ci-dessus, le motif est le signe @ entre le nom d'utilisateur et le nom de domaine, et nous l'utiliserons comme référence pour obtenir les noms d'utilisateur.
Voici la formule pour obtenir le nom d'utilisateur :
=GAUCHE(A2,TROUVER("@",A2)-1)
La formule ci-dessus utilise la fonction GAUCHE pour extraire le nom d'utilisateur en identifiant la position du signe @ dans l'identifiant. Cela se fait à l'aide de la fonction FIND, qui renvoie la position du @.
Par exemple, dans le cas de [email protected], FIND(“@”,A2) renverrait 11, qui est sa position dans la chaîne de texte.
Maintenant, nous utilisons la fonction LEFT pour extraire 10 caractères de la gauche de la chaîne (un de moins que la valeur renvoyée par la fonction LEFT).
Exemple 2 - Extraction du nom de domaine à partir des identifiants 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 cela :
=DROITE(A2,LEN(A2)-TROUVER("@",A2))
Dans la formule ci-dessus, nous utilisons la même logique, mais l'ajustons pour nous assurer que nous obtenons la bonne chaîne.
Reprenons l'exemple de [email protected]. La fonction FIND 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 recouvrent le nom de domaine à droite.
Maintenant, nous pouvons simplement utiliser la fonction DROITE pour obtenir le nom de domaine.
Exemple 3 - Extraction du nom de domaine à partir des identifiants 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 se trouve juste avant le nom de domaine) et . (qui est juste après).
Voici la formule qui va extraire le nom de domaine uniquement :
=MID(A2,TROUVE("@",A2)+1,TROUVE(".",A2)-TROUVE("@",A2)-1)
La fonction Excel MID extrait le nombre spécifié de caractères à partir de la position de départ spécifiée. Dans cet exemple ci-dessus, FIND(“@”,A2)+1 spécifie la position de départ (qui est juste après le @), et FIND(“.”,A2)-FIND(“@”,A2)-1 identifie le nombre de caractères entre le '@' et le '.‘
Mettre à jour: L'un des lecteurs William19 a mentionné que la formule ci-dessus ne fonctionnerait pas au cas où il y aurait un point (.) Dans l'identifiant de l'e-mail (par exemple, [email protected]). Voici donc la formule pour traiter de tels cas :
=MID(A1,TROUVE("@",A1)+1,TROUVE(".",A1,TROUVE("@",A1))-TROUVE("@",A1)-1)
Utilisation de texte en colonnes pour extraire une sous-chaîne dans Excel
L'utilisation de fonctions pour extraire une sous-chaîne dans Excel a l'avantage d'être dynamique. Si vous modifiez le texte d'origine, la formule mettra automatiquement à jour les résultats.
S'il s'agit de quelque chose dont vous n'avez peut-être pas besoin, l'utilisation de la fonction Texte en colonnes peut être un moyen rapide et facile de diviser le texte en sous-chaînes en fonction de marqueurs spécifiés.
Voici comment procéder :
- Sélectionnez les cellules où vous avez le texte.
- Accédez à Données -> Outils de données -> Texte vers colonnes.
- Dans l'étape 1 de l'assistant Texte en colonne, sélectionnez Délimité et appuyez sur Suivant.
- À l'étape 2, cochez l'option Autre et entrez @ dans la case correspondante. Ce sera notre délimiteur qu'Excel utiliserait pour diviser le texte en sous-chaînes. Vous pouvez voir l'aperçu des données ci-dessous. Cliquez sur Suivant.
- À l'étape 3, les paramètres généraux fonctionnent 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.
- Cliquez sur Terminer.
Cela vous donnera instantanément deux ensembles de sous-chaînes pour chaque identifiant d'e-mail utilisé dans cet exemple.
Si vous souhaitez diviser davantage le texte (par exemple, divisez batman.com en batman et com), répétez le même processus avec lui.
Utilisation de FIND et REPLACE pour extraire le texte d'une cellule dans Excel
TROUVER et REMPLACER peuvent être une technique puissante lorsque vous travaillez avec du texte dans Excel. Dans les exemples ci-dessous, vous apprendrez à utiliser FIND et REPLACE avec des caractères génériques pour faire des choses incroyables dans Excel.
Voir également: Tout savoir sur les caractères génériques dans Excel.
Prenons les mêmes exemples d'identifiants de messagerie.
Exemple 1 - Extraction des noms d'utilisateur à partir des identifiants de messagerie
Voici les étapes pour extraire les noms d'utilisateur des identifiants de messagerie à l'aide de la fonctionnalité Rechercher et remplacer :
- Copiez et collez les données d'origine. Étant donné que Rechercher et remplacer fonctionne et modifie les données sur lesquelles il est appliqué, il est préférable d'avoir une sauvegarde des données d'origine.
- Sélectionnez les données et accédez à Accueil -> Édition -> Rechercher et sélectionner -> Remplacer (ou utilisez le raccourci clavier Ctrl + H).
- Dans la boîte de dialogue Rechercher et remplacer, saisissez ce qui suit :
- Trouver quoi: @*
- Remplacer par : (laisser ce champ vide)
- Cliquez sur Remplacer tout.
Cela supprimera instantanément tout le texte avant le @ dans les identifiants de messagerie. Vous aurez le résultat comme indiqué ci-dessous :
Comment ça marche ?? - Dans l'exemple ci-dessus, nous avons utilisé une combinaison de @ et *. Un astérisque (*) est un caractère générique qui représente un nombre quelconque de caractères. Par conséquent, @* signifierait une chaîne de texte commençant par @ et pouvant avoir n'importe quel nombre de caractères après. Par exemple, dans [email protected], @* serait @batman.com. Lorsque nous remplaçons @* par un espace, cela supprime tous les caractères après @ (y compris @).
Exemple 2 - Extraction du nom de domaine à partir des identifiants de messagerie
En utilisant la même logique, vous pouvez modifier les critères « Trouver quoi » pour obtenir le nom de domaine.
Voici les étapes :
- Sélectionnez les données.
- Allez dans Accueil -> Édition -> Rechercher et sélectionner -> Remplacer (ou utilisez le raccourci clavier Ctrl + H).
- Dans la boîte de dialogue Rechercher et remplacer, saisissez ce qui suit :
- Trouver quoi: *@
- Remplacer par : (laisser ce champ vide)
- Cliquez sur Remplacer tout.
Cela supprimera instantanément tout le texte avant le @ dans les identifiants de messagerie. Vous aurez le résultat comme indiqué ci-dessous :
