Dans Excel, il existe plusieurs raisons pour lesquelles vous pourriez avoir besoin de fractionner les données des cellules. Par exemple, les données brutes peuvent contenir plusieurs informations regroupées dans une cellule, telles que des noms complets ou des adresses. Le fractionnement de ces cellules vous permet de séparer différents types d'informations, ce qui facilite le nettoyage et l'analyse des données. Cet article vous servira de guide complet, montrant différentes façons de diviser des cellules en lignes ou en colonnes en fonction de séparateurs spécifiques.

Diviser les cellules dans Excel en plusieurs colonnes

• Avec l'assistant Texte vers colonne
• Facilement avec Kutools
• Avec Flash Fill (Excel 2013 et versions ultérieures)
• Avec des formules

Diviser les cellules dans Excel en plusieurs lignes

• Avec la fonction TEXTSPLIT (Excel 365)
• Facilement avec Kutools
• Avec code VBA
• Avec Power Query (Excel 2016 et versions ultérieures)

Vidéo

Diviser les cellules dans Excel en plusieurs colonnes

Comme illustré dans la capture d'écran suivante, supposons que vous disposiez d'une liste de noms complets et que vous souhaitiez diviser chaque nom complet en noms et prénoms distincts et placer les données fractionnées dans des colonnes distinctes. Cette section présente quatre façons de vous aider à accomplir cette tâche.

Diviser les cellules en plusieurs colonnes avec l'assistant Texte vers colonne

Pour diviser des cellules en plusieurs colonnes en fonction d'un séparateur spécifique, une méthode couramment utilisée est la Texte en colonne Assistant dans Excel. Ici, je vais vous montrer étape par étape comment utiliser cet assistant pour obtenir le résultat souhaité.

Étape 1 : Sélectionnez les cellules que vous souhaitez diviser et ouvrez l'assistant Texte en colonnes

Dans ce cas, je sélectionne la plage A2: A8, qui contient les noms complets. Allez ensuite au Données onglet, cliquez sur Texte aux colonnes ouvrir le Texte aux colonnes Assistant.

Etape 2 : Configurer les étapes une par une dans l'assistant

1. Dans le Etape 1 de 3 assistant, sélectionnez le Délimité option, puis cliquez sur le Suivant .
   

   

2. Dans le Etape 2 de 3 l'assistant, sélectionnez les délimiteurs de vos données, puis cliquez sur l'icône Suivant pour continuer.
   
   Dans ce cas, comme je dois diviser les noms complets en prénoms et noms de famille en fonction des espaces, je sélectionne uniquement le Space case à cocher dans le Delimiters .

   

   Notes:

   • Si le délimiteur dont vous avez besoin n'est pas affiché dans cette section, vous pouvez sélectionner le Autre case à cocher et entrez votre propre délimiteur dans la zone de texte.
   • Pour diviser les cellules par saut de ligne, vous pouvez sélectionner le Autre case à cocher et appuyez sur Ctrl + J clés ensemble.
     
     
3. Dans le dernier assistant, vous devez configurer comme suit :
   
   1) Dans le dentaire , sélectionnez une cellule pour placer les données fractionnées. Ici, je choisis la cellule C2.

   2) Cliquez sur le Finition .

   

Résultat

Les noms complets dans les cellules sélectionnées sont séparés en prénom et nom et situés dans des colonnes différentes.

Divisez facilement les cellules en plusieurs colonnes à l'aide de Kutools

Comme vous pouvez le voir, la Texte aux colonnes L'assistant nécessite plusieurs étapes pour terminer la tâche. Si vous avez besoin d'une méthode plus simple, le Cellules divisés caractéristique de Kutools for Excel est fortement recommandé. Avec cette fonctionnalité, vous pouvez facilement diviser les cellules en plusieurs colonnes ou lignes en fonction d'un délimiteur spécifique, en complétant les paramètres dans une seule boîte de dialogue.

Après avoir installé Kutools pour Excel, sélectionnez Kutools > Fusionner et fractionner > Cellules divisés ouvrir le Cellules divisés boite de dialogue.

1. Sélectionnez la plage de cellules contenant le texte que vous souhaitez diviser.
2. Sélectionnez le Fractionner en colonnes option.
3. Sélectionnez Space (ou tout délimiteur dont vous avez besoin) et cliquez sur OK.
   
   
4. Sélectionnez une cellule de destination et cliquez sur OK pour obtenir toutes les données fractionnées.
   
   

