Découper les textes sur la virgule 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 :


Découper des textes sur des virgules

Avec cette nouvelle astuce VBA Excel, sur la base d'informations séparées par des virgules, nous allons voir comment fractionner ces données dans des cellules explicitement différentes d'une même ligne.

Classeur Excel à télécharger
Pour créer cette fonction VBA Excel, nous suggérons d'appuyer l'étude sur un classeur offrant déjà des informations propices au découpage.

• Télécharger le classeur decouper-textes.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 découvrons deux tableaux. Le premier liste les adresses postales de certains clients. Toutes les informations de chacune de ces adresses sont inscrites dans une même cellule. Mais chaque portion distincte est séparée d'une autre par une virgule.

Le second tableau est vide. Il attend les résultats à retourner par les fonctions VBA à créer. En première colonne, nous souhaitons livrer les nombres de mots qui composent chacune de ces adresses. Dans les colonnes suivantes, une même fonction, matricielle donc, doit fractionner les données des adresses sur les virgules, pour les placer dans des cellules distinctes.

La fonction pour compter les mots
Nous proposons de commencer par créer la fonction qui permettra de compter les mots d'une cellule. C'est la plus simple des deux bien que la seconde ne soit pas bien compliquée non plus.

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

De cette manière, nous affichons sa feuille de code (vierge pour l'instant) au centre de l'écran. C'est elle qui doit accueillir nos fonctions.

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

Function cpteMots(cellule As Range) As Byte
Dim tabl() As String

End Function

Le paramètre en attente (cellule) est tout simplement la cellule que devra désigner l'utilisateur au moment de la construction de la fonction. Cette fonction est typée comme un entier court (As Byte) puisqu'elle doit retourner le nombre de mots contenus dans la cellule passée en paramètre. Ensuite, nous déclarons un tableau de variables. Il est destiné à accueillir chaque mot découpé dans ses rangées. C'est grâce à lui que nous pourrons les compter.

Découper sur les mots
C'est ensuite la fonction VBA Split qui permet de découper une chaîne sur le délimiteur signifié en second paramètre. Ici, il s'agit de l'espace.

• A la suite de la fonction, ajouter les deux instructions VBA suivantes :

Function cpteMots(cellule As Range) As Byte
Dim tabl() As String

tabl = Split(cellule.Value, " ")
cpteMots = UBound(tabl()) + 1
End Function

Grâce à la fonction Split que nous faisons agir sur le contenu (Value) de la cellule passée en paramètre, nous rangeons tous les mots dans le tableau de variables nommé tabl. Grâce à la fonction UBound, nous connaissons sa dimension. Mais comme un tableau de variables commence à l'indice zéro, nous ajoutons une unité pour recaler sur le nombre exact de mots. Nous retournons ce nombre par le nom même de la fonction.

Il ne nous reste plus qu'à tester cette première fonction.

• Enregistrer les modifications (CTRL + S) et revenir sur la feuille Excel (ALT + Tab),
• Cliquer sur la cellule F4 du premier décompte à réaliser,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire le nom de la fonction suivi d'une parenthèse, soit : cpteMots(,
• Désigner la première adresse en cliquant sur la cellule D4,
• Fermer la parenthèse de la fonction cpteMots,
• Puis, valider la formule avec le raccourci clavier CTRL + Entrée,

Comme vous pouvez le voir, le premier résultat tombe. Il indique que la première adresse est constituée de 6 mots ce qui est parfaitement juste.

• Cliquer et glisser la poignée du résultat vers le bas jusqu'en cellule F11,

Cette fois, ce sont bien tous les décomptes que nous obtenons. Comme les virgules sont attachées aux mots et que la découpe est faite sur les espaces, elles ne sont pas considérées.

Fonction pour fractionner sur les virgules
Cette seconde fonction doit raisonner de façon matricielle cette fois. En effet, elle ne doit pas renvoyer un résultat, mais plusieurs sur une même ligne, en fonction de la quantité de virgules contenues dans la cellule source.

• Revenir dans l'éditeur VBA Excel,
• Sous la fonction cpteMots, créer la fonction decouper, comme suit :

Function decouper(cellule As Range) As Variant
Dim tabl As Variant: Dim tabChaine() As String

End Function

Nous la déclarons elle aussi avec un paramètre en attente. Il s'agit bien sûr de la cellule à découper. Nous ne la typons pas vraiment (As variant). C'est ainsi qu'elle pourra se transformer en tableau de variables au moment opportun. Rappelons-le, elle doit répondre par une matrice horizontale. Le tableau de variables tabChaine est destiné à recevoir la découpe de la cellule sur la virgule. Grâce à lui, nous pourrons transformer la variable tabl en matrice horizontale, dans laquelle nous y rangerons les éléments découpés. C'est elle que nous utiliserons à l'issue pour affecter la fonction.

Découper sur la virgule
Comme précédemment, nous devons maintenant engager la fonction Split pour découper les informations qu'elle contient sur la virgule.

• Après la déclaration des variables, ajouter l'instruction VBA suivante :

...
tabChaine = Split(cellule, ",")
...

Désormais, chaque information qui était séparée d'une autre par une virgule est rangée indépendamment dans le tableau de variables nommé tabChaine.

Retourner les données à l'horizontale
C'est maintenant que doit entrer en lice notre variable tabl. Nous devons la redimensionner à l'horizontale, selon le nombre d'éléments qui constituent le tableau de variables tabChaine. Puis, nous devons parcourir tous les éléments de ce dernier pour les inscrire dans cette matrice tabl.

• A la suite du code, ajouter les instructions VBA suivantes :

...
ReDim tabl(0, UBound(tabChaine))
For i = 0 To UBound(tabChaine)
tabl(0, i) = tabChaine(i)
Next i
decouper = tabl
...

Grâce à l'instruction ReDim, nous typons enfin notre variable tabl, comme un tableau d'une ligne et d'un nombre de colonnes correspondant à la dimension du tableau tabChaine, soit du nombre d'éléments (UBound) qu'il contient. C'est ainsi que nous engageons une boucle For pour ranger chaque donnée séparée dans le tableau tabl.

Le code VBA est terminé. Il ne nous reste plus qu'à le tester.

• Enregistrer les modifications (CTRL + S) et basculer sur la feuille Excel (ALT + Tab),
• Cliquer sur la cellule G4 pour la sélectionner,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire le nom de la nouvelle fonction suivi d'une parenthèse, soit : decouper(,
• Désigner la première adresse en cliquant sur sa cellule D4,
• Fermer la parenthèse de la fonction,
• Puis, valider la formule par le raccourci clavier CTRL + Entrée,

Comme vous pouvez l'apprécier, la première adresse est parfaitement décomposée sur ses informations de rue, de code postal et de ville.

• Cliquer et glisser la poignée du résultat jusqu'en cellule G11,

Ce sont bien toutes les adresses qui sont tronçonnées selon notre volonté.