Tutoriel Excel - extraire du texte ou un nombre par position spécifique

Dans de nombreux cas, vous n'aurez peut-être besoin que d'extraire le contenu utile d'une phrase ou d'une chaîne de texte dans une cellule, comme extraire la province de l'adresse, extraire l'adresse e-mail d'une phrase, extraire le numéro de facture du courrier d'une conversation, etc. Ce didacticiel réduit 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 par position spécifique dans Excel.

Extraire le texte par position

Cette section rassemble des positions communes où un texte peut être extrait d'une cellule et fournit des méthodes correspondantes pour les gérer étape par étape. Vous pouvez parcourir pour plus de détails.

1. Extraire le nombre de caractères de gauche ou de droite

Pour extraire le nombre de caractères du côté gauche ou droit d'une chaîne, vous pouvez essayer l'une des méthodes ci-dessous.

1.1 Extraire les N premiers ou derniers caractères avec des formules

En supposant que vous ayez une liste de chaînes de texte dans la colonne B, comme indiqué dans la capture d'écran ci-dessous, pour extraire les 2 premiers caractères et les 2 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

=LEFT(text_string,[num_chars])

Arguments

Vous pouvez maintenant appliquer cette formule pour extraire les 2 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 le bouton Entrer clé pour obtenir le premier résultat. Sélectionnez la cellule de résultat et faites glisser sa poignée de remplissage automatique vers le bas pour appliquer la formule à d'autres cellules.

=LEFT(B5,2)

Maintenant, les 2 premiers caractères de chaque cellule de la plage B5:B10 ont été extraits.

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

=RIGHT(text_string,[num_chars])

Arguments

Sélectionnez une cellule vide, copiez ou entrez la formule ci-dessous et appuyez sur le bouton Entrer clé pour obtenir le résultat. Sélectionnez ensuite cette cellule de résultat et faites glisser sa poignée de remplissage automatique vers le bas pour obtenir d'autres résultats.

=RIGHT(B5,2)

1.2 Extraire les N premiers ou derniers caractères avec un outil incroyable

Bien que les formules ci-dessus soient simples, pour extraire les n premiers ou derniers caractères d'une longue liste de chaînes de texte, vous devez toujours faire glisser la poignée de remplissage automatique de haut en bas, ce qui peut prendre un peu de temps. Ici recommande Kutools for Excel's Extrait du texte utilitaire pour aider à extraire les N premiers ou derniers caractères d'une liste de chaînes de texte en bloc.

1. Sélectionnez à l'avance la liste de chaînes de texte dont vous souhaitez extraire le texte et cliquez sur Kutools > Texte > Extrait du texte.

2. Dans le pop-up Extrait du texte boîte de dialogue, vous devez configurer comme suit.

Notes: Pour rendre le résultat dynamique lorsque la chaîne de texte change, vous pouvez cocher la case Insérer comme formule boîte.

3. Dans le prochain pop-up Extrait du texte boîte de dialogue, choisissez une cellule pour afficher les caractères extraits, puis cliquez sur OK.

Ensuite, les N premiers ou derniers caractères spécifiés sont extraits des cellules sélectionnées en bloc.

Cliquez pour en savoir plus sur cette fonctionnalité.

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 du texte avant ou après le premier délimiteur de chaque cellule de 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 formule

Appliquer une formule basée sur les fonctions LEFT et FIND peut aider à extraire du texte avant le premier délimiteur d'une cellule. Vous pouvez suivre les étapes ci-dessous pour le faire.

Formule générique

=LEFT(text_string,FIND("delimiter",text_string,1)-1)

Arguments

Sélectionnez une cellule vide, copiez ou entrez la formule ci-dessous et appuyez sur le bouton Entrer clé pour obtenir le premier résultat. Sélectionnez la première cellule de résultat et faites glisser sa poignée de remplissage 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 la formule

La formule ci-dessous permet d'extraire du texte après le premier délimiteur d'une cellule dans Excel.

Formule générique

=MID(text_string,FIND("delimiter",text_string)+1,LEN(text_string))

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 recommande fortement le Extrait du texte utilité de Kutools pour Excel. Avec cette fonctionnalité, vous pouvez facilement extraire des textes avant ou après le premier délimiteur d'une plage de cellules en bloc.