Diviser les cellules en plusieurs colonnes avec Flash Fill

Passons maintenant à la troisième méthode, dite Remplissage Flash. Introduit dans Excel 2013, Remplissage Flash conçu pour remplir automatiquement vos données lorsqu'il détecte un modèle. Dans cette section, je vais montrer comment utiliser la fonction Flash Fill pour séparer les noms et prénoms des noms complets dans une seule colonne.

Étape 1 : saisissez manuellement les premières données fractionnées dans la cellule adjacente à la colonne d'origine.

Dans ce cas, je vais diviser les noms complets de la colonne A en prénoms et noms de famille distincts. Le premier nom complet est dans la cellule A2, donc je sélectionne la cellule B2 adjacente et je tape le prénom. Voir capture d'écran :

Étape 2 : Appliquez le Flash Fill pour remplir automatiquement tous les prénoms

Commencez à taper le deuxième prénom dans la cellule sous B2 (qui est B3), puis Excel reconnaîtra le modèle et générera un aperçu du reste des prénoms, et vous devrez appuyer sur Entrer pour accepter l'aperçu.

Désormais, tous les prénoms des noms complets de la colonne A sont séparés dans la colonne B.

Étape 3 : Obtenir les noms de famille des noms complets dans une autre colonne

Vous devez répéter les étapes 1 et 2 ci-dessus pour séparer les noms de famille des noms complets de la colonne A dans la colonne à côté de la colonne des prénoms.

Résultat

Diviser les cellules en plusieurs colonnes avec des formules

Les méthodes ci-dessus ne sont pas dynamiques, ce qui signifie que si les données source changent, nous devons réexécuter le même processus. Prenez le même exemple que ci-dessus, pour diviser la liste des noms complets dans la colonne A en noms et prénoms séparés et que les données fractionnées se mettent à jour automatiquement avec toute modification des données source, veuillez essayer l'une des formules suivantes

Utilisez les fonctions TEXTE pour diviser les cellules en colonnes par certains délimiteurs

Les formules fournies dans cette section sont disponibles dans toutes les versions d'Excel. Pour appliquer les formules, procédez comme suit.

Étape 1 : Extrayez le texte avant le premier délimiteur (prénoms dans ce cas)

1. Sélectionnez une cellule (C2 dans ce cas) pour afficher le prénom, entrez la formule suivante et appuyez sur Entrer pour obtenir le prénom en A2.
   

   =LEFT(A2,SEARCH(" ",A2)-1)

   
2. Sélectionnez cette cellule de résultat et faites glisser sa poignée de remplissage automatique vers le bas pour obtenir le reste des prénoms.
   
   

Étape 2 : Extrayez le texte après le premier délimiteur (les noms de famille dans ce cas)

1. Sélectionnez une cellule (D2 dans ce cas) pour afficher le nom de famille, entrez la formule suivante et appuyez sur Entrer pour obtenir le nom de famille en A2.
   

   =RIGHT(A2,LEN(A2)-SEARCH(" ",A2))

   
2. Sélectionnez cette cellule de résultat et faites glisser sa poignée de remplissage automatique vers le bas pour obtenir le reste des noms de famille.
   
   

Utilisez la fonction TEXTSPLIT pour diviser les cellules en colonnes par un séparateur spécifique

Si vous utilisez Excel pour Microsoft 365, Fonction TEXTSPLIT est plus recommandé. Veuillez procéder comme suit.

Étape 1 : Sélectionnez une cellule pour afficher le résultat. Ici je sélectionne la cellule C2

Étape 2 : Entrez la formule ci-dessous et appuyez sur Entrée

=TEXTSPLIT(A2," ")

Vous pouvez voir que tout le texte séparé par des espaces en A2 est divisé en différentes colonnes.

Étape 3 : faites glisser la formule pour obtenir tous les résultats

Sélectionnez les cellules de résultat dans la même ligne, puis faites glisser la poignée de remplissage automatique vers le bas pour obtenir tous les résultats.

Diviser les cellules dans Excel en plusieurs lignes

