Extraire tous les chiffres d'un texte en VBA Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Séparer les chiffres et les lettres

Avec cette nouvelle astuce VBA Excel, nous allons découvrir avec quelle simplicité il est possible de créer des fonctions de feuille capables d'isoler les nombres et les textes d'une même cellule.



Sur l'exemple illustré par la capture, des codes sont inscrits dans la première colonne d'un tableau. Ces codes alternent des lettres et des chiffres. Dans les deux colonnes qui suivent, des fonctions créées en VBA isolent respectivement les caractères de texte puis les nombres. Et c'est volontairement que des espaces sont intercalés. Ils représentent les ruptures observées dans les chaînes sources. Ils indiquent que ces textes ou nombres ne se suivaient pas.

Classeur Excel à télécharger
Pour la création de ces deux fonctions, nous suggérons d'appuyer les travaux sur un classeur offrant déjà ces codes mélangeant des chiffres et des lettres.

• Télécharger le classeur extraire-chiffres.xlsm en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous retrouvons bien le tableau de trois colonnes. La première héberge les codes alphanumériques tandis que les deux autres sont encore vides. Elles sont en attente des deux fonctions VBA pour réaliser les découpes.

Créer les deux fonctions VBA
Pour débuter simplement, nous proposons de créer les bornes des deux fonctions VBA de découpe, qu'il s'agira ensuite d'implémenter en pas à pas.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur VBA Excel,
• Dans l'explorateur de projet sur la gauche, double cliquer sur l'élément Module1,

Nous affichons ainsi sa feuille de code au centre de l'écran. Bien sûr, elle est encore vierge.

• Créer les deux fonctions VBA suivantes :

Function isolerNombres(cellule As String) As String

End Function

Function isolerTextes(cellule As String) As String

End Function

Toutes deux attendent une variable en paramètre. Il s'agit de la cellule à désigner par l'utilisateur pour engager les traitements d'extraction. Elles sont typées comme des fonctions de texte (As String). En effet et nous l'avons annoncé, nous souhaitons séparer d'un espace les séquences qui ne sont originellement pas collées.

Les variables et les affectations
Ensuite, nous souhaitons nous concentrer sur l'élaboration de la première fonction, celle qui doit isoler les chiffres. En effet, la seconde est très similaire à un détail près qu'il s'agira d'ajuster. Et des variables sont nécessaires pour mener à bien le processus de découpe.

• Dans la fonction isolerNombres, ajouter les déclarations et affectations suivantes :

...
Dim longueur As Integer: Dim chaine As String
Dim rupture As Boolean

longueur = Len(cellule)
rupture = False
...

La variable longueur est déclarée comme un entier. Son rôle est de mémoriser le nombre de caractères contenus dans la chaîne. C'est ainsi que nous pourrons ensuite tous les passer en revue tour à tour. La variable chaine est typée comme un texte pour pouvoir reconstruire au fur et à mesure, l'assemblage des chiffres décelés dans la cellule passée en paramètre. La variable rupture est typée comme un booléen. C'est elle qui devra alerter lorsque qu'une séquence de chiffres est brisée pour reprendre plus loin.

C'est ensuite la fonction VBA Len qui retourne le nombre de caractères à traiter dans la variable longueur. Puis, nous initialisons la variable rupture à False. Forcément, au début du traitement, aucune rupture de séquence n'est encore observée.

Parcourir les caractères un à un
C'est maintenant une boucle qui doit permettre de parcourir chaque caractère de la cellule à analyser.

• A la suite du code, créer la boucle suivante :

...
For i = 1 To longueur

Next i
...

Nous partons du premier caractère (i) pour rejoindre le dernier (longueur). Il est à noter que nous utilisons une variable (i) non déclarée. Idéalement, elle devrait l'être. Mais comme vous le savez, le VBA est permissif.

Tester si le caractère est numérique
Grâce à cette boucle, nous allons désormais pouvoir exploiter la fonction VBA IsNumeric pour savoir si le caractère en cours d'analyse est un chiffre.

• Dans les bornes de la boucle, créer l'instruction conditionnelle suivante :

...
For i = 1 To longueur
If IsNumeric(Mid(cellule, i, 1)) Then

Else
rupture = True
End If
Next i
...