1. Sélectionnez la plage de cellules où vous souhaitez extraire le texte, puis cliquez sur Kutools > Texte > Extrait du texte.

2. dans le Extrait du texte boîte de dialogue, 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. Puis un autre Extrait du texte boîte de dialogue apparaît, choisissez une cellule pour afficher les résultats et cliquez sur D'ACCORD.

Ensuite, les textes avant ou après le premier délimiteur sont extraits des cellules sélectionnées à la fois.

Pour en savoir plus sur cette fonctionnalité, veuillez visiter : Extraire rapidement certains textes de cellules dans Excel.

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 d'extraction de 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 du texte avant ou après le dernier délimiteur d'une cellule. Vous pouvez suivre les étapes ci-dessous pour le faire.

2.2.1 Extraire le texte avant le dernier délimiteur avec la formule

Pour extraire du texte avant le dernier délimiteur d'une cellule, vous pouvez utiliser les fonctions RECHERCHE, LEN et SUBSTITUE à l'intérieur de la fonction GAUCHE.

Formule générique

=LEFT(text_string,SEARCH("#",SUBSTITUTE(text_string,"delimiter","#",LEN(text_string)-LEN(SUBSTITUTE(text_string,"delimiter",""))))-1)

Arguments

Sélectionnez une cellule, saisissez la formule ci-dessous et appuyez sur la touche Entrer clé pour obtenir le résultat. Sélectionnez cette cellule de résultat et faites glisser sa poignée de remplissage 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 la 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

=RIGHT(text_string,LEN(text_string)-SEARCH("#",SUBSTITUTE(text_string,"delimiter","#",LEN(text_string)-LEN(SUBSTITUTE(text_string,"delimiter","")))))

Arguments

Sélectionnez une cellule, saisissez la formule ci-dessous et appuyez sur la touche Entrer clé pour obtenir le résultat. Sélectionnez cette cellule de résultat et faites glisser sa poignée de remplissage 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 nième caractère

Regardez l'exemple ci-dessous, il existe 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 la fonction MID et la fonction LEN.

Formule générique

=MID(text_string,nth_char+1,LEN(text_string))

Arguments

Sélectionnez une cellule vide, copiez ou entrez la formule ci-dessous et appuyez sur le bouton Entrer clé pour obtenir le résultat. Sélectionnez cette cellule de résultat et faites glisser sa poignée de remplissage automatique vers le bas pour obtenir d'autres résultats.

=MID(B5,3+1,LEN(B5))

2.4 Extraire le nième mot d'une chaîne de texte

En supposant que vous ayez une liste de chaînes de texte comme indiqué dans la capture d'écran ci-dessous et que vous vouliez extraire uniquement le nième mot de la chaîne de texte, cette section vous propose trois méthodes pour le faire.

2.4.1 Extraire le nième mot avec la formule

Vous pouvez combiner les fonctions TRIM, MID, SUBSTITUTE, REPT et LEN pour extraire le nième mot d'une chaîne de texte dans une cellule.

Formule générique

=TRIM(MID(SUBSTITUTE(text_string," ",REPT(" ",LEN((text_string))), (N-1)*LEN((text_string)+1, LEN((text_string)))

Arguments

Dans ce cas, la plage B5:B10 contient les chaînes de texte, D5:D10 contient les nombres représentant le nième mot, appliquons cette formule pour extraire le nième mot de la chaîne de texte.

Sélectionnez une cellule vide, copiez ou entrez la formule ci-dessous et appuyez sur le bouton Entrer clé pour obtenir le premier résultat. Sélectionnez cette cellule de résultat et faites glisser sa poignée de remplissage automatique vers le bas pour obtenir le nième mot des autres cellules.

=TRIM(MID(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))), (D5-1)*LEN(B5)+1, LEN(B5)))

Remarque : Vous pouvez taper directement le nième nombre dans la formule comme suit.

=TRIM(MID(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))), (2-1)*LEN(B5)+1, LEN(B5)))