Comme le montre la capture d'écran ci-dessous, il existe une liste de détails de commande dans la plage A2: A4, et les données doivent être divisées à l'aide d'une barre oblique pour extraire différents types d'informations telles que l'article, la quantité, le prix unitaire et la date. Pour accomplir cette tâche, cette section présente 3 méthodes.

Diviser les cellules en plusieurs lignes avec la fonction TEXTSPLIT

Si vous utilisez Excel pour Microsoft 365, la méthode de la fonction TEXTSPLIT peut facilement vous aider. Veuillez procéder comme suit.

Étape 1 : Sélectionnez une cellule pour afficher le résultat. Ici je sélectionne la cellule B6

Étape 2 : Tapez la formule ci-dessous et appuyez sur Entrée

=TEXTSPLIT(A2,,"/")

Tout le texte en A2 est divisé en lignes séparées en fonction du séparateur "barre oblique".

Pour diviser les données des cellules A3 et A4 en lignes individuelles basées sur des barres obliques, répétez simplement les étapes 1 et 2 avec les formules appropriées ci-dessous.

Formule en C6 :

=TEXTSPLIT(A3,,"/")

Formule en D6 :

=TEXTSPLIT(A4,,"/")

Résultat

Divisez facilement les cellules en plusieurs lignes à l'aide de Kutools

Bien que la fonctionnalité TEXTSPLIT d'Excel soit très utile, elle est limitée aux utilisateurs d'Excel pour Microsoft 365. De plus, si vous avez plusieurs cellules dans une colonne à diviser, vous devrez appliquer différentes formules individuellement à chaque cellule pour obtenir les résultats. En revanche, Kutools for Excel's Cellules divisés La fonctionnalité fonctionne sur toutes les versions d'Excel. Il fournit une solution simple et efficace pour diviser les cellules en plusieurs lignes ou colonnes à la fois en quelques clics.

Après avoir installé Kutools pour Excelcliquez Kutools > Fusionner et fractionner > Cellules divisés ouvrir le Cellules divisés boite de dialogue.

1. Sélectionnez la plage de cellules contenant le texte que vous souhaitez diviser.
2. Sélectionnez le Fractionner en lignes option.
3. Sélectionnez un délimiteur dont vous avez besoin (ici, je sélectionne le Autre option et entrez une barre oblique), puis cliquez sur OK.
   
   
4. Sélectionnez une cellule de destination et cliquez sur OK pour obtenir toutes les données fractionnées
   
   

Diviser les cellules en plusieurs lignes avec le code VBA

Cette section fournit un code VBA pour vous permettre de diviser facilement des cellules en plusieurs lignes dans Excel. Veuillez procéder comme suit.

Étape 1 : Ouvrez la fenêtre Microsoft Visual Basic pour Applications

Appuyez sur la touche autre + F11 touches pour ouvrir cette fenêtre.

Étape 2 : Insérez un module et entrez le code VBA

Cliquez insérer > Module, puis copiez et collez le code VBA suivant dans le Module (Code) fenêtre.

Code VBA : diviser les cellules en plusieurs lignes dans Excel

Option Explicit

Sub SplitCellsToRows()
'Updated by Extendoffice 20230727
    Dim inputRng As Range
    Dim outputRng As Range
    Dim cell As Range
    Dim splitValues() As String
    Dim delimiter As String
    Dim i As Long
    Dim columnOffset As Long
    On Error Resume Next
    
    Set inputRng = Application.InputBox("Please select the input range", "Kutools for Excel", Type:=8) ' Ask user to select input range
    If inputRng Is Nothing Then Exit Sub ' If the user clicked Cancel or entered nothing, exit the sub
    Set outputRng = Application.InputBox("Please select the output range", "Kutools for Excel", Type:=8) ' Ask user to select output range
    If outputRng Is Nothing Then Exit Sub ' If the user clicked Cancel or entered nothing, exit the sub
    delimiter = Application.InputBox("Please enter the delimiter to split the cell contents", "Kutools for Excel", Type:=2) ' Ask user for delimiter
    If delimiter = "" Then Exit Sub ' If the user clicked Cancel or entered nothing, exit the sub
    If delimiter = "" Or delimiter = "False" Then Exit Sub ' If the user clicked Cancel or entered nothing, exit the sub
    
    Application.ScreenUpdating = False
    
    columnOffset = 0
    For Each cell In inputRng
        If InStr(cell.Value, delimiter) > 0 Then
            splitValues = Split(cell.Value, delimiter)
            For i = LBound(splitValues) To UBound(splitValues)
                outputRng.Offset(i, columnOffset).Value = splitValues(i)
            Next i
            columnOffset = columnOffset + 1
        Else
            outputRng.Offset(0, columnOffset).Value = cell.Value
            columnOffset = columnOffset + 1
        End If
    Next cell
    
    Application.ScreenUpdating = True
