Comment extraire un nombre d'une chaîne de caractères dans Excel : Trouver la bonne approche

Lorsque vous travaillez dans Excel pour nettoyer des données, vous pouvez être amené à extraire des nombres de chaînes alphanumériques. Ce problème est fréquent si vous avez des données où les nombres sont intégrés dans des chaînes, comme des codes de produits ou des réponses à des enquêtes.

Dans cet article, je vais vous montrer les différentes méthodes pour extraire des nombres à partir de chaînes de caractères en utilisant des formules comme RIGHT(), LEN(), et FIND() pour différents cas d'utilisation. Si vous n'êtes pas familier avec certaines des bases, je vous recommande de suivre notre cours d'introduction à Excel afin de construire une base solide dans Excel. Vous pouvez également consulter notre rubrique Préparation des données dans Excel pour en savoir plus sur la transformation des données brutes.

Formules Excel pour extraire des nombres d'une chaîne de caractères

Excel propose différentes méthodes basées sur des formules pour extraire des nombres de chaînes de caractères. Chaque méthode est adaptée à des besoins différents en fonction de l'emplacement du numéro dans la chaîne. Si vous êtes pressé par le temps, n'hésitez pas à passer à la section qui répond à votre besoin le plus immédiat.

Extraire des nombres du début d'une chaîne de caractères dans Excel

Il peut arriver que vous rencontriez des scénarios dans lesquels les chiffres se trouvent au début de la chaîne. Dans ce cas, vous utiliserez LEFT() et FIND() pour extraire les chiffres de la chaîne. Lorsque vous combinez les fonctions dans une formule, les nombres sont extraits selon la logique suivante :

• FIND(): Recherche la position de la première lettre dans la chaîne de caractères.

• LEFT(): Extrait les caractères du début de la chaîne jusqu'à la position de la première lettre moins un.

La première partie de la formule ci-dessous recherche la première lettre de la chaîne. Ensuite, la deuxième partie extrait tout ce qui se trouve avant cette lettre, en ne laissant que les premiers chiffres. S'il n'y a pas de lettres, la chaîne entière est renvoyée.

=LEFT(A2, FIND(MID(A2, MIN(FIND({"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"}, A2&"ABCDEFGHIJKLMNOPQRSTUVWXYZ")), 1), A2) - 1)

Extraction de nombres au début d'une chaîne de caractères dans Excel. Image par l'auteur.

Cette méthode est appropriée lorsque vous travaillez avec des ensembles de données utilisant couramment des préfixes numériques tels que les numéros de commande, les identifiants des membres et les numéros de lot.

Extraire des nombres à la fin d'une chaîne de caractères dans Excel

Si vous souhaitez extraire des nombres de la fin d'une chaîne de caractères dans Excel, vous devez utiliser les fonctions RIGHT(), LEN(), et FIND(). Supposons que vous disposiez des données suivantes dans la feuille Excel. Vous combinerez les fonctions pour extraire le nombre où :

• FIND(): Recherche la position du premier chiffre dans la chaîne de caractères.

• LEN(): Calcule le nombre de caractères entre le premier chiffre et la fin de la chaîne.

• RIGHT(): Extrait les caractères du premier chiffre à la fin de la chaîne.

La formule ci-dessous supprime tout le texte de tête et renvoie le premier nombre ainsi que tout ce qui suit dans la chaîne.

=RIGHT(A2, LEN(A2) - MIN(FIND({0,1,2,3,4,5,6,7,8,9}, A2&"0123456789")) + 1)

Extraction de nombres à la fin d'une chaîne de caractères dans Excel. Image par l'auteur.

Cette méthode est particulièrement utile pour les ensembles de données contenant des caractères alphanumériques cohérents, tels que les UGS de produits, les identifiants de commande et les numéros de série.

Extraire des nombres à partir de n'importe quelle position dans une chaîne de caractères dans Excel

Si vous disposez d'un ensemble de données où les nombres apparaissent n'importe où dans la chaîne, vous aurez besoin de formules avancées utilisant TEXTJOIN(), MID(), et ROW() pour extraire les nombres.

La combinaison de ces fonctions dans une formule permet d'extraire un nombre d'une chaîne de caractères selon la logique suivante : 

• ROW() et MID(): Interroge chaque caractère de la chaîne pour déterminer s'il s'agit d'un caractère numérique.

• IFERROR(): Test logique pour isoler les numéros identifiés des autres caractères.

• TEXTJOIN(): Combinez tous les caractères numériques extraits en une seule chaîne de chiffres.

=TEXTJOIN("", TRUE, IF(ISNUMBER(--MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)), MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1), ""))

Extrait un nombre à partir de n'importe quelle position dans une chaîne de caractères. Image par l'auteur.

Cette méthode est la mieux adaptée lorsque votre ensemble de données contient des caractères alphanumériques avec des nombres apparaissant n'importe où dans la chaîne. Notez que ces fonctions ne sont pas toutes disponibles dans les anciennes versions d'Excel. Consultez notre cursus de compétences " Excel Fundamentals " pour en savoir plus sur les fonctions et formules disponibles dans les dernières versions d'Excel.

Extraction de nombres par filtrage des caractères indésirables

Parfois, vos données sont désordonnées. Heureusement, vous pouvez combiner les fonctions SUBSTITUTE() et TEXTJOIN(). Il vous suffit de suivre les étapes suivantes :

• SUBSTITUTE(): La fonction SUBSTITUTE() peut être utilisée pour remplacer tous les caractères non numériques d'une chaîne par un délimiteur, tel qu'un espace ou une chaîne vide. Par exemple, vous pouvez remplacer les lettres et les caractères spéciaux par des espaces.

