Extraction de nombres de chaînes alphanumériques


Icône MVP

Cet article a été écrit par Ashish Mathur, MVP (Most Valuable Professional) Microsoft. Visitez le site Web MVP Microsoft pour plus d'informations.

Dans l'article suivant, j'explique une formule qui extrait des nombres des chaînes dans les situations suivantes :

  • lorsque des caractères alphabétiques et numériques sont groupés, comme abc123 ou 678sfr ;
  • lorsque des caractères alphabétiques et numériques ne sont pas groupés, comme abc15tni.

Problème

Comment extraire la partie numérique d'une chaîne alphanumérique. Par exemple : si la cellule A1 contient la chaîne « abc123 », comment renvoyer la valeur 123 dans la cellule B1.

Solution

Ici, la logique sous-jacente est de rechercher et de retourner le premier nombre de la chaîne alphanumérique, puis de retourner uniquement les nombres qui suivent.

Algorithme

La solution implique la création de formules pour accomplir les tâches suivantes :

  1. décomposer la chaîne alphanumérique en caractères séparés ;
  2. déterminer si la chaîne décomposée contient un nombre ;
  3. déterminer la position du nombre dans la chaîne alphanumérique ;
  4. compter les nombres dans la chaîne alphanumérique.

Nous prendrons en compte chacune de ces tâches séparément, puis nous intègrerons les formules pour obtenir le résultat final.

Décomposer la chaîne alphanumérique en caractères séparés

Utilisez ici la fonction STXT. Cette fonction renvoie un nombre déterminé de caractères d'une chaîne de texte, à partir de la position que vous indiquez et en se basant sur le nombre de caractères que vous spécifiez. La syntaxe de la fonction est :

STXT(texte;no_départ;no_car)

  • Texte    Chaîne de texte contenant les caractères que vous souhaitez extraire.
  • No_départ    Position du premier caractère que vous souhaitez extraire du texte. Le premier caractère du texte correspond au no_départ 1, et ainsi de suite.
  • No_car    Spécifie le nombre de caractères que vous souhaitez que la fonction STXT renvoie du texte.

Pour notre exemple, la formule est :

=STXT(A1;LIGNE($1:$9);1)

Cette formule décompose la chaîne alphanumérique et place  — virtuellement — les caractères dans des lignes différentes de la feuille de calcul. Par exemple, pour la chaîne alphanumérique abc123, les 6 caractères seront séparés.

 Remarque   Le nombre 9 peut être augmenté si besoin à un nombre supérieur pour satisfaire des chaînes plus longues. Dans cet exemple, la longueur maximale de la chaîne est de 9 caractères.

Il est également utile de noter que lorsque la chaîne est décomposée, « 1 », « 2 » et « 3 » sont traités comme du texte — pas comme des nombres. Pour convertir les nombres stockés comme du texte en nombres, multipliez la formule par 1. Par exemple :

=1*STXT(A1;LIGNE($1:$9);1)

Déterminer si la chaîne décomposée contient un nombre

Nous employons ici la fonction ESTNUM, qui détermine si la chaîne alphanumérique contient un nombre. La formule devient maintenant :

=ESTNUM(1*STXT(A1;LIGNE($1:$9);1))

Si la chaîne contient un nombre, le résultat est VRAI ; dans le cas contraire, le résultat est FAUX.

Déterminer la position du nombre dans la chaîne alphanumérique

Nous déterminons maintenant la position du nombre en localisant une valeur VRAI dans le résultat de la chaîne décomposée mentionnée dans le paragraphe précédent. Nous utilisons ici la fonction EQUIV. La formule révisée devient maintenant :

=EQUIV(VRAI;ESTNUM(1*STXT(A1;LIGNE($1:$9);1));0)

 Important   Vous devez entrer cette formule en tant que matrice en appuyant sur CTRL+MAJ+ENTRÉE.

Si la chaîne est abc123, le résultat de cette formule sera 4 — qui est la position du premier caractère numérique dans la chaîne alphanumérique.

Compter les nombres dans la chaîne alphanumérique

La tâche consiste maintenant à compter les nombres dans la chaîne, afin de déterminer les caractères qui doivent être renvoyés après le premier nombre de la chaîne alphanumérique.

Comme indiqué précédemment, les nombres stockés comme du texte dans la chaîne alphanumérique peuvent être convertis en nombres en les multipliant par 1. Par exemple : =1*STXT(A1;LIGNE($1:$9);1)

Lorsque les nombres stockés comme du texte ont été convertis en nombres, vous pouvez les compter en utilisant la fonction NB. Les nombres peuvent être comptabilisés en entrant la formule suivante :

=NB(1*STXT(A1;LIGNE($1:$9);1))

Intégrer les formules

Nous utilisons maintenant la fonction STXT pour intégrer plusieurs parties de la formule, comme le montre l'exemple suivant :

=STXT(A1;EQUIV(VRAI;ESTNUM(1*STXT(A1;LIGNE($1:$9);1));0);NB(1*STXT(A1;LIGNE($1:$9);1)))

Globalement, le problème peut maintenant se résumer de la façon suivante : déterminer la position du premier nombre dans la chaîne alphanumérique (dans la cellule A1) ; renvoyer ce nombre et les nombres qui suivent.

Pour convertir les caractères obtenus en nombres, multipliez la formule par 1. Bien que ce ne soit pas strictement nécessaire, vous devez le faire si vous effectuez ensuite des opérations mathématiques sur les résultats. La formule finale à entrer dans la cellule B1 est la suivante :

=1*STXT(A1;EQUIV(VRAI;ESTNUM(1*STXT(A1;LIGNE($1:$9);1));0);NB(1*STXT(A1;LIGNE($1:$9);1)))

 Important   Vous devez entrer ces formules en tant que matrices en appuyant sur CTRL+MAJ+ENTRÉE.

Plus d'exemples

Pour tester la formule supplémentaire, entrez les données de l'image suivante dans les cellules A1:A7 d'une feuille de calcul vierge.

Exemple de conversion d'une chaîne

Entrez la formule dans la cellule B1, puis utilisez la fonctionnalité Recopie incrémentée pour copier la formule dans les cellules B2:B7 (n'oubliez pas d'appuyer sur CTRL+MAJ+ENTRÉE).

Il est important de signaler ici que, si la chaîne est yur09875reew et que vous utilisez la formule multipliée par 1, le résultat dans la colonne B sera 9875 et non 09875. Dans la mesure où 0*1=0, le 0 est ignoré et le résultat renvoyé est 9875. Si vous souhaitez que le résultat soit 09875, ne multipliez pas la formule complète par 1.

 
 
S'applique à :
Excel 2003