Comment extraire un nombre d'une chaîne dans Excel

Comment

ParAdam Davidson

Publié7 juin 2023

Excel est un tableur puissant, mais il ne peut pas faire de miracles. Si vous importez des données et que ces données sont en désordre, Excel ne pourra pas en faire grand-chose. Par exemple, si vous avez des valeurs qui sont attachées à d'autres caractères dans le cadre d'une chaîne, vous ne pourrez pas effectuer de calculs à moins que vous ne les extrayiez d'abord.

Heureusement, il est possible de faire exactement cela, bien que cela puisse être plus compliqué que vous ne le pensez au premier abord. Voici comment extraire un nombre d'une chaîne dans Excel.

Comment extraire un nombre de la fin d'une chaîne à l'aide de Flash Fill

Un moyen simple et rapide d'extraire un nombre à la fin d'une chaîne consiste à utiliser Excel Remplissage instantané fonctionnalité. Cette fonctionnalité vous permet de remplir rapidement une plage de cellules en fonction du modèle détecté dans une cellule spécifique. Un inconvénient de cette méthode est qu'elle vous oblige à faire la première extraction vous-même et si vous ne voulez extraire qu'un nombre d'une seule chaîne, cela ne sera d'aucune utilité.

Dans la plupart des cas, Flash Fill peut extraire correctement le nombre à la fin d'une chaîne, bien qu'il ne soit pas infaillible, vous devez donc toujours vérifier attentivement les résultats. Si cela ne fonctionne pas correctement, essayez de remplir manuellement les deux ou trois premières cellules plutôt que la première.

Pour extraire un nombre à la fin d'une chaîne à l'aide de Flash Fill :

1. Sélectionnez la cellule à côté de la première cellule contenant vos chaînes.
   
2. Entrez le numéro qui apparaît à la fin de la chaîne.
   
3. Si toutes vos données se trouvent dans une colonne continue sans espace, sélectionnez la cellule directement en dessous de celle dans laquelle vous venez d'entrer le nombre.
   
4. Si vos données comportent des lacunes dans certaines lignes, sélectionnez la première cellule et toutes les cellules en dessous desquelles vous souhaitez extraire des nombres, y compris les lignes vides.
   
5. Presse Ctrl+E sous Windows ou Cmd+E sur Mac pour exécuter la fonction Flash Fill.
6. Vos cellules restantes seront automatiquement remplies en fonction du modèle repéré par Excel. Dans la plupart des cas, les nombres à partir de la fin de la chaîne seront extraits correctement.
   

Comment extraire un nombre de la fin d'une chaîne à l'aide d'une formule

L'un des inconvénients de l'utilisation de la méthode Flash Fill est qu'elle vous oblige à extraire vous-même les nombres de la chaîne pour la première ligne. Il est possible d'extraire un nombre de la fin d'une chaîne à l'aide d'une formule, bien que la formule soit assez complexe.

Pour extraire un nombre à la fin d'une chaîne à l'aide d'une formule :

1. Cliquez dans la cellule où vous souhaitez que le nombre extrait apparaisse.
2. Saisissez la formule suivante en remplaçant "B2" par la cellule contenant votre chaîne :

   =VALEUR(DROITE(B2,LEN(B2)-MAX(SI(ESTNUM(MID(B2,LIGNE(INDIRECT("1:"&LEN(B2))),1)*1)=FAUX, LIGNE(INDIRECT(" 1 :"&NBCAR(B2))),0))))

3. Presse Entrer.
4. Le nombre doit être extrait de la chaîne.
5. Pour appliquer la formule aux cellules restantes, cliquez sur la poignée de glissement dans le coin inférieur droit de la cellule.
   
6. Faites glisser vers le bas sur les cellules où vous souhaitez que la formule soit répliquée.
   
7. Relâchez votre souris et la formule sera copiée dans les cellules sélectionnées.
8. Ces cellules devraient maintenant contenir vos numéros extraits.
   
9. Toutes les cellules sans chaîne à extraire renverront une erreur. Si vous souhaitez plutôt renvoyer une cellule vide, vous pouvez à la place utiliser la formule :

   =SIERREUR(VALEUR(DROITE(B2,LEN(B2)-MAX(SI(ESTNUM(MID(B2,LIGNE(INDIRECT("1:"&LEN(B2))),1)*1)=FAUX, LIGNE(INDIRECT ("1 :"&NBCAR(B2))),0)))),"")

10. Toutes les cellules vides renverront désormais un espace vide.
    

Vous vous demandez peut-être à quoi sert cette formule. En bref, il génère un tableau de la même longueur que la chaîne. Il place chacun des caractères de la chaîne dans ce tableau dans l'ordre. Il évalue ensuite chacun de ces caractères pour voir s'il s'agit d'un nombre ou non. Tous les caractères qui sont des nombres sont convertis en zéro. Tous les caractères qui ne sont pas des nombres sont convertis en un nombre représentant sa position dans la chaîne.

Par exemple, AB5HG23 serait converti en 1, 2, 0, 4, 5, 0, 0. Il recherche ensuite la valeur la plus élevée dans cette liste, qui est la position du dernier caractère qui n'est pas un nombre. Enfin, il renvoie tous les caractères après ce point, qui sont les nombres à la fin de la chaîne. La fonction VALUE le reconvertit ensuite en nombre. Simple, non ?

Comment extraire un nombre du début d'une chaîne à l'aide de Flash Fill

Vous pouvez également utiliser Flash Fill pour extraire un nombre du début d'une chaîne en remplissant vous-même le premier exemple et en permettant à Excel d'arrêter le modèle.

Pour extraire un nombre du début d'une chaîne à l'aide de Flash Fill :

