Diviser des chaînes de texte par délimiteur en plusieurs lignes - 3 astuces rapides

Normalement, vous pouvez utiliser la fonction Texte en colonnes pour diviser 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, une barre oblique, etc. Cependant, parfois, vous pourriez avoir besoin de diviser le contenu des cellules délimitées en plusieurs lignes et répéter les données des autres colonnes comme le montre la capture d'écran ci-dessous. Avez-vous des bonnes méthodes pour gérer cette tâche dans Excel ? Ce tutoriel présentera quelques méthodes efficaces pour accomplir cette tâche dans Excel.

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

Méthode A : Diviser du texte délimité en plusieurs lignes avec du code VBA

Dans cette section, je vais introduire deux codes VBA pour aider à diviser le contenu des cellules qui sont séparées par un certain délimiteur.

Diviser du texte séparé par une virgule, un espace, un point-virgule, etc.

Pour diviser les chaînes de texte qui sont séparées par certains délimiteurs courants, tels qu'une virgule, un espace, un point-virgule, une barre oblique, etc., le code suivant peut vous aider. Veuillez suivre les étapes ci-dessous :

 Remarque : Ce code ne prend pas en charge l'annulation (Undo), il est donc préférable de sauvegarder les données avant d'appliquer ce code.

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

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

2. Dans la fenêtre ouverte, cliquez sur "Insérer" > "Module" pour créer un nouveau module vierge.

3. Ensuite, copiez et collez le code ci-dessous dans le module vierge.

Code VBA : Diviser du texte par 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 exécuter ce code. Ensuite, une boîte de dialogue s'affichera pour vous rappeler de sélectionner les cellules contenant le texte délimité que vous souhaitez diviser, voir la capture d'écran :

2. Ensuite, cliquez sur "OK", une autre boîte de dialogue apparaît pour vous rappeler de taper le séparateur selon lequel vous souhaitez diviser les données. Ici, je tape une virgule et un espace (, ), voir la capture d'écran :

3. Enfin, cliquez sur le bouton "OK". Maintenant, vous verrez que les chaînes de texte sélectionnées sont divisées en lignes en fonction de la virgule et que les données des colonnes relatives sont répétées comme le montrent les captures d'écran ci-dessous :

Diviser du texte séparé par un saut de ligne

Si vos contenus de cellules sont séparés par des sauts de ligne, pour les diviser en plusieurs lignes, voici un autre code VBA qui peut vous aider.

 Remarque : Ce code ne prend pas en charge l'annulation (Undo), il est donc préférable de sauvegarder les données avant d'appliquer ce code.

É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 for Applications".

2. Dans la fenêtre ouverte, cliquez sur "Insérer" > "Module" pour créer un nouveau module vierge.

3. Ensuite, copiez et collez le code ci-dessous dans le module vierge.

Code VBA : Diviser du texte par un saut 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 exécuter ce code. Dans la boîte de dialogue qui apparaît, veuillez sélectionner les cellules que vous souhaitez diviser, voir la capture d'écran :

2. Ensuite, cliquez sur le bouton "OK", les données des cellules sélectionnées sont divisées en lignes comme le montre la capture d'écran ci-dessous :

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

Si vous avez installé "Kutools pour Excel", avec sa fonctionnalité "Diviser les données en plusieurs lignes", vous pouvez diviser les chaînes de texte en plusieurs lignes par n'importe quel délimiteur spécifié. Veuillez suivre les étapes ci-dessous :

Étape 1 : Sélectionnez la fonctionnalité Diviser les données en plusieurs lignes

Cliquez sur "Kutools" > "Fusionner et Diviser" > "Diviser les données en plusieurs lignes", voir la capture d'écran :

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

Dans la boîte de dialogue qui apparaît, utilisez les options suivantes :

• 1). Sélectionnez la liste des cellules que vous souhaitez diviser depuis la zone de texte "Plage (une seule colonne)";
• 2). Ensuite, choisissez le délimiteur qui sépare vos données, ici, je choisirai "Autre" et taperai une virgule et un espace (, ) dans la zone de texte;
• 3). Enfin, cliquez sur le bouton "OK".

