Fonction VBA Split – Diviser une Chaîne de Texte en Tableau

Fonction VBA Split

La fonction VBA Split permet de diviser une chaîne de texte en sous-chaînes sur la base d’un caractère de délimitation spécifique (par exemple, une virgule, un espace ou deux points). Elle est plus facile à utiliser que l’écriture d’un code pour rechercher les délimiteurs dans la chaîne et en extraire les valeurs.

Il peut être utilisé pour lire une ligne d’un fichier CSV (Comma-Separated Value) ou pour afficher sur plusieurs lignes une adresse postale qui a été écrite sur une seule ligne.

La syntaxe est la suivante :

Split expression, délimiteur[optionnel], limite[optionnel], comparer[optionnel]

La fonction VBA Split a quatre paramètres :

• Expression – La chaîne de texte que vous souhaitez diviser en plusieurs parties.
• Délimiteur (facultatif)– chaîne de caractères ou caractère non imprimable – Définit le caractère de délimitation qui sera utilisé pour la division. Si aucun caractère de délimitation n’est fourni, l’espace est utilisé par défaut.
• Limite (facultatif) – nombre – Définit le nombre de fractionnements à effectuer. S’il est vide, toutes les divisions disponibles seront effectuées dans la chaîne. Si elle vaut 1, aucune division ne sera effectuée. Fondamentalement, cela vous permet de séparer un nombre spécifique de valeurs en commençant par le début de la chaîne, par exemple lorsque la chaîne est très longue et que vous n’avez besoin que des trois premiers fractionnements.
• Comparer (optionnel) – Si votre délimiteur est un caractère textuel, cette option permet d’activer ou de désactiver la distinction entre les majuscules et les minuscules. Les valeurs sont vbBinaryCompare (sensible à la casse) et vbTextCompare (non sensible à la casse).

La fonction split renvoie toujours un tableau.

Exemple Simple de la Fonction Split

Sub ExempleSplit()
'Définition des variables
Dim MonTableau() As String, MaChaine As String, I As Variant
'Chaine de texte séparée par des espaces
MaChaine = "Un Deux Trois Quatre"
'Utilisation de la fonction split pour diviser la chaine en plusieurs composants
MonTableau = Split(MaChaine)
'Boucler sur le tableau généré pour afficher les valeurs
For Each I In MonTableau
     MsgBox I
Next I
End Sub

Dans cet exemple, aucun délimiteur n’est spécifié car tous les mots sont séparés par un espace. Le délimiteur par défaut (espace) peut donc être utilisé.

Le tableau n’a pas de dimensions et est défini comme une chaîne de caractères. La variable I, qui est utilisée dans la boucle For…Next, doit être dimensionnée comme une variante.

Lorsque ce code est exécuté, il affiche quatre boîtes de message, une pour chaque fractionnement, par exemple : Un, Deux, Trois, Quatre.

Notez que s’il y a un double espace entre les mots de la chaîne, il sera évalué comme une division, bien qu’il n’y ait rien dedans. Ce n’est peut-être pas le résultat que vous souhaitez obtenir.

Vous pouvez résoudre ce problème en utilisant la fonction Replace pour remplacer les espaces doubles par un espace simple :

MaChaine = Replace(MaChaine, "  ", " ")

Un espace à la fin ou au début d’une chaine de texte peut également causer des problèmes en produisant une ligne de séparation vide. Ces espaces sont souvent très difficiles à voir. Vous pouvez supprimer ces espaces superflus à l’aide de la fonction Trim:

MaChaine = Trim(MaChaine)

Utilisation de la Fonction de Division avec un Caractère de Délimitation

Il est possible d’utiliser le point-virgule ( « ; » ) comme caractère de délimitation. Ce caractère est souvent utilisé dans les chaînes d’adresses électroniques pour séparer les adresses. Il se peut qu’un courriel qui vous a été envoyé soit partagé avec un certain nombre de collègues et que vous souhaitiez voir dans votre feuille de calcul la liste des destinataires de ce courriel. Vous pouvez facilement copier les adresses électroniques à partir des champs « À » ou « Copie Conforme » du courriel et les insérer dans votre code.