2.4.2 Extraire le nième mot avec la 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 nième mot d'une cellule dans Excel.

1. appuie sur le autre + F11 clés pour ouvrir le Microsoft Visual Basic pour applications fenêtre.

2. dans le Microsoft Visual Basic pour applications fenêtre, cliquez sur insérer > Module, puis copiez le VBA ci-dessous dans la fenêtre Code.

Code VBA : extraire le niè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. appuie sur le autre + Q touches pour fermer le Microsoft Visual Basic pour applications fenêtre.

4. Revenez à la feuille de calcul contenant les chaînes de texte dont vous souhaitez extraire le nième mot. Sélectionnez une cellule vide, copiez ou entrez la formule ci-dessous et appuyez sur le bouton Entrer clé pour obtenir le nième mot.

=FindWord(B5,D5)

Or

=FindWord(B5,2)

Remarque : dans la formule, D5 est la cellule qui contient un nombre représentant le nième mot. Alternativement, vous pouvez remplacer directement la référence de la cellule par un nombre.

5. Sélectionnez la cellule de résultat et faites glisser sa poignée de remplissage automatique vers le bas pour extraire le nième mot des chaînes de texte des autres cellules.

2.4.3 Extraire le nième mot avec un outil incroyable

Si vous ne souhaitez pas appliquer manuellement une formule ou une fonction définie par l'utilisateur fournie ci-dessus, recommande ici Kutools for Excel Extraire le nième mot de la cellule utilitaire. Avec cette fonctionnalité, vous pouvez facilement extraire le niè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 et cliquez sur Kutools > Aide à la formule > Texte > Extraction le nième mot dans la cellule. Voir la capture d'écran:

2. dans le Aide aux formules boîte de dialogue, vous devez configurer comme suit.

3. Ensuite, le niè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 remplissage automatique vers le bas pour obtenir le nième mot à partir d'autres chaînes de texte.

Cliquez pour en savoir plus sur cette fonctionnalité.

2.5 Extraire le texte avant ou après la nième occurrence d'un délimiteur

Supposons que vous ayez une liste de chaînes de texte, comme indiqué dans la capture d'écran ci-dessous. Pour extraire le texte avant ou après la deuxième occurrence d'un espace, cette section fournit deux formules pour vous aider à le faire.

2.5.1 Extraire le texte avant la nième occurrence d'un délimiteur

Vous pouvez utiliser la fonction GAUCHE avec les fonctions SUBSTITUT et FIND pour extraire du texte avant la nième occurrence d'un délimiteur d'une cellule dans Excel.

Formule générique

=LEFT(SUBSTITUTE(text_string,"delimiter",CHAR(9),n),FIND(CHAR(9),SUBSTITUTE(text_string,"delimiter",CHAR(9),n),1)-1)

Arguments

Sélectionnez une cellule, copiez ou entrez la formule ci-dessous et appuyez sur le bouton Entrer clé pour obtenir le résultat. Sélectionnez cette cellule de résultat et faites-la glisser 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 représente la deuxième occurrence d'un espace. Vous pouvez les modifier selon vos besoins.

2.5.2 Extraire le texte après la nième occurrence d'un délimiteur

Pour extraire du texte après la nième occurrence d'un délimiteur, vous pouvez appliquer la fonction RIGHT avec les fonctions SUBSTITUTE, LEN et FIND.

Formule générique

=RIGHT(SUBSTITUTE(text_string, "delimiter", CHAR(9), n), LEN(text_string)- FIND(CHAR(9), SUBSTITUTE(text_string, "delimiter", CHAR(9), n), 1) + 1)

Arguments

Vous pouvez maintenant appliquer cette formule pour extraire le texte après la deuxième occurrence de l'espace de chaque cellule de la plage B5:B10 comme suit.

Sélectionnez une cellule, saisissez la formule ci-dessous et appuyez sur la touche Entrer clé pour obtenir le résultat. Sélectionnez cette cellule de résultat et faites-la glisser vers le bas pour obtenir d'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 le 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 le faire.

2.6.1 Extraire le texte avant le premier saut de ligne avec la formule