• TEXTJOIN(): La fonction TEXTJOIN() peut alors être utilisée pour concaténer les caractères restants (qui ne devraient plus être que des nombres) en une seule chaîne.

=TEXTJOIN("", TRUE, IF(ISNUMBER(--MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)), MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1), ""))

Extraction d'un nombre à partir d'une chaîne de caractères en filtrant les caractères. Image par l'auteur.

Quand vous avez besoin d'extraire des nombres d'une chaîne de caractères dans Excel

Examinons maintenant deux petites études de cas pour nous entraîner à extraire des nombres d'une chaîne de caractères.

Exemple 1 : Extraction de numéros à partir de codes de produits

Supposons que vous gériez un stock de produits dont chaque article est identifié par un code produit unique. Ces codes produits sont des chaînes alphanumériques telles que "PROD1234", "ITEM5678" ou "SKU91011".

Ainsi, pour extraire l'ID produit numérique des codes produits alphanumériques, nous utilisons des fonctions basées sur la position du numéro. N'oubliez pas que si les chiffres sont à la fin, appliquez RIGHT(), LEN(), et FIND(). S'ils apparaissent au début, utilisez LEFT() et FIND().

L'exemple ci-dessous extrait la partie numérique des codes de produits du côté droit du code.

=RIGHT(A1, LEN(A1) - FIND("-", SUBSTITUTE(A1, MID(A1, MIN(FIND({"0","1","2","3","4","5","6","7","8","9"}, A1&"0123456789")), 1), "-")))

Extraction de numéros à partir de codes de produits dans Excel. Image par l'auteur.

Exemple 2 : Données d'enquête avec numéros intégrés

Les données d'enquête contiennent parfois à la fois du texte et des chiffres tels que "note de 5 sur 10", "score" : 8" ou "3 étoiles". Nous devrons effectuer un certain travail avant de pouvoir calculer des moyennes ou d'identifier des tendances.

Comme les chiffres peuvent apparaître n'importe où dans le texte, une formule dynamique utilisant TEXTJOIN(), MID(), et ROW() est nécessaire.

=TEXTJOIN("", TRUE, IFERROR(MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1) * (ISNUMBER(--MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1))), ""))

Extraction de nombres à partir de n'importe quelle position dans Excel. Image par l'auteur.

Exemples de codes copiés-collés pour des cas particuliers

Pour la dernière section, j'ai voulu vous donner plusieurs formules supplémentaires qui traitent des scénarios différents et spécifiques. N'hésitez pas à copier et à coller, et j'espère que cela résoudra les problèmes spécifiques que vous pourriez rencontrer. 

Extraire le premier bloc contigu de chiffres

Si votre chaîne contient plusieurs séquences numériques mais que vous n'avez besoin que du premier groupe, essayez cette solution en utilisant les fonctions LET() et SEQUENCE():

=LET(
txt, A2,
pos, MATCH(TRUE, ISNUMBER(--MID(txt, SEQUENCE(LEN(txt)), 1)), 0),
len, MATCH(FALSE, ISNUMBER(--MID(txt, SEQUENCE(LEN(txt)-pos+1)+pos-1, 1)), 0) - 1,
VALUE(MID(txt, pos, len))
)

Extraction du premier bloc contigu d'une chaîne de caractères dans Excel. Image par l'auteur.

Numéros d'extraits entre parenthèses

Pour les cas où les nombres apparaissent entre parenthèses, utilisez :

=MID(A2, FIND("(", A2)+1, FIND(")", A2)-FIND("(", A2)-1)

Extraire les nombres entre parenthèses dans Excel. Image par l'auteur.

Extraire les chiffres après un caractère spécifique

Lorsque vos données utilisent les deux points comme délimiteurs (par exemple, "Score : 8"), capturez les chiffres avec :

=TRIM(MID(A2, FIND(":", A2)+1, LEN(A2)))

Extraire un nombre après un caractère spécial dans Excel. Image par l'auteur.

Cela permet de tirer tout ce qui se trouve après le côlon. Enveloppez le résultat avec VALUE() si vous avez besoin d'une valeur numérique. Remplacez les deux points si vous avez un autre délimiteur.

Extraire tous les nombres et les combiner en une seule valeur

Pour supprimer tous les caractères non numériques et combiner tous les caractères numériques d'une chaîne, utilisez cette méthode :

=TEXTJOIN("", TRUE, IFERROR(MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)*1, ""))

Extraire tous les nombres et les combiner en une seule valeur dans Excel. Image par l'auteur.

Conseil : Dans les versions plus anciennes d'Excel, saisissez cette formule sous forme de tableau (Ctrl+Shift+Entrée).

Extraire des nombres en incluant les points décimaux

Pour les chaînes numériques pouvant inclure une décimale (par exemple, "Prix : 45.99"), utilisez cette formule pour conserver les chiffres et la virgule :

=TEXTJOIN("", TRUE, IFERROR(
  IF(MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)=".", ".",
     IF(ISNUMBER(--MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)),
        MID(A2, ROW(INDIRECT("1:" & LEN(A1))), 1),
        "")
  ),""))

Extraire des nombres avec des points décimaux dans Excel. Image par l'auteur.

Note : Cette formule suppose qu'il n'y a qu'une seule décimale.

Conclusion

L'extraction de nombres à partir de chaînes de caractères est une technique importante pour le nettoyage des données. En tant qu'analyste de données, je vous encourage à apprendre à appliquer les différentes formules présentées dans cet article.

Si vous souhaitez améliorer vos compétences en Excel, je vous recommande de suivre nos cours Analyse des données dans Excel et Visualisation des données dans Excel pour maîtriser l'analyse et la présentation des données. Essayez également notre cours de modélisation financière dans Excel qui peut vous ouvrir de nombreuses portes.