Sub ExempleSplitPointsVirgules()
'Définition des variables
Dim MonTableau() As String, MaChaine As String, I As Variant, N As Integer
'Exemple de chaine de texte délimitée par des points-virgules
MaChaine = "john@myco.com;jane@myco.com;bill@myco.com;james@myco.com"
'Utilisation de la fonction split pour diviser la chaine en plusieurs composants
MonTableau = Split(MaChaine, ";")
'Efface la feuille de calcul
ActiveSheet.UsedRange.Clear
'Boucle sur le tableau généré
For N = 0 To UBound(MonTableau)
     'Place chaque adresse courriel dans la première colonne de la feuille de calcul
     Range("A" & N + 1).Value = MonTableau(N)
Next N
End Sub

Notez qu’une boucle For…Next est utilisée pour parcourir le tableau. Le premier élément du tableau commence toujours à zéro et la fonction Upper Bound est utilisée pour obtenir le nombre maximum d’éléments.

Après avoir exécuté ce code, votre feuille de calcul ressemblera à ceci :

Utilisation d’un Paramètre de Limite dans une Fonction de Fractionnement Split

Le paramètre de limite permet d’effectuer un nombre spécifique de divisions à partir du début de la chaîne de caractères. Malheureusement, il n’est pas possible de fournir une position de départ ou une plage de fractionnements à effectuer. Vous pouvez créer votre propre code VBA pour créer une fonction à cet effet, ce qui sera expliqué plus loin dans cet article.

Sub ExempleSplitAvecLimite()
'Définition des variables
Dim MonTableau() As String, MaChaine As String, I As Variant, N As Integer
'Exemple de chaine de texte délimitée par des virgules
MaChaine = "Un,Deux,Trois,Quatre,Cinq,Six"
'Utilisation de la fonction split pour diviser la chaine en plusieurs composants
MonTableau = Split(MaChaine, ",", 4)
'Efface la feuille de calcul
ActiveSheet.UsedRange.Clear
'Boucle sur le tableau généré
For N = 0 To UBound(MonTableau)
     'Place chaque valeur divisée dans la première colonne de la feuille de calcul
     Range("A" & N + 1).Value = MonTableau(N)
Next N
End Sub

Après avoir exécuté ce code, votre feuille de calcul ressemblera à ceci :

Seules les trois premières valeurs fractionnées sont affichées séparément. Les trois dernières valeurs sont affichées sous la forme d’une longue chaîne et ne sont pas fractionnées.

Si vous choisissez une valeur limite supérieure au nombre de délimiteurs dans une chaîne, il n’y aura pas d’erreur. La chaîne sera scindée en tous ses éléments constitutifs comme si la valeur limite n’avait pas été fournie.

Utilisation du Paramètre Comparer dans une Fonction Split

Le paramètre Compare détermine si le délimiteur est sensible à la casse ou non. Ce paramètre ne s’applique pas si les délimiteurs sont des virgules, des points-virgules ou des deux-points.

Remarque : vous pouvez toujours placer l’option Compare Text en tête de votre module pour éliminer la sensibilité à la casse pour l’ensemble du module.

Sub ExempleSplitParametreCompare()
'Définition des variables
Dim MonTableau() As String, MaChaine As String, I As Variant, N As Integer
'Exemple de chaine de texte délimitée par les lettres X et x
MaChaine = "UnXDeuxXTroisxQuatreXCinqxSix"
'Utilisation de la fonction split pour diviser la chaine en plusieurs composants
MonTableau = Split(MaChaine, "X", , vbBinaryCompare)
'Efface la feuille de calcul
ActiveSheet.UsedRange.Clear
'Boucle sur le tableau généré
For N = 0 To UBound(MonTableau)
     'Place chaque valeur divisée dans la première colonne de la feuille de calcul
     Range("A" & N + 1).Value = MonTableau(N)
Next N
End Sub

Dans cet exemple, la chaîne à diviser utilise le caractère « X » comme délimiteur. Toutefois, cette chaîne contient un mélange de caractères « X » majuscules et minuscules. Le paramètre Compare de la fonction Split utilise le caractère « X » majuscule.

Si le paramètre Compare est défini sur vbBinaryCompare, les caractères « x » minuscules sont ignorés et votre feuille de calcul ressemble à ceci :

Si le paramètre Compare est défini sur vbTextCompare, les caractères ‘x’ minuscules seront utilisés dans la division et votre feuille de calcul ressemblera à ceci :

Notez que les valeurs des cellules A2 et A7 sont tronquées parce qu’elles contiennent un caractère « x » minuscule. La division n’étant pas sensible à la casse, tout délimiteur faisant partie d’une sous-chaîne entraînera une division.

