Séparer les chiffres des lettres avec une fonction VBA

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 des lettres

A l'occasion d'une astuce Excel, nous avions appris à séparer les nombres des textes. Pour cela, nous avions bâti une formule matricielle. Mais la syntaxe aboutie était relativement complexe. C'est la raison pour laquelle nous souhaitons ici développer une fonction VBA Excel, capable d'isoler les chiffres au milieu des textes et ce, sur tout un tableau présélectionné.



Sur l'exemple illustré par la capture, des chiffres sont implantés au beau milieu de chaînes de textes, en première colonne d'un tableau. L'utilisateur sélectionne la première cellule d'extraction. Il appelle la fonction que nous avons nommée separer. En argument, il lui passe la plage entière des caractères mélangés, situés en première colonne. A validation, il obtient les extractions indépendantes des lettres et des chiffres, sur deux colonnes et sur toute la hauteur du tableau.

Classeur Excel à télécharger
Pour la construction de cette puissante fonction matricielle, nous suggérons d'appuyer les travaux sur un classeur offrant des chaînes abritant des caractères de différentes natures.

• Télécharger le classeur separer-chiffres-lettres.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é,

Les chaînes à décortiquer sont placées en colonne C du tableau récupéré. Les extractions indépendantes doivent être livrées entre les colonnes D et E.

La fonction et ses variables
La première tâche à laquelle nous devons nous atteler consiste à créer la fonction matricielle avec les variables dont elle a besoin pour étudier les chaînes à fragmenter.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur VBA Excel,
• Dans l'explorateur sur la gauche, cliquer sur l'élément VBAProject (séparer-chiffres-lettres),
• En haut de l'éditeur, cliquer sur le menu Insertion puis choisir l'option Module,

Nous créons ainsi un nouveau module VBA pour accueillir la fonction à créer. Sa feuille de code apparaît au centre de l'écran. Elle est vierge pour l'instant.

• Dans cette feuille de code, créer la fonction separer, comme suit :

Function separer(plage As Range) As Variant
Dim laPlage As Range: Dim cellule As Range: Dim extract As Variant
Dim nbCel As Byte: Dim i As Byte: Dim compteur As Byte
Dim chaineT As String: Dim chaineC As String

End Function

Nous la typons comme un variant pour qu'elle puisse retourner un tableau de variables. Elle doit en effet séparer tous les chiffres des lettres, donc sur deux colonnes pour l'ensemble de la plage (plage As Range) qui lui est passée en paramètre, soit sur un nombre variable de lignes. La variable laPlage est typée comme un Range pour pouvoir prendre possession de la plage de cellules à décortiquer et passée en paramètre de la fonction. extract doit représenter le tableau de variables dans lequel nous allons stocker sur deux colonnes, toutes les lettres indépendamment des chiffres. Pour dimensionner ce tableau, nous avons besoin de connaître le nombre de cellules de la plage. C'est la raison pour laquelle nous déclarons la variable nbCel comme un entier court. Nous déclarons ensuite deux variables de boucles (i et compteur). En effet, pour chaque cellule passée en revue, nous devrons étudier chacun de ses caractères. Et nous rangerons les parties fractionnées dans les variables respectives chaineT et chaineC, que nous typons naturellement comme des textes (As String).

Initialiser et dimensionner le tableau de variables
Puisque les variables existent désormais, il est temps de les exploiter, notamment pour initialiser la plage de cellules à parcourir ainsi que le tableau de variables.

• A la suite du code de la fonction, ajouter les initialisations suivantes :

...
Set laPlage = plage
nbCel = laPlage.Count
ReDim extract(1 To nbCel, 1 To 2)
compteur = 1
...

Nous initialisons (Set) l'objet laPlage sur la plage de cellules passée à la fonction par l'utilisateur lors de la construction de la formule sur la feuille Excel. Dès lors, grâce à sa propriété Count héritée, nous stockons le nombre de cellules qu'elle contient. Nous exploitons cette information pour dimensionner (ReDim) le tableau de variables sur la hauteur nécessaire (1 to nbCel) et sur deux colonnes.

Pour chaque cellule de la plage transmise
Sur cette plage désormais maîtrisée, nous devons maintenant engager une boucle For Each avec notre objet cellule que nous n'avions pas commenté. Il est typé comme un Range. Ainsi exploité, il va nous permettre de parcourir toutes les cellules de la plage transmise par l'utilisateur.

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

...
For Each cellule In laPlage