La fonction Mid permet de prélever une séquence dans la chaîne de la cellule analysée. En partant de la position i sur une longueur d'un caractère (1), nous observons chaque caractère un à un. Et comme chaque caractère ainsi isolé est passé en paramètre de la fonction IsNumeric, nous cherchons à savoir s'il s'agit d'un chiffre pour préparer le traitement. Dans le cas contraire (else), nous basculons la valeur booléenne à True pour indiquer qu'une rupture s'est opérée, si d'aventure de nouveaux chiffres sont à suivre plus tard dans cette chaîne.

Réunir les chiffres
Si ce test numérique est vérifié, deux cas de figure se présentent. Si le booléen vaut True, cela signifie que l'assemblage des chiffres suivants doit reprendre avec un espace en préfixe pour relater la rupture existante dans la chaîne d'origine. Dans le cas contraire, l'assemblage doit se poursuivre dans la continuité.

• Dans la première branche de l'instruction conditionnelle, ajouter les lignes VBA suivantes :

...
If rupture = True Then
chaine = chaine & " " & Mid(cellule, i, 1)
Else
chaine = chaine & Mid(cellule, i, 1)
End If
rupture = False
...

Si le booléen vaut True, à l'assemblage précédent (chaine = chaine), nous ajoutons un espace (& " ") avant de reprendre la séquence des chiffres suivants (& Mid(cellule, i, 1)). Dans le cas contraire, nous poursuivons l'assemblage des chiffres les uns à la suite des autres (chaine = chaine & Mid(cellule, i, 1)).

Retourner les chiffres
Il ne nous reste plus qu'à retourner la séquence des chiffres ainsi reconstruite. Et comme vous le savez, il n'existe pas d'instruction Return en VBA. La valeur à retourner doit être affectée au nom même de la fonction.

• Après la boucle et avant le End Function, ajouter la ligne VBA suivante :

isolerNombres = Trim(chaine)

Nous retournons donc le résultat des concaténations réalisées sur les chiffres détectés, mais avec un petit traitement subsidiaire. La fonction Trim permet de nettoyer les potentiels espaces se trouvant en préfixe ou en suffixe de la chaîne ainsi reconstruite.

Extraire les chiffres des textes
Il est temps de tester cette fonction.

• Enregistrer les modifications (CTRL + S) et basculer sur la feuille Excel (ALT + Tab),
• En cellule E4, construire et valider la formule suivante : =isolerNombres(C4),

Comme vous pouvez l'apprécier pour cette première cellule, la séquence numérique est parfaitement extraite indépendamment des lettres.

• Double cliquer sur la poignée du résultat pour répandre la logique sur tout le tableau,

Tous les nombres sont parfaitement extraits. Et les séquences trouvées sont reconstruites en respectant les intervalles grâces aux espaces de séparation ajoutés par le code VBA de la fonction.

Extraire les lettres au milieu des chiffres
C'est le processus inverse que nous devons maintenant accomplir. Le code est quasiment identique. Il suffit seulement d'inverser le test réalisé par la fonction IsNumeric pour déceler cette fois la présence des caractères non numériques. Il convient donc tout d'abord de copier et de coller le précédent code.

• Revenir dans l'éditeur VBA Excel,
• Dans la fonction isolerTextes, adapter le code VBA comme suit :

Function isolerTextes(cellule As String) As String
Dim longueur As Integer: Dim chaine As String
Dim rupture As Boolean

longueur = Len(cellule)
rupture = False

For i = 1 To longueur
If Not IsNumeric(Mid(cellule, i, 1)) Then
If rupture = True Then
chaine = chaine & " " & Mid(cellule, i, 1)
Else
chaine = chaine & Mid(cellule, i, 1)
End If
rupture = False
Else
rupture = True
End If
Next i

isolerTextes = Trim(chaine)
End Function

• Enregistrer les modifications (CTRL + S) et basculer sur la feuille Excel (ALT + Tab),
• En cellule D4, construire et valider la formule suivante : =isolerTextes(C4),
• Puis, double cliquer sur la poignée du résultat pour répandre la logique,

Comme vous pouvez le voir, ce sont bien toutes les lettres qui sont réunies et qui sont séparées des chiffres.