Il s’agit d’un point important à garder à l’esprit lorsque vous utilisez un délimiteur de texte et vbTextCompare. Vous pouvez facilement obtenir un résultat erroné.

Utilisation de Caractères Non Imprimables Comme Caractères de Délimitation

Vous pouvez utiliser des caractères non imprimables comme délimiteurs, tels qu’un retour chariot (un saut de ligne).

Ici, nous utilisons le vbCr pour spécifier un retour chariot.

Sub ExempleSplitCaractereNonImprimable()
'Définition des variables
Dim MonTableau() As String, MaChaine As String, I As Variant, N As Integer
'Exemple de chaine de texte délimitée par des retour de chariot
MaChaine = "Un" & vbCr & "Deux" & vbCr & "Trois" & vbCr & "Quatre" & vbCr & "Cinq" & vbCr & "Six"
'Utilisation de la fonction split pour diviser la chaine en plusieurs composants
MonTableau = Split(MaChaine, vbCr, , vbTextCompare)
'Efface la feuille de calcul
ActiveSheet.UsedRange.Clear
'Boucle sur le tableau généré
For N = 0 To UBound(MonTableau)
     'Place chaque valeur divisée dans la première colonne de la feuille de calcul
     Range("A" & N + 1).Value = MonTableau(N)
Next N
End Sub

Dans cet exemple, une chaîne est construite en utilisant vbCr (caractère de retour chariot) comme délimiteur.

Lorsque ce code est exécuté, votre feuille de calcul se présente comme suit :

Utilisation de la Fonction Join pour Inverser une Division

La fonction Join permet de joindre à nouveau tous les éléments d’un tableau, mais en utilisant un délimiteur spécifié. Si aucun caractère de délimitation n’est spécifié, un espace sera utilisé.

Sub ExempleJoin()
'Définition des variables
Dim MonTableau() As String, MaChaine As String, I As Variant, N As Integer
Dim Target As String
'Exemple de chaine de texte délimitée par des virgules
MaChaine = "Un,Deux,Trois,Quatre,Cinq,Six"
'Insertion de la chaine MaChaine dans la cellule A1
Range("A1").Value = MaChaine
'Utilisation de la fonction split pour diviser la chaine en plusieurs composants
MonTableau = Split(MaChaine, ",")
'Utilisation de la fonction Join pour re-créer la chaine originale en utilisant des points-virugles comme délimiteur
Target = Join(MonTableau, ";")
'Placer la chaine résultante dans la cellule A2
Range("A2").Value = Target
End Sub

Ce code divise une chaîne de caractères avec des délimiteurs de type virgule en un tableau, et la joint à nouveau en utilisant des délimiteurs de type point-virgule.

Après avoir exécuté ce code, votre feuille de calcul ressemblera à ceci :

La cellule A1 contient la chaîne originale avec des délimiteurs de type virgule, et la cellule A2 contient la nouvelle chaîne jointe avec des délimiteurs de type point-virgule.

 

Utilisation de la Fonction « Split » pour Compter les Mots

Sachant qu’une variable de type chaîne de caractères dans Excel VBA peut avoir une longueur maximale de 2 Go, vous pouvez utiliser la fonction « split » pour compter les mots d’un texte. Bien entendu, Microsoft Word le fait automatiquement, mais cela peut être utile pour un simple fichier texte ou un texte copié à partir d’une autre application.

Sub ExempleCompterMots()
'Définition des variables
Dim MonTableau() As String, MaChaine As String
'Exemple de chaine de texte délimitée par des espaces
MaChaine = "One Two Three Four Five Six"
'Suppression d'espaces doubles
MaChaine = Replace(MaChaine, " ", " ")
'Suppression des espaces au début et à la fin de la chaine
MaChaine = Trim(MaChaine)
'Utilisation de la fonction split pour diviser la chaine en plusieurs composants
MonTableau = Split(MaChaine)
'Affiche le nombre de mots en utilisant la fonction UBound
MsgBox "Nombre de Mots " & UBound(MonTableau) + 1
End Sub

L’un des dangers de ce code de comptage de mots est qu’il sera perturbé par les espaces doubles et les espaces de début et de fin. S’ils sont présents, ils seront comptés comme des mots supplémentaires et le nombre de mots sera inexact.

Le code utilise les fonctions Replace et Trim pour supprimer ces espaces supplémentaires.

La dernière ligne de code affiche le nombre de mots trouvés en utilisant la fonction UBound pour obtenir le numéro d’élément maximal du tableau, puis en l’incrémentant de 1. En effet, le premier élément du tableau commence à zéro.

