Il n'y a pas de fonction intégrée dans Excel pour extraire des nombres d'une chaîne dans une cellule (et vice versa - supprimer la partie numérique et extraire la partie texte d'une chaîne alphanumérique).
Cependant, cela peut être fait en utilisant une fonction Excel ou un simple code VBA.
Permettez-moi d'abord de vous dire de quoi je parle.
Supposons que vous ayez un ensemble de données comme ci-dessous et que vous souhaitiez extraire des nombres de chaînes (comme ci-dessous):
La méthode que vous choisissez dépend également de la version d'Excel que vous utilisez :
- Pour les versions d'Excel antérieures à 2016, vous devez utiliser une formule légèrement plus longue
- Pour Excel 2016, vous pouvez utiliser la nouvelle fonction TEXTJOIN
- Méthode VBA disponible pour toutes les versions d'Excel
Table des matières
Extraire des nombres de chaînes dans Excel (formules Excel 2016)
Cette formule ne fonctionne que dans Excel 2016 car elle utilise la fonction TEXTJOIN nouvellement introduite.
De plus, cette formule peut extraire des nombres au début, à la fin ou au milieu d'une chaîne de texte.
Notez que la formule TEXTJOIN décrite dans cette section rassemble tous les caractères numériques.Par exemple, si le texte est « 10 billets coûtent 200 $ », le résultat est 10200.
Supposons que vous disposiez d'un ensemble de données comme celui-ci et que vous souhaitiez extraire des nombres à partir de chaînes dans chaque cellule :
La formule ci-dessous vous donnera la 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.
Cette formule renverra un blanc (chaîne vide) s'il n'y a pas de nombres dans la chaîne de texte.
Comment fonctionne cette formule ?
Permettez-moi de décomposer cette formule et d'essayer d'expliquer comment cela fonctionne:
- ROW(INDIRECT(“1:”&LEN(A2))) – Cette partie de la formule donnera 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.在“成本為 100 美元”的情況下，它將返回 19。因此，公式將變為 ROW(INDIRECT(“1:19”)。然後 ROW 函數將返回一系列數字 – {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 renverra un tableau de #VALUES !Erreurs ou nombres basés sur des chaînes.Tous les caractères de texte de la chaîne deviennent #VALEUR !Les erreurs et toutes les valeurs restent telles quelles.Cela se produit lorsque nous multiplions la fonction MID par 1.
- IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),"") – Lors de l'utilisation de la fonction IFERROR, toutes les #VALUES sont supprimées !Erreur, seuls les chiffres resteront.La sortie de cette partie ressemble à 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 chaîne Toujours là (uniquement des nombres) et ignore les chaînes vides.
Conseil de pro :Si vous souhaitez vérifier la sortie d'une partie de la formule, sélectionnez la cellule, appuyez sur F2 pour passer en mode édition, sélectionnez la partie de la formule que vous souhaitez générer et appuyez sur F9.Vous verrez les résultats immédiatement.N'oubliez pas d'appuyer sur Ctrl + Z ou appuyez sur Échap.N'appuyez pas sur Entrée.
Vous pouvez également utiliser la même logique pour extraire des parties de texte à partir de chaînes alphanumériques.Voici la formule pour obtenir la partie texte de la chaîne :
=TEXTJOIN("",TRUE,IF(ISERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),MID(A2,ROW(INDIRECT("1:"&LEN) (A2))),1),""))
Un petit changement à cette formule est que la fonction IF est utilisée pour vérifier le tableau que nous obtenons de la fonction MID pour les erreurs.S'il s'agit d'une erreur, conservez la valeur, sinon remplacez-la par un blanc.
Utilisez ensuite TEXTJOIN pour combiner tous les caractères de texte.
注意: Bien que cette formule soit valide, 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 des résultats.Il est préférable de créer une sauvegarde avant d'utiliser cette formule dans Excel.
Extraire des nombres de chaînes dans Excel (pour Excel 2013/2010/2007)
Si vous avez Excel 2013. 2010. ou 2007, vous ne pouvez pas utiliser les formules TEXTJOIN, vous devez donc utiliser des formules complexes pour ce faire.
Supposons que vous ayez un ensemble de données comme ci-dessous et que vous souhaitiez extraire tous les nombres dans les chaînes de chaque cellule.
La formule ci-dessous fera cela:
=IF(SUM(LEN(A2)-LEN(SUBSTITUTE(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))* ROW(INDIRECT("$1:$"&LEN(A2))),0), ROW(INDIRECT("$1:$"&LEN(A2))))+1,1) * 10^ROW(INDIRECT ("$1:$"&LEN(A2)))/10),"")
Cette formule renverra un blanc (chaîne vide) s'il n'y a pas de nombres dans la chaîne de texte.
Bien qu'il s'agisse d'une formule matricielle, vousPas besoinUtilisez "Control-Shift-Enter" pour l'utiliser.Une entrée simple fonctionne pour cette formule.
Le mérite de cette formule revient à l'incroyableM. Excel Forum.
Encore une fois, cette formule extraira tous les nombres de la chaîne, quelle que soit leur position.Par exemple, si le texte est « 10 billets coûtent 200 $ », le résultat est 10200.
注意: Bien que cette formule soit valide, 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 des résultats.Il est préférable de créer une sauvegarde avant d'utiliser cette formule dans Excel.
Séparez le texte et les nombres dans Excel avec VBA
Si vous avez fréquemment besoin de séparer du texte et des nombres (ou d'extraire des nombres du texte), vous pouvez également utiliser la méthode VBA.
Tout ce que vous avez à faire est de créer une fonction personnalisée définie par l'utilisateur (UDF) dans Excel à l'aide d'un code VBA simple, puis d'utiliser cette formule VBA au lieu d'une formule longue et complexe.
Laissez-moi vous montrer comment créer deux formules dans VBA - une pour extraire des nombres et une pour extraire du texte à partir de chaînes.
Extraire des nombres de chaînes dans Excel (à l'aide de VBA)
Dans cette partie, je vais vous montrer comment créer une fonction personnalisée pour obtenir uniquement la partie numérique d'une chaîne.
Voici 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 une feuille de calcul :
Vous pourrez maintenant utiliser la fonction GetText sur 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 vous donnera immédiatement la partie numérique de la chaîne.
Notez que puisque le classeur contient maintenant du code VBA, vous devrez l'enregistrer avec une extension .xls ou .xlsm.
Si vous devez utiliser cette formule fréquemment, vous pouvez également l'enregistrer dans votre classeur de macros personnel.Cela vous permettra d'utiliser cette formule personnalisée dans n'importe quel classeur Excel que vous utilisez.
Extraire le texte de la chaîne dans Excel (à l'aide de VBA)
Dans cette partie, je vais vous montrer comment créer une fonction personnalisée pour obtenir uniquement la partie texte d'une chaîne.
Voici 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 = Result End Function
Voici les étapes pour créer cette fonction, puis l'utiliser dans une feuille de calcul :
Vous pourrez maintenant 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 vous donnera immédiatement la partie numérique de la chaîne.
Notez que puisque le classeur contient maintenant du code VBA, vous devrez l'enregistrer avec une extension .xls ou .xlsm.
Si vous devez utiliser cette formule fréquemment, vous pouvez également l'enregistrer dans votre classeur de macros personnel.Cela vous permettra d'utiliser cette formule personnalisée dans n'importe quel classeur Excel que vous utilisez.