Comme vous pouvez le voir dans la capture d'écran ci-dessus, la partie date se situe avant le premier saut de ligne dans la cellule. Cette section présentera une fonction GAUCHE ainsi que la fonction RECHERCHE pour vous aider à extraire le texte avant le premier saut de ligne dans une cellule.

Formule générique

=LEFT(cell, SEARCH(CHAR(10), cell)-1)

Arguments

Sélectionnez une cellule vide, copiez ou entrez la formule ci-dessous et appuyez sur le bouton Entrer clé pour obtenir le résultat. Sélectionnez cette cellule de résultat, puis faites glisser sa poignée de remplissage automatique vers le bas pour appliquer cette formule à d'autres cellules.

=LEFT(B5, SEARCH(CHAR(10), B5)-1)

Ensuite, vous pouvez voir le texte avant que le premier saut de ligne dans chaque cellule de la plage B5:B8 ne soit extrait, comme indiqué dans la capture d'écran ci-dessous.

Remarque : Dans la formule, CHAR(10) représente un saut de ligne sous Windows.

2.6.2 Extraire le texte après le dernier saut de ligne avec la formule

À l'étape précédente, nous avons expliqué comment extraire du texte avant le premier saut de ligne dans une cellule. Et cette partie vous expliquera comment extraire du texte après le dernier saut de ligne dans une cellule avec une formule différente.

Formule générique

=TRIM(RIGHT(SUBSTITUTE(cell,CHAR(10),REPT(" ",200)),200))

Arguments

Sélectionnez une cellule vide, entrez la formule ci-dessous et appuyez sur le bouton Entrer clé pour obtenir le résultat. Sélectionnez cette cellule de résultat, puis faites glisser sa poignée de remplissage automatique vers le bas pour appliquer la formule à d'autres cellules.

=TRIM(RIGHT(SUBSTITUTE(B5,CHAR(10),REPT(" ",200)),200))

Ensuite, le numéro de produit. une partie de chaque cellule de la liste est extraite comme indiqué dans la capture d'écran ci-dessus.

Remarque : Dans la formule, CHAR(10) représente un saut de ligne sous Windows.

2.7 Extraire du texte avant ou après un mot

Dans les parties précédentes, nous avons appris à 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 du texte avant un certain mot avec une formule

La formule suivante vous aide à extraire du texte avant un certain mot dans une cellule dans Excel.

Formule générique

=IFERROR(LEFT(cell,FIND(word,cell)-1),cell)

Arguments

Sélectionnez une cellule vide, entrez la formule ci-dessous et appuyez sur le bouton Entrer clé pour obtenir le résultat. Sélectionnez cette cellule de résultat, puis faites glisser sa poignée de remplissage 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 le mettons entre guillemets. Ou vous pouvez faire référence à une cellule qui contient le mot « Excel ».

=IFERROR(LEFT(B5,FIND("Excel",B5)-1),B5)

Notes:

2.7.2 Extraire du texte après un certain mot avec une formule

Pour extraire du texte après un certain mot, vous pouvez appliquer la formule suivante pour le faire.

Formule générique

=TRIM(MID(cell,SEARCH(word,cell)+LEN(word),255))

Arguments

Sélectionnez une cellule, saisissez la formule ci-dessous et appuyez sur la touche Entrer clé pour obtenir le résultat. Sélectionnez la cellule de résultat, puis faites glisser sa poignée de remplissage automatique vers le bas pour appliquer cette formule à d'autres cellules.

=TRIM(MID(B5,SEARCH("Excel",B5)+LEN("Excel"),255))

Ensuite, vous pouvez voir tous les textes après que le mot "Excel" dans chaque cellule est extrait comme indiqué dans la capture d'écran ci-dessous.

Notes:

2.7.3 Extraire du texte avant ou après un certain mot avec un outil incroyable

Si vous pensez que l'utilisation d'une formule peut causer beaucoup d'inconvénients, nous vous recommandons fortement de Extrait du texte utilité de Kutools for Excel. Cette fonctionnalité permet d'automatiser la tâche d'extraction dans Excel en quelques clics.

1. Cliquez Kutools > Texte > Extrait du texte pour activer cette fonctionnalité.