1. Sélectionnez la cellule à droite de la première chaîne contenant un nombre que vous souhaitez extraire.
2. Entrez le nombre qui apparaît au début de la chaîne.
   
3. Si toutes vos données se trouvent dans une colonne continue sans espace, sélectionnez la cellule directement en dessous.
   
4. Si vos données comportent des lacunes, sélectionnez la première cellule et toutes les cellules en dessous desquelles vous souhaitez extraire des nombres, y compris les lignes vides.
   
5. Presse Ctrl+E sous Windows ou Cmd+E sur Mac pour démarrer Flash Fill.
6. Les cellules que vous avez sélectionnées doivent maintenant afficher les nombres extraits.
   

Comment extraire un nombre du début d'une chaîne à l'aide d'une formule

Vous pouvez également utiliser une formule pour extraire un nombre du début d'une chaîne. Cela fonctionne de la même manière que la formule de la section précédente, mais trouve la position du premier caractère non numérique et renvoie tous les caractères précédents.

Pour extraire un nombre du début d'une chaîne à l'aide d'une formule :

1. Sélectionnez la cellule dans laquelle vous souhaitez que le nombre extrait apparaisse.
2. Saisissez la formule suivante en remplaçant "B18" par la cellule contenant votre chaîne :

   =VALEUR(GAUCHE(B18,CONCORDANCE(FAUX, ESTNUM(MID(B18,LIGNE(INDIRECT("1:"&LEN(B18)+1)),1)*1),0)-1))

3. Presse Entrer et le numéro sera extrait.
   
4. Tu peux utiliser la saisie automatique dans Excel pour copier la formule dans toutes les autres cellules dont vous avez besoin.
5. Si vous souhaitez que des lignes vides renvoient un résultat vide, utilisez plutôt la formule suivante :

   =SIERREUR(VALEUR(GAUCHE(B18,CONCORDANCE(FAUX, ESTNUM(MID(B18,LIGNE(INDIRECT("1:"&LEN(B18)+1)),1)*1),0)-1))," ")

Comment extraire des nombres de n'importe où dans une chaîne à l'aide de Flash Fill

Comme mentionné précédemment, Flash Fill peut ne pas fonctionner si la première cellule ne correspond pas au format de certaines des autres cellules. Dans ce cas, vous devrez peut-être calculer manuellement deux ou trois cellules avant que Flash Fill vous donne le résultat dont vous avez besoin.

Pour extraire des nombres n'importe où dans une chaîne à l'aide de Flash Fill :

1. Dans la cellule à côté de la première chaîne, tapez manuellement les nombres qui se trouvent dans la chaîne.
   
2. Si toutes vos données se trouvent dans une colonne continue, sélectionnez la cellule directement en dessous.
   
3. Si vos données comportent des lacunes, sélectionnez la première cellule et toutes les cellules en dessous desquelles vous souhaitez extraire des nombres, y compris les lignes vides.
   
4. Presse Ctrl+E sous Windows ou Cmd+E sur Mac pour démarrer Flash Fill.
5. Les cellules doivent maintenant se remplir avec les nombres extraits.
   

Comment extraire des nombres de n'importe où dans une chaîne à l'aide d'une formule

Vous pouvez utiliser une formule pour extraire tous les nombres d'une chaîne, peu importe où ils apparaissent.

Pour extraire des nombres n'importe où dans une chaîne à l'aide d'une formule :

1. Sélectionnez la cellule dans laquelle vous souhaitez que le nombre extrait apparaisse.
2. Saisissez la formule suivante en remplaçant "B9" par la cellule contenant votre chaîne :

   =SOMMEPROD(MID(0&B9,LARGE(INDEX(ISNUMBER(--MID(B9,LIGNE(INDIRECT("1:"&LEN(B9))),1))*LIGNE(INDIRECT("1:"&LEN(B9) )),0),LIGNE(INDIRECT("1 :"&NBCAR(B9))))+1,1)*10^LIGNE(INDIRECT("1 :"&NBCAR(B9)))/10)

3. Votre formule doit extraire tous les nombres de la chaîne.
   
4. Si vous souhaitez que les cellules vides renvoient une cellule vide plutôt que zéro, utilisez plutôt cette formule :

   =SIERREUR(SOMMEPROD(MID(0&B9,LARGE(INDEX(ESTNUMÉRO(--MID(B9,LIGNE(INDIRECT("1 :"&LEN(B9))),1))*LIGNE(INDIRECT("1 :"&LEN( B9))),0),LIGNE(INDIRECT("1 :"&NBCAR(B9))))+1,1)*10^LIGNE(INDIRECT("1 :"&NBCAR(B9)))/10)," ")

5. Tu peux utiliser la saisie automatique pour appliquer la formule à toutes les autres cellules de votre feuille de calcul.
6. Toutes les chaînes qui ne contiennent aucun nombre renverront un zéro. Vous pouvez apprendre à convertir un zéro en un tiret dans Excel si vous voulez changer cela.

Développez vos compétences Excel

Apprendre à extraire un nombre d'une chaîne dans Excel peut vous aider à extraire rapidement les données dont vous avez besoin à partir d'une liste de chaînes. Bien que les formules requises puissent être complexes, vous n'avez pas besoin de comprendre exactement comment elles fonctionnent; juste qu'ils le font!

Il existe des astuces Excel plus utiles que vous pouvez apprendre pour faire passer vos compétences au niveau supérieur. Tu peux apprendre comment compter les cellules avec du texte en eux, ou comment calculer l'âge d'une personne à partir de sa date de naissance. Vous pouvez même créer un générateur de nombres aléatoires dans Excel.