Tutoriel Excel – extraire du texte ou des nombres selon une position spécifique
Dans de nombreux cas, vous pourriez avoir besoin d'extraire uniquement le contenu utile d'une phrase ou d'une chaîne de texte dans une cellule, comme extraire la province d'une adresse, extraire l'adresse e-mail d'une phrase, extraire le numéro de suivi d'une conversation, et ainsi de suite. Ce tutoriel se concentre sur l'extraction à une position spécifique dans une cellule et rassemble différentes méthodes pour aider à extraire du texte ou des nombres d'une cellule selon une position précise dans Excel.
Table des matières : [ Masquer ]
Extraire le texte par position
Cette section regroupe les positions courantes à partir desquelles un texte peut être extrait d'une cellule et fournit des méthodes correspondantes pour les traiter étape par étape. Vous pouvez parcourir pour plus de détails.
1. Extraire le nombre de caractères depuis la gauche ou la droite
Pour extraire le nombre de caractères depuis la gauche ou la droite d'une chaîne, vous pouvez essayer l'une des méthodes ci-dessous.
1.1 Extraire les premiers ou derniers N caractères avec des formules
Supposons que vous ayez une liste de chaînes de texte dans la colonne B comme le montre la capture d'écran ci-dessous, pour extraire les deux premiers caractères et les deux derniers caractères de chaque chaîne, vous pouvez appliquer les formules suivantes.
Extraire les N premiers caractères d'une chaîne de texte
La fonction GAUCHE peut aider à extraire facilement les N premiers caractères d'une chaîne de texte dans Excel.
Formule générique
=GAUCHE(chaine_texte,[nb_caracteres])
Arguments
Vous pouvez maintenant appliquer cette formule pour extraire les deux premiers caractères des cellules de la colonne B.
1. Sélectionnez une cellule vide, copiez ou entrez la formule ci-dessous et appuyez sur la touche "Entrée" pour obtenir le premier résultat. Sélectionnez la cellule de résultat et faites glisser son poignée de recopie automatique vers le bas pour appliquer la formule à d'autres cellules.
=LEFT(B5,2)
Vous avez maintenant extrait les deux premiers caractères de chaque cellule de la plage donnée.
Extraire les N derniers caractères d'une chaîne de texte
Ici, nous appliquons la fonction DROITE pour extraire les N derniers caractères d'une chaîne de texte dans Excel.
Formule générique
=DROITE(chaine_texte,[nb_caracteres])
Arguments
Sélectionnez une cellule vide, copiez ou entrez la formule ci-dessous et appuyez sur la touche "Entrée" pour obtenir le résultat. Ensuite, sélectionnez cette cellule de résultat et faites glisser sa poignée de recopie automatique vers le bas pour obtenir d'autres résultats.
=RIGHT(B5,2)
1.2 Extraire les premiers ou derniers N caractères avec un outil incroyable
Bien que les formules ci-dessus soient simples, pour extraire les premiers ou derniers n caractères d'une longue liste de chaînes de texte, vous devez tout de même faire glisser la poignée de remplissage automatique de haut en bas, ce qui peut être un peu chronophage. Ici, nous recommandons l’utilitaire "Extraction de texte" de "Kutools pour Excel" pour aider à extraire en bloc les premiers ou derniers N caractères d’une liste de chaînes de texte.
1. Sélectionnez à l'avance la liste des chaînes de texte à partir de laquelle vous souhaitez extraire du texte, puis cliquez sur "Kutools" > "Texte" > "Extraire le texte".
2. Dans la boîte de dialogue "Extraire le texte" qui apparaît, vous devez configurer comme suit.
Remarque : Pour rendre le résultat dynamique lorsque la chaîne de texte change, vous pouvez cocher la case "Insérer en tant que formule".
3. Dans la boîte de dialogue "Extraire le texte" qui apparaît ensuite, choisissez une cellule pour afficher les caractères extraits, puis cliquez sur "OK".
Ensuite, les N premiers ou N derniers caractères sont extraits en bloc des cellules sélectionnées.
Cliquez pour en savoir plus sur cette fonctionnalité.
Kutools pour Excel - Boostez Excel avec plus de 300 outils essentiels. Profitez de fonctionnalités IA gratuites en permanence ! Obtenez-le maintenant
2. Extraire le texte avant ou après un certain caractère/mot
Pour extraire du texte avant ou après un certain caractère ou mot, les différents scénarios de cette section répondront à vos besoins.
2.1 Extraire le texte avant ou après le premier délimiteur (un caractère)
Comme le montre la capture d'écran ci-dessous, pour extraire le texte avant ou après le premier délimiteur de chaque cellule dans la plage B4:B10, vous pouvez appliquer l'une des méthodes ci-dessous.
2.1.1 Extraire le texte avant le premier délimiteur avec une formule
Appliquer une formule basée sur les fonctions GAUCHE et TROUVER peut aider à extraire le texte avant le premier délimiteur d'une cellule. Vous pouvez suivre les étapes ci-dessous pour y parvenir.
Formule générique
=GAUCHE(chaine_texte,TROUVE("délimiteur",chaine_texte,1)-1)
Arguments
Sélectionnez une cellule vide, copiez ou entrez la formule ci-dessous dedans et appuyez sur la touche "Entrée" pour obtenir le premier résultat. Sélectionnez la première cellule de résultat et faites glisser son poignée de recopie automatique vers le bas pour obtenir les textes avant le premier délimiteur des autres cellules.
=LEFT(B5,FIND("-",B5,1)-1)
2.1.2 Extraire le texte après le premier délimiteur avec une formule
La formule ci-dessous permet d'extraire le texte après le premier délimiteur d'une cellule dans Excel.
Formule générique
=STXT(chaine_texte,TROUVE("délimiteur",chaine_texte)+1,NB.CAR(chaine_texte))
Arguments
=MID(B5,FIND("-",B5)+1,LEN(B5))
2.1.3 Extraire le texte avant ou après le premier délimiteur avec un outil incroyable
Ici, nous recommandons vivement l'utilitaire "Extraire le texte" de "Kutools pour Excel". Avec cette fonctionnalité, vous pouvez facilement extraire des textes avant ou après le premier délimiteur à partir d'une plage de cellules en bloc.
1. Sélectionnez la plage de cellules à partir de laquelle vous souhaitez extraire le texte, puis cliquez sur "Kutools" > "Texte" > "Extraire le texte".
2. Dans la boîte de dialogue « Extraire le texte », vous devez configurer comme suit.
Remarque : Pour rendre le résultat dynamique lorsque la chaîne de texte change, vous pouvez cocher la case Insérer en tant que formule.
3. Ensuite, une autre boîte de dialogue "Extraire le texte" apparaît, choisissez une cellule pour afficher les résultats et cliquez sur "OK".
Ensuite, les textes avant ou après le premier délimiteur sont extraits des cellules sélectionnées en une seule fois.
Pour en savoir plus sur cette fonctionnalité, veuillez visiter : Extraire Rapidement un Texte Spécifique des Cellules dans Excel.
Kutools pour Excel - Boostez Excel avec plus de 300 outils essentiels. Profitez de fonctionnalités IA gratuites en permanence ! Obtenez-le maintenant
2.2 Extraire le texte avant ou après le dernier délimiteur (un caractère)
Dans les étapes ci-dessus, nous avons appris les méthodes pour extraire le texte avant ou après le premier délimiteur d'une cellule. Comme le montre la capture d'écran ci-dessous, cette section vous montrera deux formules pour extraire le texte avant ou après le dernier délimiteur d'une cellule. Vous pouvez suivre les étapes ci-dessous pour y parvenir.
2.2.1 Extraire le texte avant le dernier délimiteur avec une formule
Pour extraire le texte avant le dernier délimiteur d'une cellule, vous pouvez utiliser les fonctions RECHERCHE, NBCAR et SUBSTITUE à l'intérieur de la fonction GAUCHE.
Formule générique
=GAUCHE(chaine_texte;RECHERCHE("#";SUBSTITUE(chaine_texte;"délimiteur";"#";NBCAR(chaine_texte)-NBCAR(SUBSTITUE(chaine_texte;"délimiteur";""))))-1)
Arguments
Sélectionnez une cellule, entrez la formule ci-dessous et appuyez sur la touche "Entrée" pour obtenir le résultat. Sélectionnez cette cellule de résultat et faites glisser sa poignée de recopie automatique vers le bas pour extraire les textes d'autres chaînes de texte dans la même colonne.
=LEFT(B5,SEARCH("#",SUBSTITUTE(B5,"-","#",LEN(B5)-LEN(SUBSTITUTE(B5,"-",""))))-1)
2.2.2 Extraire le texte après le dernier délimiteur avec une formule
Après avoir extrait le texte avant le dernier délimiteur d'une cellule, vous pouvez appliquer la formule ci-dessous pour extraire le texte après le dernier délimiteur selon vos besoins.
Formule générique
=DROITE(chaîne_texte,NB.CAR(chaîne_texte)-RECHERCHE("#",SUBSTITUE(chaîne_texte,"délimiteur","#",NB.CAR(chaîne_texte)-NB.CAR(SUBSTITUE(chaîne_texte,"délimiteur","")))))
Arguments
Sélectionnez une cellule, entrez la formule ci-dessous et appuyez sur la touche "Entrée" pour obtenir le résultat. Sélectionnez cette cellule de résultat et faites glisser sa poignée de recopie automatique vers le bas pour extraire les textes d'autres chaînes de texte dans la même colonne.
=RIGHT(B5,LEN(B5)-SEARCH("#",SUBSTITUTE(B5,"-","#",LEN(B5)-LEN(SUBSTITUTE(B5,"-","")))))
2.3 Extraire le texte après le n-ième caractère
Regardez l'exemple ci-dessous, il y a une liste de chaînes de texte dans la plage B4:B10. Pour extraire le texte après le troisième caractère de chaque cellule, vous pouvez appliquer une formule basée sur les fonctions MID et LEN.
Formule générique
=STXT(chaine_texte,n_char+1,NBCAR(chaine_texte))
Arguments
Sélectionnez une cellule vide, copiez ou entrez la formule ci-dessous dans celle-ci et appuyez sur la touche "Entrée" pour obtenir le résultat. Sélectionnez cette cellule de résultat et faites glisser sa poignée de recopie automatique vers le bas pour obtenir d'autres résultats.
=MID(B5,3+1,LEN(B5))
2.4 Extraire le N-ième mot d'une chaîne de texte
Supposons que vous ayez une liste de chaînes de texte comme le montre la capture d'écran ci-dessous, et que vous souhaitiez extraire uniquement le N-ième mot de la chaîne de texte. Cette section propose trois méthodes pour vous aider à accomplir cette tâche.
2.4.1 Extraire le N-ième mot avec une formule
Vous pouvez combiner les fonctions TRIM, MID, SUBSTITUTE, REPT et LEN pour extraire le N-ième mot d'une chaîne de texte dans une cellule.
Formule générique
=TRIM(MILIEU(SUBSTITUE(text_string," ",REPT(" ",NBCAR((text_string))), (N-1)*NBCAR((text_string)+1, NBCAR((text_string)))
Arguments
Dans ce cas, la plage B5:B10 contient les chaînes de texte, D5:D10 contient les nombres représentant le N-ième mot. Appliquons cette formule pour extraire le N-ième mot de la chaîne de texte.
Sélectionnez une cellule vide, copiez ou entrez la formule ci-dessous dans celle-ci et appuyez sur la touche "Entrée" pour obtenir le premier résultat. Sélectionnez cette cellule de résultat et faites glisser son Poignée de Recopie automatique vers le bas pour obtenir le n-ième mot des autres cellules.
=TRIM(MID(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))), (D5-1)*LEN(B5)+1, LEN(B5)))
Remarque : Vous pouvez directement saisir le nombre n-ième dans la formule comme suit.
=TRIM(MID(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))), (2-1)*LEN(B5)+1, LEN(B5)))2.4.2 Extraire le N-ième mot avec une fonction définie par l'utilisateur
Outre la formule ci-dessus, vous pouvez également appliquer une fonction définie par l'utilisateur pour extraire le n-ième mot d'une cellule dans Excel.
1. Appuyez sur les touches "Alt" + "F11" pour ouvrir la fenêtre "Microsoft Visual Basic for Applications".
2. Dans la fenêtre "Microsoft Visual Basic for Applications", cliquez sur "Insertion" > "Module", puis copiez le code VBA ci-dessous dans la fenêtre de code.
Code VBA : Extraire le N-ième mot d'une chaîne de texte dans une cellule
Function ExtractTheNthWord(Source As String, Position As Integer)
'Update by Extendoffice 20211202
Dim arr() As String
arr = VBA.Split(Source, " ")
xCount = UBound(arr)
If xCount < 1 Or (Position - 1) > xCount Or Position < 0 Then
FindWord = ""
Else
FindWord = arr(Position - 1)
End If
End Function
3. Appuyez sur les touches "Alt" + "Q" pour fermer la fenêtre "Microsoft Visual Basic for Applications".
4. Revenez à la feuille de calcul contenant les chaînes de texte à partir desquelles vous souhaitez extraire le N-ième mot. Sélectionnez une cellule vide, copiez ou entrez la formule ci-dessous dans celle-ci et appuyez sur la touche "Entrée" pour obtenir le N-ième mot.
=FindWord(B5,D5)Ou
=FindWord(B5,2)Remarque : dans la formule, D5 est la cellule qui contient un nombre représentant le N-ième mot. Vous pouvez également remplacer directement la référence de cellule par un nombre.
5. Sélectionnez la cellule de résultat et faites glisser sa poignée de recopie automatique vers le bas pour extraire le n-ième mot des chaînes de texte d'autres cellules.
2.4.3 Extraire le n-ième mot avec un outil incroyable
Si vous ne souhaitez pas appliquer manuellement une formule ou une fonction définie par l'utilisateur mentionnée ci-dessus, nous vous recommandons l'utilitaire "Extraire le N-ième mot d'une cellule" de Kutools pour Excel. Grâce à cette fonctionnalité, vous pouvez facilement extraire le N-ième mot d'une chaîne de texte dans une cellule en quelques clics seulement.
1. Sélectionnez une cellule pour placer le résultat, puis cliquez sur "Kutools" > "Assistant de formule" > "Texte" > "Extraire" "le N-ième mot d'une cellule". Voir la capture d'écran :
2. Dans la boîte de dialogue « Assistant de formules », vous devez configurer comme suit.
3. Ensuite, le N-ième (deuxième) mot est extrait de la chaîne de texte dans la cellule B5, et vous pouvez voir qu'une formule est créée en même temps. Sélectionnez cette cellule de résultat et faites glisser sa poignée de recopie automatique vers le bas pour obtenir le N-ième mot à partir d'autres chaînes de texte.
Cliquez pour en savoir plus sur cette fonctionnalité.
Kutools pour Excel - Boostez Excel avec plus de 300 outils essentiels. Profitez de fonctionnalités IA gratuites en permanence ! Obtenez-le maintenant
2.5 Extraire le texte avant ou après la n-ième occurrence d'un délimiteur
Supposons que vous ayez une liste de chaînes de texte comme le montre la capture d'écran ci-dessous. Pour extraire le texte avant ou après la deuxième occurrence d'un espace, cette section propose deux formules pour vous aider à accomplir cette tâche.
2.5.1 Extraire le texte avant la n-ième occurrence d'un délimiteur
Vous pouvez utiliser la fonction GAUCHE avec les fonctions SUBSTITUE et TROUVE pour extraire le texte avant la n-ième occurrence d'un délimiteur à partir d'une cellule dans Excel.
Formule générique
=GAUCHE(SUBSTITUE(texte_chaîne,"délimiteur",CAR(9),n),TROUVE(CAR(9),SUBSTITUE(texte_chaîne,"délimiteur",CAR(9),n),1)-1)
Arguments
Sélectionnez une cellule, copiez ou entrez la formule ci-dessous et appuyez sur la touche "Entrée" pour obtenir le résultat. Sélectionnez cette cellule de résultat et faites glisser la poignée de recopie automatique vers le bas pour obtenir d'autres résultats dans la liste.
=LEFT(SUBSTITUTE(B5," ",CHAR(9),2),FIND(CHAR(9),SUBSTITUTE(B5," ",CHAR(9),2),1)-1)
Remarque : Dans la formule, B5 est la cellule contenant la chaîne de texte à partir de laquelle vous souhaitez extraire le texte ; « » représente ici un espace et le chiffre 2 indique la deuxième occurrence d'un espace. Vous pouvez les modifier selon vos besoins.
2.5.2 Extraire le texte après la n-ième occurrence d'un délimiteur
Pour extraire le texte après la n-ième occurrence d'un délimiteur, vous pouvez appliquer la fonction DROITE avec les fonctions SUBSTITUE, NBCAR et TROUVE.
Formule générique
=DROITE(SUBSTITUE(chaine_texte, "délimiteur", CAR(9), n), NBCAR(chaine_texte) - TROUVE(CAR(9), SUBSTITUE(chaine_texte, "délimiteur", CAR(9), n), 1) + 1)
Arguments
Vous pouvez maintenant appliquer cette formule pour extraire le texte après la deuxième occurrence de l'espace dans chaque cellule de la plage B5:B10 comme suit.
Sélectionnez une cellule, entrez la formule ci-dessous et appuyez sur la touche "Entrée" pour obtenir le résultat. Sélectionnez cette cellule de résultat et faites glisser la poignée de recopie automatique vers le bas pour obtenir les autres résultats.
=RIGHT(SUBSTITUTE(B5, " ", CHAR(9), 2), LEN(B5)- FIND(CHAR(9), SUBSTITUTE(B5, " ", CHAR(9), 2), 1) + 1)
2.6 Extraire le texte avant ou après un saut de ligne
Supposons que vous ayez une liste de commandes dans la colonne B et que vous souhaitiez extraire uniquement la partie date et la partie numéro de produit de chaque cellule. Vous pouvez utiliser les formules Excel ci-dessous pour y parvenir.
2.6.1 Extraire le texte avant le premier saut de ligne avec une formule
Comme vous pouvez le voir sur la capture d'écran ci-dessus, la partie date se trouve avant le premier saut de ligne dans la cellule. Cette section va démontrer une fonction GAUCHE associée à la fonction RECHERCHE pour vous aider à extraire le texte avant le premier saut de ligne dans une cellule.
Formule générique
=GAUCHE(cellule, RECHERCHE(CAR(10), cellule)-1)
Arguments
Sélectionnez une cellule vide, copiez ou entrez la formule ci-dessous dans celle-ci et appuyez sur la touche "Entrée" pour obtenir le résultat. Sélectionnez cette cellule de résultat, puis faites glisser son poignée de recopie automatique vers le bas pour appliquer cette formule à d'autres cellules.
=LEFT(B5, SEARCH(CHAR(10), B5)-1)Ensuite, vous pouvez voir que le texte avant le premier saut de ligne dans chaque cellule de la plage B5:B8 est extrait, comme le montre la capture d'écran ci-dessous.
Remarque : Dans la formule, CAR(10) représente un saut de ligne sur Windows.
2.6.2 Extraire le texte après la dernière rupture de ligne avec une formule
Dans l'étape précédente, nous avons expliqué comment extraire le texte avant le premier saut de ligne dans une cellule. Cette partie vous guidera sur la manière d'extraire le texte après le dernier saut de ligne dans une cellule en utilisant une formule différente.
Formule générique
=TRIM(DROITE(SUBSTITUE(cellule, CAR(10), REPT(" ", 200)), 200))
Arguments
Sélectionnez une cellule vide, entrez la formule ci-dessous et appuyez sur la touche "Entrée" pour obtenir le résultat. Sélectionnez cette cellule de résultat, puis faites glisser sa poignée de recopie automatique vers le bas pour appliquer la formule à d'autres cellules.
=TRIM(RIGHT(SUBSTITUTE(B5,CHAR(10),REPT(" ",200)),200))
Ensuite, la partie numéro de produit de chaque cellule dans la liste est extraite, comme le montre la capture d'écran ci-dessus.
Remarque : Dans la formule, CAR(10) représente un saut de ligne sous Windows.
2.7 Extraire le texte avant ou après un mot
Dans les parties précédentes, nous avons appris comment extraire du texte avant ou après un caractère ou un délimiteur. Que devez-vous faire pour extraire du texte avant ou après un mot entier ? Cette section présentera trois méthodes pour vous aider à accomplir cette tâche.
2.7.1 Extraire le texte avant un mot spécifique avec une formule
La formule suivante vous aide à extraire le texte avant un certain mot dans une cellule d'Excel.
Formule générique
=SIERREUR(GAUCHE(cellule;TROUVE(mot;cellule)-1);cellule)
Arguments
Sélectionnez une cellule vide, entrez la formule ci-dessous et appuyez sur la touche "Entrée" pour obtenir le résultat. Sélectionnez cette cellule de résultat, puis faites glisser sa poignée de recopie automatique vers le bas pour appliquer cette formule à d'autres cellules.
Dans cet exemple, nous allons extraire tous les textes avant le mot « Excel », donc nous tapons directement le mot dans la formule et l'entourons de guillemets doubles. Ou vous pouvez faire référence à une cellule contenant le mot « Excel ».
=IFERROR(LEFT(B5,FIND("Excel",B5)-1),B5)
Remarques :
2.7.2 Extraire le texte après un mot spécifique avec une formule
Pour extraire le texte après un mot spécifique, vous pouvez appliquer la formule suivante pour y parvenir.
Formule générique
=TRIM(MID(cellule,RECHERCHE(mot,cellule)+NBCAR(mot),255))
Arguments
Sélectionnez une cellule, entrez la formule ci-dessous et appuyez sur la touche "Entrée" pour obtenir le résultat. Sélectionnez la cellule de résultat, puis faites glisser son poignée de recopie automatique vers le bas pour appliquer cette formule à d'autres cellules.
=TRIM(MID(B5,SEARCH("Excel",B5)+LEN("Excel"),255))Ensuite, vous pouvez voir que tous les textes après le mot « Excel » dans chaque cellule sont extraits, comme le montre la capture d'écran ci-dessous.
Remarques :
2.7.3 Extraire le texte avant ou après un mot spécifique avec un outil incroyable
Si vous pensez que l'utilisation de formules peut causer beaucoup d'inconvénients, nous recommandons vivement l'outil "Extraire le texte" de "Kutools pour Excel". Cette fonctionnalité permet d'automatiser la tâche d'extraction dans Excel en quelques clics seulement.
1. Cliquez sur "Kutools" > "Texte" > "Extraire le texte" pour activer cette fonctionnalité.
2. Dans la boîte de dialogue « Extraire le texte », effectuez les réglages suivants.
bouton pour sélectionner la plage de cellules où vous souhaitez extraire des textes ;
Remarque : Si vous souhaitez créer des résultats dynamiques, cochez la case « Insérer en tant que formule ». Les résultats se mettront alors automatiquement à jour lorsque les données de la plage changent.
3. Ensuite, une boîte de dialogue "Extraire le texte" apparaît, vous devez sélectionner une cellule pour afficher le résultat, puis cliquer sur le bouton "OK".
Les textes avant ou après un certain mot dans chaque cellule de la plage sélectionnée sont alors extraits immédiatement.
Remarque : Cette fonctionnalité est sensible à la casse.
Cliquez pour en savoir plus sur cette fonctionnalité.
Kutools pour Excel - Boostez Excel avec plus de 300 outils essentiels. Profitez de fonctionnalités IA gratuites en permanence ! Obtenez-le maintenant
3. Extraire entre les caractères/mots
Si vous souhaitez extraire du texte entre certains caractères ou mots, essayez les méthodes suivantes.
3.1 Extraire le texte entre deux caractères
Pour extraire le texte entre deux caractères, qui peuvent être identiques ou différents. Cette section propose plusieurs méthodes, et vous pouvez choisir celle qui répond le mieux à vos besoins.
3.1.1 Extraire le texte entre deux caractères identiques avec une formule
Comme le montre la capture d'écran ci-dessous, il y a une liste de chaînes de texte dans la colonne B, et vous souhaitez extraire la partie numérique située entre les caractères « / » de chaque cellule dans la plage. La formule suivante peut vous aider.
Sélectionnez une cellule vide, copiez ou entrez la formule ci-dessous, puis appuyez sur la touche "Entrée" pour obtenir le résultat. Sélectionnez la cellule de résultat, puis faites glisser la poignée de recopie automatique vers le bas pour obtenir les résultats pour les autres cellules de la liste.
=SUBSTITUTE(MID(SUBSTITUTE("/" & B5&REPT(" ",6),"/",REPT(",",255)),2*255,255),",","")Ensuite, le texte entre deux mêmes caractères « / » est extrait de chaque cellule dans la plage. Voir la capture d'écran :
Remarque :
3.1.2 Extraire le texte entre deux caractères différents avec une formule
Après avoir appris à extraire du texte entre deux caractères identiques dans une cellule, nous allons ici démontrer une formule pour extraire du texte entre deux caractères différents. Comme le montre la capture d'écran ci-dessous, pour extraire uniquement l'adresse e-mail entre les symboles « < » et « > » de chaque cellule de la colonne B, vous pouvez procéder comme suit.
Formule générique
=STXT(GAUCHE(cellule;TROUVE("car_fin";cellule)-1);TROUVE("car_début";cellule)+1;NBCAR(cellule))
Arguments
Sélectionnez une cellule vide, copiez ou entrez la formule ci-dessous et appuyez sur la touche "Entrée" pour obtenir le résultat. Sélectionnez cette cellule de résultat, puis faites glisser son poignée de recopie automatique vers le bas pour appliquer cette formule à d'autres cellules.
=MID(LEFT(B5,FIND(">",B5)-1),FIND("<",B5)+1,LEN(B5))
Vous pouvez constater que seul le texte situé entre les caractères spécifiés est extrait, comme le montre la capture d'écran ci-dessus.
3.1.3 Extraire le texte entre deux caractères avec un outil incroyable
Ici, nous recommandons vivement la fonctionnalité "Extraire la chaîne entre des textes spécifiés" de "Kutools for Excel" pour vous aider à extraire facilement du texte entre deux caractères identiques ou différents dans une cellule Excel.
1. Sélectionnez une cellule vide pour afficher le résultat, puis cliquez sur "Kutools" > "Assistant de formule" > "Assistant de formule".
2. Dans la boîte de dialogue "Assistant de formules", effectuez les réglages suivants.
3. Ensuite, seul le texte entre « < » et « > » dans la cellule B5 est extrait. Pendant ce temps, une formule a été créée, vous pouvez sélectionner cette cellule de résultat, puis faire glisser sa poignée de recopie automatique vers le bas pour extraire les textes d'autres cellules de la même liste.
Cliquez pour en savoir plus sur cette fonctionnalité.
Kutools pour Excel - Boostez Excel avec plus de 300 outils essentiels. Profitez de fonctionnalités IA gratuites en permanence ! Obtenez-le maintenant
3.1.4 Extraire le texte entre deux caractères (y compris les caractères) selon une règle
Si vous souhaitez conserver les deux caractères après extraction, essayez d'appliquer une règle dans la fonction Extraire le texte de Kutools pour Excel.
1. Cliquez sur "Kutools" > "Texte" > "Extraire le texte".
2. Dans la boîte de dialogue « Extraire le texte », effectuez les réglages suivants.
bouton pour sélectionner la plage de cellules où vous souhaitez extraire le texte entre les caractères ;
3. Une autre boîte de dialogue "Extraire le texte" apparaît. Veuillez sélectionner une cellule pour afficher le résultat, puis cliquez sur le bouton "OK".
Ensuite, le texte entre les caractères spécifiés (y compris les caractères) est extrait en bloc de chaque cellule dans la plage sélectionnée.
Kutools pour Excel - Boostez Excel avec plus de 300 outils essentiels. Profitez de fonctionnalités IA gratuites en permanence ! Obtenez-le maintenant
3.2 Extraire le texte entre deux mots
En plus d'extraire le texte entre deux caractères, vous pourriez également avoir besoin d'extraire le texte entre deux mots. Par exemple, extrayez toutes les chaînes de texte entre les deux mots « KTE » et « fonctionnalité » de chaque cellule dans la colonne B, comme illustré dans la capture d'écran ci-dessous. Vous pouvez essayer l'une des méthodes suivantes pour y parvenir.
3.2.1 Extraire le texte entre deux mots avec une formule
Vous pouvez utiliser une formule basée sur la fonction MID et la fonction SEARCH pour extraire toutes les chaînes de texte entre deux mots dans une cellule.
Formule générique
=STXT(cellule;RECHERCHE("mot_début";cellule)+3;RECHERCHE("mot_fin";cellule)-RECHERCHE("mot_début";cellule)-4)
Arguments
Sélectionnez une cellule vide, copiez ou entrez la formule ci-dessous et appuyez sur la touche "Entrée" pour obtenir le résultat. Sélectionnez cette cellule de résultat, puis faites glisser son poignée de recopie automatique vers le bas pour appliquer cette formule à d'autres cellules.
=MID(B5,SEARCH("KTE",B5)+3,SEARCH("feature",B5)-SEARCH("KTE",B5)-4)
Remarque : Dans la formule, le nombre 3 représente la longueur en caractères du mot "KTE" ; le nombre 4 représente la longueur en caractères du mot "KTE" plus 1.
Vous pouvez constater que toutes les chaînes de texte entre les deux mots spécifiés sont extraites de chaque cellule de la colonne B.
3.2.2 Extraire le texte entre deux mots avec un outil incroyable
Pour de nombreux utilisateurs d'Excel, les formules peuvent être difficiles à mémoriser et à manipuler. Ici, avec la fonctionnalité "Extraire la chaîne entre des textes spécifiés" de "Kutools for Excel", vous pouvez facilement extraire du texte entre deux mots en quelques clics seulement.
1. Sélectionnez une cellule pour afficher le résultat, puis cliquez sur "Kutools" > "Assistant de formule" > "Assistant de formule."
2. Dans la boîte de dialogue « Assistant de formule », vous devez configurer comme suit.
3. Ensuite, toutes les chaînes de texte entre les deux mots « KTE » et « feature » dans la cellule B5 sont extraites. Pendant ce temps, une formule a été créée, vous pouvez sélectionner cette cellule de résultat, puis faire glisser sa poignée de recopie automatique vers le bas pour extraire du texte à partir d'autres cellules de la même liste.
Kutools pour Excel - Boostez Excel avec plus de 300 outils essentiels. Profitez de fonctionnalités IA gratuites en permanence ! Obtenez-le maintenant
Extraire les nombres par position
Pour une liste de chaînes alphanumériques, il peut y avoir trois cas :
- Le nombre se trouve au début du texte ;
- Le nombre est à la fin du texte ;
- Le nombre peut se trouver n'importe où dans le texte.
Dans cette section, nous présenterons différentes méthodes qui peuvent être utilisées pour extraire les nombres dans chaque cas mentionné ci-dessus.
1 Extraire le nombre à gauche d'une chaîne de caractères
Cette partie présentera une formule pour vous aider à extraire uniquement les nombres qui apparaissent avant le texte dans une cellule.
Formule générique
=GAUCHE(cellule, EQUIV(FAUX, ESTNUM(CAR(cellule, LIGNE(INDIRECT("1:"&NBCAR(cellule)+1)), 1) *1), 0) -1)
Arguments
Remarque :
Sélectionnez une cellule vide, entrez la formule ci-dessous et appuyez sur "Ctrl" + "Maj" + "Entrée" ou sur la touche "Entrée" pour obtenir le résultat. Sélectionnez cette cellule de résultat, puis faites glisser son poignée de recopie automatique vers le bas pour obtenir les nombres des autres cellules.
=LEFT(B5, MATCH(FALSE, ISNUMBER(MID(B5, ROW(INDIRECT("1:"&LEN(B5)+1)), 1) *1), 0) -1)
Remarques :
2 Extraire le nombre à droite d'une chaîne de caractères
Comme le montre la capture d'écran ci-dessous, pour extraire uniquement les nombres qui apparaissent après un texte dans une cellule, essayez la formule suivante.
Formule générique
=DROITE(cellule, NBCAR(cellule) - MAX(SI(ESTNUM(STXT(cellule, LIGNE(INDIRECT("1:"&NBCAR(cellule))), 1) *1)=FAUX, LIGNE(INDIRECT("1:"&NBCAR(cellule))), 0)))
Arguments
Remarque :
Sélectionnez une cellule vide, entrez la formule ci-dessous et appuyez sur "Ctrl" + "Maj" + "Entrée" ou sur la touche "Entrée" pour obtenir le résultat. Sélectionnez cette cellule de résultat, puis faites glisser son poignée de recopie automatique vers le bas pour obtenir les nombres des autres cellules.
=RIGHT(B5, LEN(B5) - MAX(IF(ISNUMBER(MID(B5, ROW(INDIRECT("1:"&LEN(B5))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(B5))), 0)))
Remarques :
3. Extraire tous les nombres de n'importe quelle position dans une chaîne de texte
Les méthodes ci-dessus permettent d'extraire uniquement les nombres situés à gauche ou à droite d'une chaîne de texte. Si vous souhaitez extraire tous les nombres présents n'importe où dans une chaîne de texte, voici trois méthodes pour y parvenir.
3.1 Extraire tous les nombres de n'importe où dans une chaîne de caractères avec une formule
Vous pouvez appliquer la formule suivante pour extraire tous les nombres situés n'importe où dans une chaîne de texte dans Excel.
1. Sélectionnez une cellule vide, copiez ou entrez la formule ci-dessous et appuyez sur la touche "Entrée" pour extraire tous les nombres de la cellule B5.
=SUMPRODUCT(MID(0&B5, LARGE(INDEX(ISNUMBER(--MID(B5, ROW(INDIRECT("1:"&LEN(B5))), 1)) * ROW(INDIRECT("1:"&LEN(B5))), 0), ROW(INDIRECT("1:"&LEN(B5))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(B5)))/10)
2. Sélectionnez la cellule de résultat, puis faites glisser sa poignée de recopie automatique vers le bas pour obtenir tous les nombres des autres cellules.
3.2 Extraire tous les nombres de n'importe où dans une chaîne de caractères avec VBA
La formule ci-dessus est trop longue et compliquée pour de nombreux utilisateurs d'Excel. En réalité, vous pouvez exécuter un script VBA pour automatiser cette tâche dans Excel. Vous pouvez procéder comme suit.
1. Appuyez sur les touches "Alt" + "F11" pour ouvrir la fenêtre "Microsoft Visual Basic for Applications".
2. Dans la fenêtre ouverte "Microsoft Visual Basic for Applications", cliquez sur "Insertion" > "Module". Ensuite, copiez le code VBA ci-dessous dans la fenêtre de code du Module.
Code VBA : Extraire tous les nombres de n'importe où dans une chaîne de texte
Sub ExtrNumbersFromRange()
'Updated by Extendoffice 20220106
Dim xRg As Range
Dim xDRg As Range
Dim xRRg As Range
Dim nCellLength As Integer
Dim xNumber As Integer
Dim strNumber As String
Dim xTitleId As String
Dim xI As Integer
xTitleId = "KutoolsforExcel"
Set xDRg = Application.InputBox("Please select text strings:", xTitleId, "", Type:=8)
If TypeName(xDRg) = "Nothing" Then Exit Sub
Set xRRg = Application.InputBox("Please select output cell:", xTitleId, "", Type:=8)
If TypeName(xRRg) = "Nothing" Then Exit Sub
xI = 0
strNumber = ""
For Each xRg In xDRg
xI = xI + 1
nCellLength = Len(xRg)
For xNumber = 1 To nCellLength
If IsNumeric(Mid(xRg, xNumber, 1)) Then
strNumber = strNumber & Mid(xRg, xNumber, 1)
End If
Next xNumber
xRRg.Item(xI) = strNumber
strNumber = ""
Next xRg
End Sub3. Appuyez sur la touche "F5" pour exécuter le code. Dans la boîte de dialogue "KutoolsforExcel" qui s'ouvre, sélectionnez la plage de cellules à partir de laquelle vous souhaitez extraire tous les nombres de chaque cellule, puis cliquez sur le bouton "OK".
4. Ensuite, une autre boîte de dialogue "KutoolsforExcel" apparaît. Dans cette boîte de dialogue, sélectionnez une cellule de destination et cliquez sur "OK".
Ensuite, tous les nombres sont extraits en bloc de chaque cellule dans la plage sélectionnée.
4. Extraire les nombres après un texte spécifique
Comme le montre la capture d'écran ci-dessous, pour extraire tous les nombres après le texte spécifique « No. », cette section propose deux méthodes pour vous aider à réaliser cette tâche.
4.1 Extraire les nombres après un texte spécifique avec une formule
Vous pouvez appliquer la formule suivante pour extraire des nombres après un texte spécifique dans une cellule dans Excel.
Formule générique :
=RECHERCHE(10^6,1*MID(cellule,MIN(TROUVE({0,1,2,3,4,5,6,7,8,9},cellule&"0123456789",TROUVE("texte"," "&cellule&" "))),{2,3,4,5,6}))
Arguments
Sélectionnez une cellule vide, copiez ou entrez la formule ci-dessous et appuyez sur la touche "Entrée" pour obtenir le résultat. Sélectionnez cette cellule de résultat, puis faites glisser son poignée de recopie automatique vers le bas pour appliquer cette formule à d'autres cellules.
=LOOKUP(10^6,1*MID(B5,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&"0123456789",FIND("No."," "&B5&" "))),{2,3,4,5,6}))
Remarques :
4.2 Extraire les nombres après un texte spécifique avec une fonction définie par l'utilisateur
La fonction utilisateur suivante peut également aider à extraire des nombres après un texte spécifique dans une cellule. Veuillez procéder comme suit.
1. Appuyez sur les touches "Alt" + "F11" pour ouvrir la fenêtre "Microsoft Visual Basic for Applications".
2. Dans la fenêtre "Microsoft Visual Basic for Applications", cliquez sur "Insertion" > "Module", puis copiez le code VBA ci-dessous dans la fenêtre de code du Module.
Code VBA : extraire les nombres après un texte spécifique dans une cellule
Function GetNumberAfterTheChar(Rng As Range, Char As String)
'Updated by Extendoffice 20220106
Dim xValue As String
Dim xRntString As String
Dim xStart As Integer
Dim xC
xValue = Rng.Text
xStart = InStr(1, xValue, Char, vbTextCompare)
If IsEmpty(xStart) Then
GetNumberAfterTheChar = ""
Exit Function
End If
If xStart < 1 Then
GetNumberAfterTheChar = ""
Exit Function
End If
xStart = xStart - 1 + Len(Char)
If xStart < 1 Then
GetNumberAfterTheChar = ""
Exit Function
End If
xValue = Mid(xValue, xStart + 1)
xRntString = ""
For xI = 1 To Len(xValue)
xC = Mid(xValue, xI, 1)
Select Case Asc(xC)
Case 48 To 57
xRntString = xRntString & xC
Case Else
Exit For
End Select
Next
GetNumberAfterTheChar = xRntString
End Function
3. Appuyez sur les touches "Alt" + "Q" pour fermer la fenêtre "Microsoft Visual Basic for Applications".
4. Sélectionnez une cellule, entrez la formule ci-dessous et appuyez sur la touche "Entrée". Sélectionnez cette cellule de résultat, puis faites glisser sa poignée de recopie automatique vers le bas pour appliquer cette formule à d'autres cellules.
=GetNumberAfterTheChar(B5,"No. ")
Remarques :
Articles connexes :
Tutoriel Excel : Diviser les cellules de texte, de nombre et de date (séparer en plusieurs colonnes)
Ce tutoriel est divisé en trois parties : diviser des cellules de texte, diviser des cellules de nombres et diviser des cellules de dates. Chaque partie fournit différents exemples pour vous aider à comprendre comment gérer la tâche de division lorsque vous rencontrez le même problème.
Cliquez pour en savoir plus...
Ajouter du texte et des nombres à une position spécifique dans une cellule Excel
Dans Excel, ajouter des textes ou des nombres dans des cellules est une tâche très courante. Par exemple, ajouter un espace entre les noms, ajouter un préfixe ou un suffixe aux cellules, ou encore ajouter des tirets aux numéros de sécurité sociale. Dans ce tutoriel, nous listons presque toutes les situations d'ajout possibles dans Excel et vous fournissons les méthodes correspondantes.
Cliquez pour en savoir plus...
Excel Supprimer des caractères, mots, nombres des chaînes de texte
Supposons que vous ayez une longue liste de chaînes de texte contenant des caractères, des nombres ou d'autres symboles spécifiques. Dans certains cas, vous pourriez avoir besoin de supprimer certains caractères en fonction de leur position, par exemple depuis la droite, la gauche ou le milieu des chaînes de texte, ou encore effacer certains caractères ou nombres indésirables de la liste de chaînes. Trouver les solutions une par une pourrait vous donner mal à la tête, ce tutoriel rassemble donc toutes sortes de méthodes pour supprimer des caractères, des mots ou des nombres dans Excel.
Cliquez pour en savoir plus...
Meilleurs outils de productivité pour Office
Boostez vos compétences Excel avec Kutools pour Excel, et découvrez une efficacité sans précédent. Kutools pour Excel propose plus de300 fonctionnalités avancées pour augmenter la productivité et gagner du temps. Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte une interface à onglets à Office, et facilite grandement votre travail
- Activez la modification et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
- Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
- Augmente votre productivité de50 %, et réduit des centaines de clics de souris pour vous chaque jour !