compteur = compteur + 1
Next cellule
...

A chaque passage dans cette boucle, nous incrémentons la variable compteur. C'est elle qui va permettre de faire progresser l'indice de ligne du tableau de variables dans lequel nous devons ranger les données découpées.

Parcourir chaque caractère de chaque cellule
Mais avant cela, nous devons précisément découper les informations de chaque cellule pour séparer les lettres des chiffres. Pour ce faire, nous devons analyser chaque caractère de l'information en cours d'analyse. Donc, nous devons inclure une seconde boucle dans cette première boucle. Cette fois, il s'agit d'une boucle For Next classique et nous allons comprendre pourquoi.

• Dans la boucle et avant l'incrémentation, ajouter la seconde boucle suivante :

...
For Each cellule In laPlage
For i = 1 To cellule.Characters.Count

Next i
compteur = compteur + 1
Next cellule
...

La collection Characters d'un objet de type Range représente tous les caractères contenus dans la plage, en l'occurrence ici dans la cellule en cours d'analyse. Sa propriété Count renvoie leur nombre. C'est ainsi que nous les parcourons tous, du premier au dernier, grâce à cette boucle For Next.

Tester si le caractère est numérique
Maintenant, pour chaque caractère passé en revue pour la cellule en cours d'analyse, nous devons vérifier s'il s'agit d'un chiffre. Pour cela, nous pouvons exploiter la fonction VBA IsNumeric dans une instruction conditionnelle.

• A l'intérieur de la seconde boucle, créer le test suivant :

...
If (IsNumeric(Mid(cellule.Value, i, 1))) Then
chaineC = chaineC & Mid(cellule.Value, i, 1)
Else
chaineT = chaineT & Mid(cellule.Value, i, 1)
End If
...

La fonction VBA Mid permet d'isoler le caractère en cours (i,1). S'il s'agit bien d'un chiffre (If (IsNumeric...), nous le rangeons à la suite des autres chiffres dans la variable chaineC. Sinon, nous rangeons la lettre à la suite des autres dans la variable chaineT. C'est ainsi que nous séparons les données et réunissons celles de même nature.

Ranger les chaînes et retourner les extractions
A chaque passage dans la première boucle, donc pour chaque cellule, une fois que tous les caractères ont été analysés, donc après la seconde boucle, nous devons ranger les informations séparées dans les deux cellules du tableau de variables pour l'indice de ligne(compteur) en cours.

• Avant l'incrémentation de la première boucle, ajouter les instructions VBA suivantes :

...
Next i
extract(compteur, 1) = chaineT
extract(compteur, 2) = chaineC
chaineT = "": chaineC = ""
compteur = compteur + 1
Next cellule
...

Pour la ligne en cours (compteur), nous rangeons la chaîne des lettres isolées (chaineT) en première colonne du tableau de variables (extract). Pour cette même ligne, nous rangeons la chaîne des chiffres isolés (chaineC) en seconde colonne. Nous n'oublions pas de réinitialiser ces chaînes pour qu'elles soient purgées, en vue de l'analyse de la prochaine cellule par la première boucle.

Il ne nous reste plus qu'à retourner les valeurs de ce tableau de variables pour que notre fonction renvoie les résultats de son analyse matricielle. Pour cela et comme vous le savez, nous devons affecter ce tableau sur le nom même de la fonction.

• Après la boucle et avant la fin de la fonction, ajouter les deux instructions VBA suivantes :

...
Next cellule

Set laPlage = Nothing
separer = extract

End Function
...

Tout d'abord, nous pensons bien à détruire la variable laPlage qui n'est plus utilisée. Puis, nous réalisons le retour de la fonction par affectation.

• Enregistrer les modifications (CTRL + S) et revenir sur la feuille Excel (ALT + Tab),
• Cliquer sur la première case pour l'extraction, soit sur la cellule D4,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Appeler la fonction par son nom, suivie d'une parenthèse, soit : Separer(,
• Désigner l'intégralité des codes, soit la plage de cellules C4:C11,
• Fermer la parenthèse de la fonction Separer,
• Enfin, valider la formule par la touche Entrée du clavier,

Comme vous pouvez l'apprécier et quelles que soient leurs positions, les lettres sont parfaitement réunies pour chaque cellule, indépendamment des chiffres qui sont eux-mêmes regroupés. Nous sommes donc parvenus à créer une fonction VBA Excel matricielle pour isoler les chiffres des lettres sur l'intégralité d'une plage de cellules.