Comment extraire un nombre à partir d’un texte automatiquement sur Excel ?

Dans ce tutoriel, je vais vous montrer comme créer une formule qui va nous permettre de convertir rapidement un texte en un nombre afin de pouvoir l’utiliser facilement dans un calcul !

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

Partie 1 : Comment extraire un nombre contenu dans un texte ?

Partie 2 : Comment extraire un ou plusieurs nombres (au début, au milieu ou à la fin)

1. Introduction

Comme nous l’avons vu dans l’introduction de ce tutoriel, nous allons voir comment analyser un texte contenant un uafin d’extraire celui-ci pour pouvoir l’exploiter dans un calcul.

Pour cela, nous allons partir de la petite liste de fruits et légumes suivantes :

Comme vous pouvez le constater, chaque élément de cette liste est placé sur la gauche des cellules, ce qui signifie que pour Excel, il s’agit de textes et non de nombres.

La raison est ici évidente : dès qu’Excel détecte au moins un caractère non numérique dans une cellule, alors il va considérer qu’il s’agit d’une chaîne de caractères, et non d’un nombre.

C’est pourquoi, si nous essayons d’encapsuler les valeurs de cette liste dans une formule, Excel nous retournera une valeur erronée :

Le total de ces cellules retourné ici par la fonction SOMME() est en effet de 0.

2. Extraire les valeurs numériques d’une chaîne de caractères

Ici, nous allons donc exploiter la colonne « Nombre » située juste à droite, et nous allons souhaiter y insérer uniquement le nombre de fruits ou de légumes, sans le nom de celui-ci.

Dans cet exemple, nous allons pouvoir utiliser la fonction GAUCHE() pour récupérer les deux premiers caractères de la chaîne.

Comme nous l’avons déjà vu dans le tutoriel dédié à cette fonction, celle-ci permet d’extraire les premiers caractères contenus dans une cellule :

=GAUCHE(B8;2)

Excel nous indique alors qu’il y a 18 carottes.

En revanche, comme nous pouvons le constater, ce résultat est toujours placé sur la gauche de la cellule, signe que pour Excel il s’agit encore d’un texte ! En effet, la fonction GAUCHE() renverra toujours une chaîne de caractères, peu importe le contenu véritablement retourné…

Mais pas de soucis, pour convertir un texte en nombre, nous pouvons utiliser la fonction CNUM() dans laquelle nous allons passer en argument la fonction GAUCHE() que nous venons d’insérer :

=CNUM(GAUCHE(B8;2))

Maintenant, la cellule est bien placée à droite de la cellule, et se retrouve dans le résultat de la cellule C18 dans laquelle est insérée la fonction SOMME().

Ensuite, pour étendre cette formule sur toutes les cellules de la colonne, nous pouvons faire glisser la poignée de recopie vers le bas :

Pour conserver la mise en forme déjà appliquée, nous utiliserons plutôt le bouton droit de la souris afin de faire apparaître un menu contextuel, sur lequel nous choisissons de ne dupliquer que les valeurs, sans la mise en forme :

À ce moment-là, nous pouvons repérer deux anomalies :

Tout d’abord, lorsque la cellule ne contient aucune valeur, la formule nous retourne une erreur #VALEUR!.

Cette erreur nous est retournée par la fonction CNUM() qui ne trouve pas de texte à convertir en nombre.

Pour régler ce problème, nous allons pouvoir utiliser la fonction SIERREUR() qui permet d’effectuer un test sur le contenu d’une cellule, ou sur le résultat d’une forme afin de retourner une valeur alternative lorsque ce test retourne une erreur :

=SIERREUR(CNUM(GAUCHE(B8;2));0)

Ici, nous choisissons de retourner la valeur zéro lorsque la fonction CNUM() retourne effectivement une erreur.

Pour modifier toutes les cellules sélectionnées en même temps, nous validons en appuyant sur les touches [Ctrl]+[Entrée].

Le second problème que nous pouvons relever se situe sur la ligne « 124 Noisettes ».

En effet, étant donné que nous avons demandé à Excel d’extraire les deux premiers caractères de la cellule, celui-ci ne tient pas compte du reste du contenu de la cellule.

La solution va alors consister à obtenir le nombre de caractères à extraire de manière dynamique en demandant à Excel de retrouver la position de l’espace au sein de la chaîne de caractères.

Et pour cela, nous pouvons utiliser la fonction TROUVE() que nous avons déjà eu l’occasion de découvrir dans un tutoriel précédent.

=SIERREUR(CNUM(GAUCHE(B8;TROUVE(" ";B8)));0) 

Nous remplaçons le chiffre « 2 » que nous avions inséré dans la forme par cette fonction TROUVE() dans laquelle nous commençons par spécifier le caractère pour lequel nous souhaitons obtenir la position (l’espace), puis la cellule dans laquelle nous devons effectuer cette recherche.

Ici encore, nous validons en appuyant sur les deux touches [Ctrl] et [Entrée] :

Et nous retrouvons bien le bon nombre d’éléments de chaque ligne dans la colonne Nombre.

Nous pouvons maintenant ajouter une nouvelle ligne pour retrouver la valeur de celle-ci directement ajoutée dans le total :

Alors évidemment, comme nous l’avons un peu plus tôt, cet exemple est simplifié au maximum en limitant l’analyse sur des textes sur lesquels le nombre se retrouve au tout début de la cellule.

Si ce nombre pouvait se trouver à n’importe quel endroit de la chaîne (par exemple « Pack de 18 pommes rouges »), ou même avec plusieurs nombres dans une seule cellule (par exemple « Pack de 18 pommes rouges et de 12 pommes vertes »), alors l’analyse serait bien plus complexe et nécessiterait de mettre en place une fonction personnalisée.