2. dans le Extrait du texte boîte de dialogue, effectuez les réglages suivants.

2.1) Assurez-vous que vous êtes dans le Extrait par emplacement languette;

2.2) Dans le Catégorie boîte, cliquez sur la bouton pour sélectionner la plage de cellules où vous souhaitez extraire les textes ;

2.3) Dans le Options section:

Pour extraire tous les textes avant un mot, choisissez le avant le texte bouton radio, puis entrez le mot dans la zone de texte ;

Pour extraire tous les textes après un mot, choisissez le après le texte bouton radio, puis saisissez le mot dans la zone de texte.

2.4) Cliquez sur le OK bouton. Voir la capture d'écran:

Notes: Si vous souhaitez créer des résultats dynamiques, cochez la case Insérer comme formule boîte. Ensuite, les résultats seront automatiquement mis à jour lorsque les données de la plage changeront.

3. Puis un Extrait du texte boîte de dialogue apparaît, vous devez sélectionner une cellule pour afficher le résultat, puis cliquez sur le OK .

Ensuite, les textes avant ou après un certain mot dans chaque cellule de la plage sélectionnée sont extraits immédiatement.

Remarque : Cette fonctionnalité est sensible à la casse.

Cliquez pour en savoir plus sur cette fonctionnalité.

3. Extraire entre caractères/mots

Si vous souhaitez extraire du texte entre certains caractères ou mots, essayez les méthodes suivantes.

3.1 Extraire du texte entre deux caractères

Pour extraire du texte entre deux caractères, qui peuvent être identiques ou différents. Cette section propose plusieurs méthodes, et vous pouvez en choisir une en fonction de vos besoins.

3.1.1 Extraire du texte entre deux mêmes caractères avec 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 entre les caractères "/" de chaque cellule de la plage, la formule suivante peut vous rendre service.

Sélectionnez une cellule vide, copiez ou entrez la formule ci-dessous et appuyez sur le bouton Entrer touche pour obtenir le résultat. Sélectionnez la cellule de résultat, puis faites-la glisser AutoFill Handle vers le bas pour obtenir les résultats des 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 de la plage. Voir capture d'écran :

Remarque:

3.1.2 Extraire du texte entre deux caractères différents avec formule

Après avoir appris à extraire du texte entre deux mêmes caractères dans une cellule, nous allons démontrer ici 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 le « < » et le « > » de chaque cellule de la colonne B, vous pouvez procéder comme suit.

Formule générique

=MID(LEFT(cel,FIND("end_char",cell)-1),FIND("start_char",cell)+1,LEN(cell))

Arguments

Sélectionnez une cellule vide, copiez ou entrez la formule ci-dessous et appuyez sur le bouton Entrer clé pour obtenir le résultat. Sélectionnez cette cellule de résultat, puis faites glisser sa poignée de remplissage automatique vers le bas pour appliquer cette formule à d'autres cellules.

=MID(LEFT(B5,FIND(">",B5)-1),FIND("<",B5)+1,LEN(B5))

Vous pouvez voir que seul le texte entre les caractères spécifiés est extrait comme indiqué dans la capture d'écran ci-dessus.

3.1.3 Extraire du texte entre deux caractères avec un outil incroyable

Ici recommande fortement le Extraire les chaînes entre le texte spécifié caractéristique de Kutools for Excel pour vous aider à extraire facilement du texte entre deux caractères identiques ou différents dans une cellule dans Excel.

1. Sélectionnez une cellule vide pour afficher le résultat, puis cliquez sur Kutools > Aide à la formule > Aide à la formule.

2. dans le Aide aux formules boîte de dialogue, 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 remplissage automatique vers le bas pour extraire les textes d'autres cellules de la même liste.

Cliquez pour en savoir plus sur cette fonctionnalité.

3.1.4 Extraire le texte entre deux caractères (y compris les caractères) par règle

Si vous souhaitez conserver les deux caractères après l'extraction, essayez d'appliquer une règle dans la fonction Extraire le texte de Kutools for Excel.

1. Cliquez Kutools > Texte > Extrait du texte.