Maintenant, les cellules sélectionnées avec des chaînes de texte délimitées ont été converties en plusieurs lignes en fonction du séparateur spécifique, voir la capture d'écran :

 Astuce : Si vous souhaitez restaurer les données d'origine, il vous suffit d'appuyer sur Ctrl + Z pour annuler.

Facile à utiliser ? Intéressé par cette fonctionnalité, veuillez cliquer pour télécharger une version d'essai gratuite pendant 30 jours.

Méthode C : Diviser du texte délimité en plusieurs lignes avec Power Query

Si vous utilisez Office 365 ou Excel 2016 et versions ultérieures, "Power Query" est un outil puissant qui peut vous aider à diviser du texte délimité en plusieurs lignes ou colonnes. Il est utile si vous souhaitez que les données divisées soient actualisées lorsque vos données d'origine changent. Veuillez suivre les étapes ci-dessous pour terminer cette tâche :

É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", voir la capture d'écran :

 Astuce : Dans Excel 2019 et Office 365, cliquez sur Données > À partir d'un tableau/Plage.

2. Dans la boîte de dialogue "Créer un tableau" qui apparaît, cliquez sur le bouton "OK" pour créer un tableau, voir la capture d'écran :

3. Maintenant, la fenêtre "Éditeur Power Query" s'affiche avec les données, voir la capture d'écran :

Étape 2 : Effectuer les transformations dans Power Query

1. Sélectionnez la colonne que vous souhaitez diviser. Ensuite, cliquez sur "Accueil" > "Diviser la colonne" > "Par délimiteur", voir la capture d'écran :

2. Dans la boîte de dialogue "Diviser la colonne par délimiteur" :

Pour diviser les chaînes de texte par une virgule, un espace, un point-virgule, etc., procédez comme suit :

• 1). Choisissez le délimiteur qui sépare vos données dans la liste déroulante "Sélectionner ou entrer un délimiteur";
• 2). Sélectionnez "Chaque occurrence du délimiteur" dans la section "Diviser à";
• 3). Ensuite, cliquez sur "Options avancées" pour développer la section, et sélectionnez "Lignes" sous "Diviser en";
• 4). Enfin, cliquez sur "OK" pour fermer cette boîte de dialogue.

Pour diviser des chaînes de texte en plusieurs lignes par un saut de ligne, procédez comme suit :

• 1). Choisissez "Personnalisé" dans la liste déroulante "Sélectionner ou entrer un délimiteur";
• 2). Depuis la liste déroulante "Insérer des caractères spéciaux", cliquez sur "Saut de ligne", et les caractères "#(lf)" seront insérés automatiquement dans la zone de texte sous "Personnalisé";
• 3). Sélectionnez "Chaque occurrence du délimiteur" dans la section "Diviser à";
• 4). Ensuite, cliquez sur "Options avancées" pour développer la section, et sélectionnez "Lignes" sous "Diviser en";
• 5). Enfin, cliquez sur "OK" pour fermer cette boîte de dialogue.

3. Maintenant, les données sélectionnées ont été divisées en plusieurs lignes comme le montre la capture d'écran ci-dessous :

Étape 3 : Exporter la requête Power Query vers une table Excel

1. Ensuite, vous devez exporter les données dans votre feuille de calcul. Veuillez cliquer sur "Accueil" > "Fermer et charger" > "Fermer et charger / Fermer et charger vers", (ici, je cliquerai sur "Fermer et charger"), voir la capture d'écran :

 Astuce : Cliquer sur Fermer et charger exportera les données vers une nouvelle feuille de calcul ; cliquer sur l'option Fermer et charger vers permettra d'exporter les données vers toute autre feuille dont vous avez besoin.

2. Finalement, les données seront chargées dans une nouvelle feuille de calcul, voir la capture d'écran :

 Astuce : Si vous devez mettre à jour fréquemment vos données dans le tableau d'origine, ne vous inquiétez pas, il vous suffit de faire un clic droit sur le tableau résultat et de cliquer sur "Actualiser" pour obtenir le nouveau résultat dynamiquement.

Articles connexes :