Regarder la vidéo - Comment extraire des nombres d'une chaîne de texte dans Excel (à l'aide d'une formule et de VBA)
Il n'y a pas de fonction intégrée dans Excel pour extraire les nombres d'une chaîne dans une cellule (ou vice versa - supprimez la partie numérique et extrayez la partie texte d'une chaîne alphanumérique).
Cependant, cela peut être fait en utilisant un cocktail de fonctions Excel ou un simple code VBA.
Permettez-moi d'abord de vous montrer de quoi je parle.
Supposons que vous ayez un ensemble de données comme indiqué ci-dessous et que vous souhaitiez extraire les nombres de la chaîne (comme indiqué ci-dessous) :
La méthode que vous choisirez dépendra également de la version d'Excel que vous utilisez :
- Pour les versions antérieures à Excel 2016, vous devez utiliser des formules légèrement plus longues
- Pour Excel 2016, vous pouvez utiliser la nouvelle fonction TEXTJOIN
- La méthode VBA peut être utilisée dans toutes les versions d'Excel
Cliquez ici pour télécharger le fichier exemple
Extraire les nombres de la chaîne dans Excel (formule pour Excel 2016)
Cette formule ne fonctionnera que dans Excel 2016 car elle utilise la nouvelle fonction TEXTJOIN.
En outre, cette formule peut extraire les nombres qui se trouvent au début, à la fin ou au milieu de la chaîne de texte.
Notez que la formule TEXTJOIN couverte dans cette section vous donnera tous les caractères numériques ensemble. Par exemple, si le texte est « Le prix de 10 billets est de 200 USD », il vous donnera 10 200 comme résultat.Supposons que vous ayez l'ensemble de données comme indiqué ci-dessous et que vous souhaitiez extraire les nombres des chaînes de chaque cellule :
Vous trouverez ci-dessous la formule qui vous donnera une partie numérique d'une chaîne dans Excel.
=TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),""))
Ceci est une formule matricielle, vous devez donc utiliser 'Contrôle + Maj + Entrée' au lieu d'utiliser Entrée.
Au cas où il n'y aurait pas de nombres dans la chaîne de texte, cette formule renverrait un blanc (chaîne vide).
Comment fonctionne cette formule ?
Permettez-moi de casser cette formule et d'essayer d'expliquer comment cela fonctionne :
- ROW(INDIRECT(“1:”&LEN(A2))) - cette partie de la formule donnerait une série de nombres à partir de un. La fonction LEN dans la formule renvoie le nombre total de caractères dans la chaîne. Dans le cas de « Le coût est de 100 USD », il renverra 19. Les formules deviendraient ainsi ROW(INDIRECT(“1:19”). La fonction ROW renverra alors une série de nombres - {1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19}
- (MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1) - Cette partie de la formule renverrait un tableau de #VALUE ! erreurs ou nombres basés sur la chaîne. Tous les caractères de texte de la chaîne deviennent #VALUE ! les erreurs et toutes les valeurs numériques restent telles quelles. Cela se produit car nous avons multiplié la fonction MID par 1.
- IFERROR((MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1)*1),””) - Lorsque la fonction IFERROR est utilisée, elle supprimera tous les #VALUE ! erreurs et seuls les chiffres resteraient. La sortie de cette partie ressemblerait à ceci - {"";"";"";"";"";"";"";"";"";"";"";"";""; ””;””;””;1;0;0}
- =TEXTJOIN(“”,TRUE,IFERROR((MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1)*1),””)) - La fonction TEXTJOIN combine maintenant simplement les caractères de la chaîne qui reste (qui ne sont que des nombres) et ignore la chaîne vide.
Télécharger le fichier exemple
Vous pouvez également utiliser la même logique pour extraire la partie texte d'une chaîne alphanumérique. Vous trouverez ci-dessous la formule qui obtiendrait la partie texte de la chaîne :
=TEXTJOIN("",TRUE,SI(ESTERREUR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),MID(A2,ROW(INDIRECT("1:"&LEN (A2))),1),""))
Un changement mineur dans cette formule est que la fonction IF est utilisée pour vérifier si le tableau que nous obtenons de la fonction MID contient des erreurs ou non. S'il s'agit d'une erreur, il conserve la valeur sinon il la remplace par un blanc.
Ensuite, TEXTJOIN est utilisé pour combiner tous les caractères du texte.
Avertir: Bien que cette formule fonctionne très bien, elle utilise une fonction volatile (la fonction INDIRECT). Cela signifie que si vous l'utilisez avec un énorme ensemble de données, cela peut prendre un certain temps pour vous donner les résultats. Il est préférable de créer une sauvegarde avant d'utiliser cette formule dans Excel.Extraire les nombres de la chaîne dans Excel (pour Excel 2013/2010/2007)
Si vous avez Excel 2013. 2010. ou 2007, vous ne pouvez pas utiliser la formule TEXTJOIN, vous devrez donc utiliser une formule compliquée pour y parvenir.
Supposons que vous ayez un ensemble de données comme indiqué ci-dessous et que vous souhaitiez extraire tous les nombres de la chaîne dans chaque cellule.
La formule ci-dessous y parviendra :
=SI(SOMME(LEN(A2)-LEN(SUBSTITUT(A2, {"0","1","2","3","4","5","6","7"," 8","9"}, "")))>0, SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("$1:$"&LEN(A2)))), 1))* LIGNE(INDIRECT("$1:$"&LEN(A2))),0), LIGNE(INDIRECT("$1:$"&LEN(A2))))+1,1) * 10^LIGNE(INDIRECT ("$1:$"&LEN(A2)))/10),"")
Au cas où il n'y aurait pas de nombre dans la chaîne de texte, cette formule renverrait un blanc (chaîne vide).
Bien qu'il s'agisse d'une formule matricielle, vous pas besoin pour utiliser 'Control-Shift-Enter' pour l'utiliser. Une simple entrée fonctionne pour cette formule.
Le mérite de cette formule revient à l'incroyable forum Mr. Excel.
Encore une fois, cette formule extraira tous les nombres de la chaîne, quelle que soit la position. Par exemple, si le texte est « Le prix de 10 billets est de 200 USD », il vous donnera 10 200 comme résultat.
Avertir: Bien que cette formule fonctionne très bien, elle utilise une fonction volatile (la fonction INDIRECT). Cela signifie que si vous l'utilisez avec un énorme ensemble de données, cela peut prendre un certain temps pour vous donner les résultats. Il est préférable de créer une sauvegarde avant d'utiliser cette formule dans Excel.Séparer le texte et les nombres dans Excel à l'aide de VBA
Si vous devez souvent séparer le texte et les nombres (ou extraire des nombres du texte), vous pouvez également utiliser la méthode VBA.
Tout ce que vous avez à faire est d'utiliser un simple code VBA pour créer une fonction définie par l'utilisateur (UDF) personnalisée dans Excel, puis au lieu d'utiliser des formules longues et compliquées, utilisez cette formule VBA.
Laissez-moi vous montrer comment créer deux formules en VBA - une pour extraire des nombres et une pour extraire du texte d'une chaîne.
Extraire les nombres de la chaîne dans Excel (à l'aide de VBA)
Dans cette partie, je vais vous montrer comment créer la fonction personnalisée pour obtenir uniquement la partie numérique d'une chaîne.
Vous trouverez ci-dessous le code VBA que nous utiliserons pour créer cette fonction personnalisée :
Fonction GetNumeric(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1) Next i GetNumeric = fonction de fin de résultat
Voici les étapes pour créer cette fonction, puis l'utiliser dans la feuille de calcul :
- Allez dans l'onglet Développeur.
- Cliquez sur Visual Basic (Vous pouvez également utiliser le raccourci clavier ALT + F11)
- Dans le backend de l'éditeur VB qui s'ouvre, cliquez avec le bouton droit sur l'un des objets du classeur.
- Allez dans Insérer et cliquez sur Module. Cela insérera l'objet module pour le classeur.
- Dans la fenêtre Code du module, copiez et collez le code VBA mentionné ci-dessus.
- Fermez l'éditeur VB.
Maintenant, vous pourrez utiliser la fonction GetText dans la feuille de calcul. Puisque nous avons fait tout le gros du travail dans le code lui-même, tout ce que vous avez à faire est d'utiliser la formule =GetNumeric(A2).
Cela ne vous donnera instantanément que la partie numérique de la chaîne.
Notez que puisque le classeur contient maintenant du code VBA, vous devez l'enregistrer avec l'extension .xls ou .xlsm.
Télécharger le fichier exemple
Si vous devez souvent utiliser cette formule, vous pouvez également l'enregistrer dans votre classeur de macros personnelles. Cela vous permettra d'utiliser cette formule personnalisée dans l'un des classeurs Excel avec lesquels vous travaillez.
Extraire le texte d'une chaîne dans Excel (à l'aide de VBA)
Dans cette partie, je vais vous montrer comment créer la fonction personnalisée pour obtenir uniquement la partie texte d'une chaîne.
Vous trouverez ci-dessous le code VBA que nous utiliserons pour créer cette fonction personnalisée :
Fonction GetText(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If Not (IsNumeric(Mid(CellRef, i, 1))) Then Result = Result & Mid(CellRef, i, 1 ) Suivant i GetText = Fonction de fin de résultat
Voici les étapes pour créer cette fonction, puis l'utiliser dans la feuille de calcul :
- Allez dans l'onglet Développeur.
- Cliquez sur Visual Basic (Vous pouvez également utiliser le raccourci clavier ALT + F11)
- Dans le backend de l'éditeur VB qui s'ouvre, cliquez avec le bouton droit sur l'un des objets du classeur.
- Allez dans Insérer et cliquez sur Module. Cela insérera l'objet module pour le classeur.
- Si vous possédez déjà un module, double-cliquez dessus (inutile d'en insérer un nouveau si vous l'avez déjà).
- Si vous possédez déjà un module, double-cliquez dessus (inutile d'en insérer un nouveau si vous l'avez déjà).
- Dans la fenêtre Code du module, copiez et collez le code VBA mentionné ci-dessus.
- Fermez l'éditeur VB.
Maintenant, vous pourrez utiliser la fonction GetNumeric dans la feuille de calcul. Puisque nous avons fait tout le gros du travail dans le code lui-même, tout ce que vous avez à faire est d'utiliser la formule =GetText(A2).
Cela ne vous donnera instantanément que la partie numérique de la chaîne.
Notez que puisque le classeur contient maintenant du code VBA, vous devez l'enregistrer avec l'extension .xls ou .xlsm.
Si vous devez souvent utiliser cette formule, vous pouvez également l'enregistrer dans votre classeur de macros personnelles. Cela vous permettra d'utiliser cette formule personnalisée dans l'un des classeurs Excel avec lesquels vous travaillez.
Si vous utilisez Excel 2013 ou des versions antérieures et que vous n'avez pas