Découpage d’une Adresse en Cellules de Feuille de Calcul

Les adresses électroniques sont souvent de longues chaînes de texte délimitées par des virgules. Il se peut que vous souhaitiez répartir chaque partie de l’adresse dans une cellule distincte.

Sub ExempleAdresse()
'Définition des Variables
Dim MonTableau() As String, MaChaine As String, N As Integer
'Création d'une chaine contenant l'adresse de la compagnie Microsoft
MaChaine = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"
'Utilisation de la fonction split pour diviser la chaine en plusieurs composants
MonTableau = Split(MaChaine, ",")
'Efface la feuille de calcul
ActiveSheet.UsedRange.Clear
'Boucle sur le tableau généré
For N = 0 To UBound(MonTableau)
     'Place chaque valeur divisée dans la première colonne de la feuille de calcul
     Range("A" & N + 1).Value = MonTableau(N)
Next N
End Sub

L’exécution de ce code utilisera le délimiteur virgule pour placer chaque ligne de l’adresse dans une cellule distincte :

Si vous ne voulez renvoyer que le code postal (dernier élément du tableau), vous pouvez utiliser le code suivant :

Sub ExempleCodePostal()
'Définition des Variables
Dim MonTableau() As String, MaChaine As String, N As Integer
'Création d'une chaine contenant l'adresse de la compagnie Microsoft
MaChaine = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"
'Utilisation de la fonction split pour diviser la chaine en plusieurs composants
MonTableau = Split(MaChaine, ",")
'Efface la feuille de calcul
ActiveSheet.UsedRange.Clear
'Affiche le dernier élément du tableau (code postal) dans la cellule A1
Range("A1").Value = MonTableau(UBound(MonTableau))
End Sub

Ceci n’utilisera que le dernier élément du tableau, qui est trouvé en utilisant la fonction UBound.

D’autre part, vous pouvez souhaiter voir toutes les lignes dans une seule cellule afin de les imprimer sur une étiquette d’adresse :

Sub ExempleAdresseUneLigne()
'Définition des Variables
Dim MonTableau() As String, MaChaine As String, N As Integer
'Création d'une chaine contenant l'adresse de la compagnie Microsoft
MaChaine = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"
'Utilisation de la fonction split pour diviser la chaine en plusieurs composants
MonTableau = Split(MaChaine, ",")
'Efface la feuille de calcul
ActiveSheet.UsedRange.Clear
'Boucle sur le tableau généré
For N = 0 To UBound(MonTableau)
     'place chaque élément du tableau et un saut de ligne dans une nouvelle chaine de texte
     Temp = Temp & MonTableau(N) & vbLf
Next N
'Affiche la nouvelle chaine de texte dans la cellule A1
Range("A1") = Temp
End Sub

Cet exemple fonctionne de la même manière que le précédent, sauf qu’il crée une chaîne temporaire de tous les éléments du tableau, mais en insérant un caractère de saut de ligne après chaque élément.

La feuille de calcul se présentera comme suit après l’exécution du code :

Diviser une Chaîne de Caractères en Cellules de Feuille de Calcul

Vous pouvez copier le tableau de fractionnement dans les cellules d’une feuille de calcul à l’aide d’une seule commande :

Sub CopierVersPlage()
'Définition des variables
Dim MonTableau() As String, MaChaine As String
'Exemple de chaine délimitée par des espaces
MaChaine = "Un,Deux,Trois,Quatre,Cinq,Six"
'Utilisation de la fonction split pour diviser la chaine en plusieurs composants
MonTableau = Split(MaChaine, ",")
'Copie le tableau dans la feuille de calcul
Range("A1:A" & UBound(MonTableau) + 1).Value = WorksheetFunction.Transpose(MonTableau)
End Sub

Une fois ce code exécuté, votre feuille de calcul ressemblera à ceci :

Création d’une Nouvelle Fonction Permettant le Fractionnement à Partir d’un Point Donné

Le paramètre Limite de la fonction Fractionner vous permet uniquement de spécifier une limite supérieure à laquelle vous souhaitez que le fractionnement s’arrête. Le fractionnement commence toujours au début de la chaîne.

Il serait très utile de disposer d’une fonction similaire permettant de spécifier le point de départ de la division dans la chaîne et le nombre de divisions que vous souhaitez voir apparaître à partir de ce point. De plus, cette fonction n’extraira que les divisions que vous avez spécifiées dans le tableau, plutôt que d’avoir une énorme valeur de chaîne comme dernier élément du tableau.

Vous pouvez facilement créer vous-même une fonction (appelée dans cet exemple SplitSlicer) en VBA pour effectuer cette opération :

Function SplitSlicer(Target As String, Del As String, Start As Integer, N As Integer)
'Définition des variables
Dim MonTableau() As String
'Crée un tableau avec la fonction split et les parametres Del et Start
MonTableau = Split(Target, Del, Start)
'Vérifie si le paramètre Start est plus grand que le nombre de divisions – Cela pourrait causer des problèmes
If Start > UBound(MonTableau) + 1 Then
     'Affiche l'erreur et quitte la fonction
     MsgBox "Le paramètre Start est plus grand que le nombre de fractionnement possible"
     SplitSlicer = MonTableau
     Exit Function
End If
'Insertion du dernier élément du tableau dans la chaine
Target = MonTableau(UBound(MonTableau))
'Fractionnement de la chaine en utilisant le paramètre N comme limite
MonTableau = Split(Target, Del, N)
'Vérifie que la limite supérieure est plus grande que zéro puisque le code supprime le dernier élément
If UBound(MonTableau) > 0 Then
     'Suppression du dernier élément du tableau à l'aide de ReDim
     ReDim Preserve MonTableau(UBound(MonTableau) - 1)
End If
'Retourne le nouveau tableau
SplitSlicer = MonTableau
End Function

Cette fonction est construite avec quatre paramètres :

• Target – chaîne – il s’agit de la chaîne d’entrée que vous souhaitez diviser
• Del – chaîne ou caractère non imprimable – il s’agit du caractère de délimitation que vous utilisez, par exemple une virgule ou deux points
• Start – nombre – il s’agit du début de la division pour votre tranche
• N – nombre – il s’agit du nombre de fractionnements que vous souhaitez effectuer dans votre tranche

Aucun de ces paramètres n’est facultatif ou n’a de valeur par défaut, mais vous pouvez l’intégrer dans le code de la fonction si vous souhaitez l’étendre davantage.

La fonction utilise la fonction Split pour créer un tableau en utilisant le paramètre Start comme limite. Cela signifie que les éléments du tableau contiendront les divisions jusqu’au paramètre de départ, mais que le reste de la chaîne sera le dernier élément et ne sera pas divisé.

Le dernier élément du tableau est retransféré dans une chaîne de caractères à l’aide de la fonction UBound pour déterminer de quel élément il s’agit.

La chaîne est ensuite à nouveau divisée en tableau, en utilisant N comme variable limite. Cela signifie que les divisions seront effectuées pour la chaîne jusqu’à la position N, après quoi le reste de la chaîne formera le dernier élément du tableau.

L’instruction ReDim est utilisée pour supprimer le dernier élément, car nous voulons que seuls les éléments spécifiques restent dans le tableau. Notez que le paramètre Preserve est utilisé, sinon toutes les données du tableau seront perdues.

Le nouveau tableau est ensuite renvoyé au code à partir duquel il a été appelé.

Notez que le code est « à l’épreuve des erreurs ». Les utilisateurs feront souvent des choses inattendues auxquelles vous n’avez pas pensé. Par exemple, s’ils essaient d’utiliser la fonction avec un paramètre Start ou N supérieur au nombre de divisions disponibles dans la chaîne, la fonction échouera probablement.

Du code est inclus pour vérifier la valeur de Start et pour s’assurer qu’il existe un élément qui peut être supprimé lorsque l’instruction ReDim est utilisée sur le tableau.

Voici le code permettant de tester la fonction :

Sub TestSplitSlicer()
'Définition des variables
Dim MonTableau() As String, MaChaine As String
'Définition de la chaine exemple séparée par des virgules
MaChaine = "Un,Deux,Trois,Quatre,Cinq,Six,Sept,Huit,Neuf,Dix"
'Utilisation de la fonction SplitSlicer pour créer un nouveau tableau
MonTableau = SplitSlicer(MaChaine, ",", 4, 3)
'Efface la feuille de calcul
ActiveSheet.UsedRange.Clear
'Affiche le nouveau tableau sur la feuille de calcul
Range("A1:A" & UBound(MonTableau) + 1).Value = WorksheetFunction.Transpose(MonTableau)
End Sub

Exécutez ce code et votre feuille de calcul ressemblera à ceci :