2. dans le Extrait du texte boîte de dialogue, effectuez les réglages suivants.

2.1) Cliquez sur le Extrait par règle languette;

2.2) Dans le Catégorie section, cliquez sur bouton pour sélectionner la plage de cellules où vous souhaitez extraire le texte entre les caractères ;

2.3) Dans le Texte boîte, entrez <*>;

Conseils: "<" et ">" sont les caractères que vous souhaitez extraire du texte entre eux, * est un caractère générique qui représente un nombre quelconque de caractères. Vous pouvez entrer la condition selon vos besoins.

2.4) Cliquez sur le Ajouter bouton pour ajouter la condition au Description de la règle zone de liste;

2.5) Cliquez sur le Ok bouton. Voir la capture d'écran:

3. Un autre Extrait du texte boîte de dialogue apparaît, veuillez sélectionner une cellule pour afficher le résultat, puis cliquez sur le OK .

Ensuite, le texte entre les caractères spécifiés (y compris les caractères) est extrait de chaque cellule de la plage sélectionnée en bloc.

3.2 Extraire du texte entre deux mots

En plus d'extraire du texte entre deux caractères, vous devrez peut-être également extraire du texte entre deux mots. Par exemple, extrayez toutes les chaînes de texte entre deux mots « KTE » et « fonctionnalité » de chaque cellule de la colonne B, comme indiqué dans la capture d'écran ci-dessous. Vous pouvez essayer l'une des méthodes suivantes pour le faire.

3.2.1 Extraire du texte entre deux mots avec 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

=MID(cell,SEARCH("start_word",cell)+3,SEARCH("end_word",cell)-SEARCH("start_word",cell)-4)

Arguments

Sélectionnez une cellule vide, copiez ou entrez la formule ci-dessous et appuyez sur le bouton Entrer clé pour obtenir le résultat. Sélectionnez cette cellule de résultat, puis faites glisser sa poignée de remplissage automatique vers le bas pour appliquer cette formule à d'autres cellules.

=MID(B5,SEARCH("KTE",B5)+3,SEARCH("feature",B5)-SEARCH("KTE",B5)-4)

Notes: Dans la formule, le chiffre 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 voir 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 du texte entre deux mots avec un outil incroyable

Pour de nombreux utilisateurs d'Excel, les formules peuvent être difficiles à mémoriser et à gérer. Ici, avec le Extraire les chaînes entre le texte spécifié caractéristique de Kutools for Excel, vous pouvez facilement extraire du texte entre deux mots en quelques clics.

1. Sélectionnez une cellule pour afficher le résultat, puis cliquez sur Kutools > Aide à la formule > Assistant de formule.

2. dans le Aide à la formule boîte de dialogue, vous devez configurer comme suit.

3. Ensuite, toutes les chaînes de texte entre deux mots "KTE" et "fonctionnalité" 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 remplissage automatique vers le bas pour extraire les textes des autres cellules de la même liste.

Extraire les nombres par position

Pour une liste de chaînes alphanumériques, il peut y avoir trois cas :

1. Le numéro est au début du texte;
2. Le numéro est à la fin du texte;
3. Le numéro peut être n'importe où dans le texte.

Dans cette section, nous fournirons 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

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

=LEFT(cell, MATCH(FALSE, ISNUMBER(MID(cell, ROW(INDIRECT("1:"&LEN(cell)+1)), 1) *1), 0) -1)

Arguments

Remarque:

Sélectionnez une cellule vide, entrez la formule ci-dessous et appuyez sur Ctrl + Shift + Entrer or Entrer clé pour obtenir le résultat. Sélectionnez cette cellule de résultat, puis faites glisser sa poignée de remplissage automatique vers le bas pour obtenir le nombre d'autres cellules.

=LEFT(B5, MATCH(FALSE, ISNUMBER(MID(B5, ROW(INDIRECT("1:"&LEN(B5)+1)), 1) *1), 0) -1)

Notes:

2 Extraire le nombre à droite d'une chaîne

Comme le montre la capture d'écran ci-dessous, pour extraire uniquement les nombres qui apparaissent après le texte dans une cellule, essayez la formule suivante.