C’est justement ce que nous allons voir dans la suite de ce tutoriel.

3. Créer une fonction évoluée de conversion de texte en nombre

Alors, évidemment, pour construire une fonction suffisamment évoluée pour réaliser cette opération, nous allons devoir la développer en VBA.

Pour cela, nous allons commencer par lancer VBE, qui est l’outil de développement des macros VBA.

Pour cela, nous disposons de plusieurs options, la plus rapide consistant à utiliser le raccourci clavier [Alt]+[F11].

Ensuite, pour créer une fonction personnalisée, nous allons insérer un nouveau module en nous rendant dans le menu Insertion > Module :

Un module est une sorte de feuille blanche, dans laquelle nous allons venir saisir des lignes de code pour créer nos macro-commandes et fonctions personnalisées.

   3.1. Fonction pour extraire un nombre à n’importe quelle position

Dans un premier temps, nous allons souhaiter extraire une valeur numérique depuis un texte, celle-ci pouvant être placée à n’importe quel endroit de ce texte :

Pour cela, nous allons créer une fonction que nous appelons EXTRAIRENOMBRE.

Pour créer une fonction, nous utilisons le mot-clé Function, suivi du nom de la fonction :

Lorsque nous validons la création de la fonction en appuyant sur la touche [Entrée] du clavier, VBA valide la création de celle-ci en ajoutant deux éléments :

• Parenthèses vides, dans lesquelles nous viendrons juste après insérer un argument
• La ligne End Function qui vient terminer la fonction. Tout ce que nous allons saisir entre ces deux lignes sera donc exécuté dès que nous appellerons la fonction.

Pour insérer un argument, il suffit de le saisir entre les parenthèses :

Function EXTRAIRENOMBRES(texte As  String)
 
End Function 

Ensuite, pour que la fonction retourne une valeur, il suffit de l’affecter à la fonction, comme s’il s’agissait d’une variable :

Function EXTRAIRENOMBRES(texte As  String)
 
    EXTRAIRENOMBRES = 10
    
End Function 

Ici la fonction nous retournera la valeur 10 lorsque nous l’appellerons, quel qu’en soit son argument.

Pour l’utiliser sur la feuille de calcul, il suffit de l’appeler comme nous le ferions pour n’importe quelle autre fonction d’Excel :

Bien entendu, le résultat est ici erroné, et nous allons maintenant chercher à obtenir la valeur réelle de la chaîne de caractères.

Pour cela, nous allons commencer par scinder le texte afin de stocker chaque mot comme valeur d’un tableau.

Nous commençons donc par créer une variable que nous appelons tableauValeurs et que nous typons en tant que Variant.

Ensuite, nous allons y stocker chaque mot de la variable passée en argument en utilisant la fonction VBA Split() et en utilisant l’espace comme caractère de séparation :

    Dim tableauValeurs As Variant
    tableauValeurs = Split(texte,  " ")

Nous pouvons maintenant passer en revue tous les éléments de ce tableau en utilisant une boucle For Each.

Une boucle For Each permet d’effectuer une action tant qu’une condition est remplie, ici tant qu’il y a des éléments dans le tableau tableauValeurs.

Si vous souhaitez en savoir plus sur les boucles, vous pouvez cliquer ici pour retrouver le tutoriel dédié.

    Dim valeur As Variant
    For Each valeur In tableauValeurs
    
    Next

Maintenant, à l’intérieur de cette boucle, nous allons analyser chaque élément pour déterminer s’il s’agit d’un nombre en utilisant la fonction VBA IsNumeric() :

    For Each valeur In tableauValeurs
        If IsNumeric(valeur) Then
            EXTRAIRENOMBRES = valeur
        End If
    Next

Nous pouvons maintenant tester la fonction pour extraire les nombres saisis dans les textes :

Excel nous retourne bien les bonnes valeurs, seulement nous pouvons constater que celles-ci sont placées sur le bord gauche de la cellule, ce qui signifie que pour Excel il s’agit encore de texte et non pas de nombres.

Pas de problème, pour corriger ce petit problème il suffit de donner un type à la fonction pour s’assurer qu’Excel la retourne bien comme une valeur numérique :

Function EXTRAIRENOMBRES(texte As  String) As Double

Nous pouvons même tester avec des nombres décimaux, cela ne posera pas de problème :

   3.2. Additionner les nombres contenus dans un texte

Maintenant, si nous prévoyons le fait qu’il puisse y avoir plusieurs nombres dans une seule et même cellule, nous pouvons souhaiter que tous ces nombres soient additionnés dans le résultat retourné par la fonction.

Pour cela, nous allons partir de la même fonction, que nous allons tout simplement copier-coller juste en dessous, en modifiant simplement son nom en ADDITIONNERNOMBRES() :

Ensuite, nous allons créer une nouvelle variable au tout début de la fonction, que nous appelons résultat, et que nous typons en tant que Double, comme la fonction :

    Dim resultat As Double

Celle-ci va nous permettre de récupérer la somme des valeurs numériques en ajoutant ces dernières à son propre résultat dans la boucle For Each :

    For Each valeur In tableauValeurs
        If IsNumeric(valeur) Then
            resultat = resultat + valeur
        End If
    Next

Il ne reste plus qu’à affecter la valeur de cette variable resultat en tant valeur de la fonction ADDITIONNERNOMBRES, juste avant que celle-ci ne se termine :

    ADDITIONNERNOMBRES = resultat

Nous pouvons maintenant tester cette nouvelle fonction sur la ligne correspondante :