Téléchargez l'exemple de classeur
Ce tutoriel va vous montrer comment rechercher et extraire un nombre à l'intérieur d'une chaîne de texte dans Excel et Google Sheets.
Rechercher et extraire le numéro de la chaîne
Parfois, vos données peuvent contenir des chiffres et du texte et vous souhaitez extraire les données numériques.
Si la partie numérique se trouve à droite ou à gauche de la chaîne, il est relativement facile de diviser le nombre et le texte . Cependant, si les nombres sont à l'intérieur de la chaîne, c'est-à-dire entre deux chaînes de texte, vous devrez utiliser une formule beaucoup plus compliquée (illustrée ci-dessous).
TEXTJOIN - Extraire des nombres dans Excel 2016+
Dans Excel 2016, la fonction TEXTJOIN a été introduite, qui peut extraire les nombres de n'importe où dans la chaîne de texte. Voici la formule :
| 1 | =TEXTJOIN("",TRUE,IFERROR((MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1)*1),"")) |
Voyons comment fonctionne cette formule.
Les fonctions ROW, INDIRECT et LEN renvoient un tableau de nombres correspondant à chaque caractère de votre chaîne alphanumérique. Dans notre cas, "Monday01Day" a 11 caractères, donc la fonction ROW renverra alors le tableau {1;2;3;4;5;6;7;8;9;10;11}.
| 1 | =TEXTJOIN("",TRUE,IFERROR((MID(B3, {1;2;3;4;5;6;7;8;9;10;11},1)*1),"")) |
Ensuite, la fonction MID extrait chaque caractère de la chaîne de texte, créant un tableau contenant votre chaîne d'origine :
| 1 | =TEXTJOIN("",TRUE,IFERROR(({"M";"o";"n";"d";"a";"y";"0";"1";"D";"a ";"y"}*1),"")) |
Multiplier chaque valeur du tableau par 1 créera un tableau contenant des erreurs Si le caractère du tableau était du texte :
| 1 | =TEXTJOIN("",TRUE,IFERROR(({#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;0;1;#VALUE!;#VALUE!;#VALUE !}),"")) |
Ensuite, la fonction IFERROR supprime les valeurs d'erreur :
| 1 | =TEXTJOIN("",TRUE,{"";"";"";"";"";"";0;1;"";"";""}) |
Ne laissant que la fonction TEXTJOIN qui relie les nombres restants.
Notez que la formule vous donnera tous les caractères numériques ensemble de votre chaîne. Par exemple, si votre chaîne alphanumérique est Monday01Day01, elle vous donnera comme résultat 0101.
Extraire des nombres - Avant Excel 2016
Avant Excel 2016, vous pouviez utiliser une méthode beaucoup plus compliquée pour extraire des nombres du texte. Vous pouvez utiliser la fonction FIND avec les fonctions SIERREUR et MIN pour déterminer à la fois la première position de la partie numérique et la première position de la partie texte après le numéro. Ensuite, nous extrayons simplement la partie numérique avec la fonction MID.
| 1 | =MID(B3,MIN(SIERREUR(TROUVE({0,1,2,3,4,5,6,7,8,9},B3),999999999)),MIN(SIERREUR(RECHERCHE({"a" ,"b","c","d","e","f","g","h","je","j","k","l","m"," n","o","p","q","r","s","t","u","v","w","x","y","z" },B3,MIN(SIERREUR(TROUVE({0,1,2,3,4,5,6,7,8,9},B3),999999999))),999999999))-MIN(SIERREUR(TROUVE( {0,1,2,3,4,5,6,7,8,9},B3),999999999))) |
Remarque : il s'agit d'une formule matricielle, vous devez entrer la formule en appuyant sur CTRL + MAJ + ENTRÉE, au lieu de simplement ENTRÉE.
Voyons étape par étape comment fonctionne cette formule.
Trouver le numéro de poing
Nous pouvons utiliser la fonction FIND pour localiser la position de départ du nombre.
| 1 | = MIN(SIERREUR(TROUVE({1,2,3,4,5,6,7,8,9,0},B3),999999999)) |
Pour l'argument find_text de la fonction FIND, nous utilisons la constante de tableau {0,1,2,3,4,5,6,7,8,9}, ce qui permet à la fonction FIND d'effectuer une recherche distincte pour chaque valeur dans la constante matricielle.
L'argument inside_text de la fonction FIND dans notre cas est Monday01Day, dans lequel 1 peut être trouvé à la position 8 et 0 à la position 7, donc notre tableau de résultats sera : {8,#VALUE,#VALUE,#VALUE, #VALEUR,#VALEUR,#VALEUR, #VALEUR, #VALEUR,7}.
En utilisant la fonction SIERREUR, nous remplaçons les erreurs #VALUE par 999999999. Ensuite, nous cherchons simplement le minimum dans ce tableau et obtenons donc la place du premier nombre (7).
Veuillez noter que la formule ci-dessus est une formule matricielle, vous devez appuyer sur Ctrl+Maj+Entrée pour la déclencher.
Trouver le premier caractère de texte après le nombre
De la même manière que pour localiser le premier nombre dans la chaîne, nous utilisons la fonction FIND pour déterminer où le texte commence à nouveau après le nombre en utilisant également l'argument start_num de la fonction.
| 1 | =MIN(IFERREUR(TROUVE({"a","b","c","d","e","f","g","h","I","j","k ","l","m","n","o","p","q","r","s","t","u","v","w", "x", "y", "z"},B3,C3),999999999)) |
Cette formule fonctionne de manière très similaire à la précédente utilisée pour localiser le premier nombre, nous utilisons uniquement des lettres dans notre constante de tableau et, par conséquent, les nombres provoquent des erreurs #VALUE. Veuillez noter que nous commençons la recherche uniquement après la position déterminée pour le premier nombre (ce sera l'argument start_num) et non à partir du début de la chaîne.
N'oubliez pas d'appuyer sur Ctrl+Maj+Entrée pour utiliser la formule ci-dessus.
Il ne reste plus qu'à mettre tout cela ensemble.
Extraire le numéro de deux textes
Une fois que nous avons la position de départ de la partie numérique et le début de la partie texte après cela, nous utilisons simplement la fonction MID pour extraire la partie numérique souhaitée.
| 1 | =MID(B3,C3,D3-C3) |
Autre méthode
Sans l'expliquer plus en détail, vous pouvez également utiliser la formule complexe ci-dessous pour obtenir le nombre à l'intérieur d'une chaîne.
| 1 | =SUMPRODUCT(MID(0&B3,LARGE(INDEX(ISNUMBER(--MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1))*LIGNE(INDIRECT("1:"&LEN(B3) )),0),LIGNE(INDIRECT("1:"&LEN(B3))))+1,1)*10^LIGNE(INDIRECT("1:"&LEN(B3)))/10) |
Veuillez noter que cette formule ci-dessus vous donnera 0 lorsqu'il n'y a pas de nombre dans la chaîne et que les zéros non significatifs seront omis.
Rechercher et extraire le nombre d'une chaîne en texte dans Google Sheets
Les exemples ci-dessus fonctionnent de la même manière dans Google Sheets que dans Excel.