Formule générique

=RIGHT(cell, LEN(cell) - MAX(IF(ISNUMBER(MID(cell, ROW(INDIRECT("1:"&LEN(cell))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(cell))), 0)))

Arguments

Remarque:

Sélectionnez une cellule vide, entrez la formule ci-dessous et appuyez sur Ctrl + Shift + Entrer or Entrer clé pour obtenir le résultat. Sélectionnez cette cellule de résultat, puis faites glisser sa poignée de remplissage automatique vers le bas pour obtenir le nombre d'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)))

Notes:

3. Extraire tous les nombres de n'importe quelle position dans une chaîne de texte

Les méthodes ci-dessus aident à extraire le nombre uniquement à partir de la gauche ou de la droite d'une chaîne de texte. Si vous souhaitez extraire tous les nombres de n'importe où dans une chaîne de texte, nous vous proposons ici trois méthodes pour le faire.

3.1 Extraire tous les nombres de n'importe où dans une chaîne avec une formule

Vous pouvez appliquer la formule suivante pour extraire tous les nombres de 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 le bouton Entrer pour obtenir 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 remplissage automatique vers le bas pour obtenir tous les nombres d'autres cellules.

3.2 Extraire tous les nombres de n'importe où dans une chaîne avec VBA

La formule ci-dessus est trop longue et compliquée pour de nombreux utilisateurs d'Excel. En fait, vous pouvez exécuter un script VBA pour automatiser la tâche dans Excel. Vous pouvez procéder comme suit.

1. appuie sur le autre + F11 clés pour ouvrir le Microsoft Visual Basic pour applications fenêtre.

2. Dans l'ouverture Microsoft Visual Basic pour applications fenêtre, cliquez sur insérer > Module. Copiez ensuite le VBA ci-dessous dans la fenêtre 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 Sub

3. appuie sur le F5 clé pour exécuter le code. Dans l'ouverture KutoolsforExcel boîte de dialogue, sélectionnez la plage de cellules dans laquelle vous souhaitez extraire tous les nombres de chaque cellule, puis cliquez sur le bouton OK .

4. Puis un autre KutoolsforExcel boîte de dialogue apparaît. Dans cette boîte de dialogue, sélectionnez une cellule de destination et cliquez sur D'ACCORD.

Ensuite, tous les nombres sont extraits de chaque cellule de la plage sélectionnée en bloc.

4. Extraire des nombres après un texte spécifique

Comme le montre la capture d'écran ci-dessous, pour extraire des nombres après le texte spécifique « Non », cette section fournit deux méthodes pour vous aider à le faire.

4.1 Extraire des 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:

=LOOKUP(10^6,1*MID(cell,MIN(FIND({0,1,2,3,4,5,6,7,8,9},cell&"0123456789",FIND("text"," "&cell&" "))),{2,3,4,5,6}))

Arguments

Sélectionnez une cellule vide, copiez ou entrez la formule ci-dessous et appuyez sur le bouton Entrer clé pour obtenir le résultat. Sélectionnez cette cellule de résultat, puis faites glisser sa poignée de remplissage 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}))

Notes:

4.2 Extraire des nombres après un texte spécifique avec une fonction définie par l'utilisateur

La fonction définie par l'utilisateur suivante peut également aider à extraire des nombres après un texte spécifique dans une cellule. Veuillez procéder comme suit.

1. appuie sur le autre + F11 clés pour ouvrir le Microsoft Visual Basic pour applications fenêtre.

2. dans le Microsoft Visual Basic pour applications fenêtre, cliquez sur insérer > Module, puis copiez le code VBA ci-dessous dans la fenêtre Code du module.

Code VBA : extraire des 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. appuie sur le autre + Q touches pour fermer le Microsoft Visual Basic pour applications fenêtre.

4. Sélectionnez une cellule, entrez la formule ci-dessous et appuyez sur la Entrer clé. Sélectionnez cette cellule de résultat, puis faites glisser sa poignée de remplissage automatique vers le bas pour appliquer cette formule à d'autres cellules.

=GetNumberAfterTheChar(B5,"No. ")

Notes:

Articles Liés: