Scinder des chaînes de texte par délimiteur en plusieurs lignes – Astuces rapides 3

Généralement, vous pouvez utiliser la fonctionnalité « Convertir en colonnes » pour scinder le contenu d’une cellule en plusieurs colonnes à l’aide d’un délimiteur spécifique, tel qu’une virgule, un point, un point-virgule ou une barre oblique. Toutefois, il peut arriver que vous deviez scinder le contenu délimité d’une cellule en plusieurs lignes tout en répétant les données des autres colonnes, comme illustré dans la capture d’écran ci-dessous. Disposez-vous de méthodes efficaces pour accomplir cette tâche dans Excel ? Ce tutoriel vous présente quelques solutions simples et performantes pour y parvenir.

Vidéo : Scinder des chaînes de texte par délimiteur en plusieurs lignes

Méthode A : Scinder un texte délimité en plusieurs lignes à l’aide d’un code VBA

Dans cette section, je vous présente deux codes VBA pour scinder le contenu des cellules selon un délimiteur spécifié.

Scindez un texte délimité par une virgule, un espace, un point-virgule, etc.

Pour scinder des chaînes de texte séparées par un délimiteur courant, tel qu’une virgule, un espace, un point-virgule, une barre oblique, etc., le code suivant vous sera utile. Veuillez suivre les étapes ci-dessous :

Remarque : Ce code ne prend pas en charge la commande « Annuler ». Il est fortement recommandé de sauvegarder vos données avant de l’appliquer.

Étape 1 : Ouvrir l’éditeur de module VBA et copier le code

1. Activez la feuille que vous souhaitez utiliser, puis appuyez sur les touches « Alt » + « F11 » pour ouvrir la fenêtre « Microsoft Visual Basic pour Applications ».

2. Dans la fenêtre qui s’ouvre, cliquez sur « Insertion » > « Module » pour créer un nouveau module vide.

3. Copiez ensuite le code ci-dessous et collez-le dans le module vierge.

Code VBA : Scinder un texte selon un délimiteur spécifique (virgule, point, espace, etc.)

Sub SplitTextIntoRows()
'UpdatebyExtendoffice
    Dim xSRg, xIptRg, xCrRg, xRg As Range
    Dim xSplitChar As String
    Dim xArr As Variant
    Dim xFNum, xFFNum, xRow, xColumn, xNum As Integer
    Dim xWSh As Worksheet
    Set xSRg = Application.InputBox("Select a range:", "Kutools for Excel", , , , , , 8)
    If xSRg Is Nothing Then Exit Sub
    xSplitChar = Application.InputBox("Type delimiter:", "Kutools for Excel", , , , , , 2)
    If xSplitChar = "" Then Exit Sub
    Application.ScreenUpdating = False
    xRow = xSRg.Row
    xColumn = xSRg.Column
    Set xWSh = xSRg.Worksheet
    For xFNum = xSRg.Rows.Count To 1 Step -1
        Set xRg = xWSh.Cells.Item(xRow + xFNum - 1, xColumn)
        xArr = Split(xRg, xSplitChar)
        xIndex = UBound(xArr)
        For xFFNum = LBound(xArr) To UBound(xArr)
            xRg.EntireRow.Copy
            xRg.Offset(1, 0).EntireRow.Insert Shift:=xlShiftDown
            xRg.Worksheet.Cells(xRow + xFNum, xColumn) = xArr(xIndex)
            xIndex = xIndex - 1
        Next
        xRg.EntireRow.Delete
    Next
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub   

Étape 2 : Exécuter le code pour obtenir le résultat

1. Après avoir collé le code, appuyez sur la touche « F5 » pour l’exécuter. Une boîte de dialogue s’ouvre alors afin que vous sélectionniez les cellules contenant le texte délimité à scinder, comme illustré ci-dessous :

2. Cliquez ensuite sur « OK ». Une nouvelle boîte de dialogue s’ouvre pour vous inviter à indiquer le séparateur selon lequel vous souhaitez fractionner vos données. Ici, je saisis une virgule suivie d’un espace (, ), comme illustré ci-dessous :

3. Enfin, cliquez sur le bouton « OK ». Vous constaterez alors que les chaînes de texte sélectionnées ont été divisées en lignes selon la virgule, et que les données des autres colonnes correspondantes ont été répétées, comme illustré dans les captures d’écran ci-dessous :

Scinder un texte séparé par un saut de ligne

Si le contenu de vos cellules est séparé par des sauts de ligne, voici un autre code VBA qui vous permettra de les fractionner en plusieurs lignes.

Remarque : Ce code ne prend pas en charge la commande Annuler. Il est fortement recommandé de sauvegarder vos données avant de l’appliquer.

Étape 1 : Ouvrir l’éditeur de module VBA et copier le code

1. Appuyez sur les touches « Alt » + « F11 » pour ouvrir la fenêtre « Microsoft Visual Basic pour Applications ».

2. Dans la fenêtre qui s’ouvre, cliquez sur « Insertion » > « Module » pour créer un nouveau module vide.

3. Copiez ensuite le code ci-dessous et collez-le dans le module vierge.

Code VBA : Scinder un texte selon les sauts de ligne

Sub SplitTextIntoRows()
'UpdatebyExtendoffice
    Dim xSRg, xIptRg, xCrRg, xRg As Range
    Dim xSplitChar As String
    Dim xArr As Variant
    Dim xFNum, xFFNum, xRow, xColumn, xNum As Integer
    Dim xWSh As Worksheet
    Set xSRg = Application.InputBox("Select a range:", "Kutools for Excel", , , , , , 8)
    If xSRg Is Nothing Then Exit Sub
    xSplitChar = Chr(10)
    Application.ScreenUpdating = False
    xRow = xSRg.Row
    xColumn = xSRg.Column
    Set xWSh = xSRg.Worksheet
    For xFNum = xSRg.Rows.Count To 1 Step -1
        Set xRg = xWSh.Cells.Item(xRow + xFNum - 1, xColumn)
        xArr = Split(xRg, xSplitChar)
        xIndex = UBound(xArr)
        For xFFNum = LBound(xArr) To UBound(xArr)
            xRg.EntireRow.Copy
            xRg.Offset(1, 0).EntireRow.Insert Shift:=xlShiftDown
            xRg.Worksheet.Cells(xRow + xFNum, xColumn) = xArr(xIndex)
            xIndex = xIndex - 1
        Next
        xRg.EntireRow.Delete
    Next
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub 

Étape 2 : Exécuter le code pour obtenir le résultat

1. Après avoir collé le code, appuyez sur la touche « F5 » pour l’exécuter. Dans la boîte de dialogue qui s’affiche, veuillez sélectionner les cellules que vous souhaitez scinder, comme illustré ci-dessous :

2. Cliquez ensuite sur le bouton « OK ». Les données des cellules sélectionnées sont alors divisées en lignes, comme le montre la capture d’écran ci-dessous :

Méthode B : Scinder un texte délimité en plusieurs lignes en 10 secondes avec Kutools pour Excel

Si vous avez installé « Kutools pour Excel », sa fonctionnalité « Diviser en plusieurs lignes » vous permet de fractionner des chaînes de texte en plusieurs lignes selon n’importe quel délimiteur de votre choix. Voici la marche à suivre :

Étape 1 : Sélectionner la fonctionnalité Diviser en plusieurs lignes

Cliquez sur «Kutools» > «Fusionner et scinder» > «Diviser en plusieurs lignes», comme illustré ci-dessous :

Étape 2 : Spécifier les cellules de données et le délimiteur à utiliser pour la division

Dans la boîte de dialogue qui s’affiche, utilisez les options suivantes :

• 1). Sélectionnez la liste des cellules que vous souhaitez fractionner dans la zone de texte « Plage (une seule colonne) ».
• 2). Choisissez ensuite le délimiteur qui sépare vos données ; ici, je sélectionnerai « Autre » et saisirai une virgule suivie d’un espace (, ) dans la zone de texte.
• 3). Enfin, cliquez sur le bouton « OK ».

Les cellules sélectionnées contenant des chaînes de texte délimitées ont désormais été converties en plusieurs lignes selon le séparateur indiqué, comme illustré ci-dessous :

Conseil : Pour restaurer les données d’origine, appuyez simplement sur Ctrl + Z afin d’annuler l’opération.

Facile à utiliser ? Cette fonctionnalité vous intéresse ? Cliquez ici pour télécharger une version d’essai gratuite valable 30 jours.

Méthode C : Scinder un texte délimité en plusieurs lignes à l’aide de Power Query

Si vous utilisez Office 365 ou Excel 2016 et les versions ultérieures, Power Query est un outil puissant qui vous permet de fractionner un texte délimité en plusieurs lignes ou colonnes. Il s’avère particulièrement utile lorsque vous souhaitez que le fractionnement de vos données soit automatiquement mis à jour dès que vos données sources changent. Suivez les étapes ci-dessous pour réaliser cette opération :

Étape 1 : Importer le tableau de données dans Power Query

1. Sélectionnez la plage de données que vous souhaitez utiliser, puis cliquez sur « Données » > « À partir d’un tableau », comme illustré ci-dessous :

Conseil : Dans Excel 2019 et Office 365, cliquez sur Données > À partir d’un tableau ou d’une plage.

2. Dans la boîte de dialogue « Créer un tableau » qui s’ouvre, cliquez sur le bouton « OK » pour créer votre tableau, comme illustré ci-dessous :

3. La fenêtre « Éditeur Power Query » s’ouvre alors et affiche vos données, comme illustré ci-dessous :

Étape 2 : Appliquer les transformations dans Power Query

1. Sélectionnez la colonne que vous souhaitez scinder, puis cliquez sur « Accueil » > « Scinder la colonne » > « Par délimiteur », comme illustré ci-dessous :

2. Dans la boîte de dialogue « Scinder la colonne par délimiteur » :

Pour scinder des chaînes de texte par virgule, espace, point-virgule, etc., procédez comme suit :

• 1). Choisissez, dans la liste déroulante « Sélectionner ou saisir un délimiteur », le délimiteur utilisé pour séparer vos données ;
• 2). Sélectionnez « À chaque occurrence du délimiteur » dans la section « Fractionner à ».
• 3). Cliquez ensuite sur « Options avancées » pour développer la section, puis sélectionnez « Lignes » sous « Fractionner en ».
• 4). Enfin, cliquez sur « OK » pour fermer cette boîte de dialogue.

Pour scinder des chaînes de texte en plusieurs lignes selon les sauts de ligne, procédez comme suit :

• 1). Sélectionnez « Personnalisé » dans la liste déroulante « Sélectionner ou saisir un délimiteur » ;
• 2). Dans la liste déroulante « Insérer des caractères spéciaux », sélectionnez « Retour à la ligne » ; les caractères « #(lf) » s’inséreront automatiquement dans la zone de texte située sous « Personnalisé ».
• 3). Sélectionnez « À chaque occurrence du délimiteur » dans la section « Fractionner à ».
• 4). Cliquez ensuite sur « Options avancées » pour développer la section, puis sélectionnez « Lignes » sous « Scinder en ».
• 5). Enfin, cliquez sur « OK » pour fermer cette boîte de dialogue.

3. La commande « Sélectionner les données » a désormais été répartie sur plusieurs lignes, comme illustré dans la capture d’écran ci-dessous :

Étape 3 : Exporter les données de Power Query vers un tableau Excel

1. Vous devez maintenant exporter les données vers votre feuille de calcul. Cliquez sur « Accueil » > « Fermer et charger » > « Fermer et charger / Fermer et charger vers… » (ici, je sélectionne « Fermer et charger »), comme illustré ci-dessous :

Conseil : cliquez sur « Fermer et charger » pour exporter les données vers une nouvelle feuille de calcul ; choisissez « Fermer et charger vers… » pour les exporter vers la feuille de votre choix.

2. Les données sont finalement chargées dans une nouvelle feuille de calcul, comme illustré ci-dessous :

Conseil : Si vous devez fréquemment mettre à jour vos données dans le tableau d’origine, pas d’inquiétude : il vous suffit de cliquer avec le bouton droit sur le tableau de résultats et de choisir « Actualiser » pour obtenir instantanément les nouveaux résultats.

Articles associés :