End Sub

Étape 3 : Exécutez le code VBA

Appuyez sur la touche F5 clé pour exécuter le code. Ensuite, vous devez effectuer les configurations suivantes.

1. Une boîte de dialogue apparaîtra vous invitant à sélectionner les cellules avec les données que vous souhaitez diviser (ici, je sélectionne la plage A2: A4). Après avoir fait votre sélection, cliquez sur OK.
   
   
2. Dans la deuxième boîte de dialogue contextuelle, vous devez sélectionner la plage de sortie (ici, je sélectionne la cellule B6), puis cliquez sur OK.
   
   
3. Dans la dernière boîte de dialogue, saisissez le délimiteur utilisé pour fractionner le contenu de la cellule (ici, je saisis une barre oblique) puis cliquez sur le OK .
   
   

Résultat

Les cellules de la plage sélectionnée sont divisées en plusieurs lignes en même temps.

Divisez les cellules en plusieurs lignes avec Power Query

Une autre méthode pour diviser les cellules en plusieurs lignes par certains délimiteurs consiste à utiliser Power Query, ce qui peut également faire en sorte que les données fractionnées changent dynamiquement avec les données source. L'inconvénient de cette méthode est qu'elle nécessite plusieurs étapes. Plongeons-nous pour voir comment cela fonctionne.

Étape 1 : Sélectionnez les cellules que vous souhaitez diviser en plusieurs lignes, sélectionnez Données > À partir du tableau/de la plage

Étape 2 : convertir les cellules sélectionnées en tableau

Si les cellules sélectionnées ne sont pas au format tableau Excel, un Créer un tableau boîte de dialogue apparaîtra. Dans cette boîte de dialogue, il vous suffit de vérifier si Excel a sélectionné correctement la plage de cellules sélectionnée, de marquer si votre tableau a un en-tête, puis de cliquer sur le OK .
Si les cellules sélectionnées sont des tableaux Excel, passez à l'étape 3.

Étape 3 : Choisissez Fractionner la colonne par délimiteur

A Tableau - Power Query Éditeur la fenêtre apparaît, cliquez sur Colonne divisée > Par délimiteur sous le Accueil languette.

Étape 4 : Configurer la boîte de dialogue Fractionner la colonne par délimiteur

1. Dans le Sélectionnez ou entrez le délimiteur section, spécifiez un délimiteur pour diviser le texte (Ici, je choisis Coutume et entrez une barre oblique / dans la zone de texte).
2. Élargir la options avancées section (qui est pliée par défaut) et sélectionnez la rangées option.
3. Dans le Citation Caractère section, choisissez Aucun dans la liste déroulante;
4. Cliquez OK.
   
   

Étape 5 : Enregistrer et charger les données fractionnées

1. Dans ce cas, comme je dois spécifier une destination personnalisée pour mes données fractionnées, je clique sur Fermer et charger > Fermer et charger vers.
   
   
   Conseil : Pour charger les données fractionnées dans une nouvelle feuille de calcul, choisissez Fermer et charger option.
2. Dans le Importer des données boîte de dialogue, choisissez le Feuille de calcul existante option, sélectionnez une cellule pour localiser les données fractionnées, puis cliquez sur OK.
   
   

Résultat

Ensuite, toutes les cellules de la plage sélectionnée sont divisées en différentes lignes dans la même colonne par le délimiteur spécifié.

En conclusion, cet article a exploré différentes méthodes pour diviser les cellules en plusieurs colonnes ou lignes dans Excel. Quelle que soit l'approche que vous choisissez, la maîtrise de ces techniques peut grandement améliorer votre efficacité lorsque vous traitez des données dans Excel. Continuez à explorer et vous trouverez la méthode qui vous convient le mieux.

Articles Relatifs