Comment diviser le contenu d'une cellule en plusieurs lignes dans Excel (Scénarios + Comparaison d'outils)
Ce guide vous explique comment diviser le contenu d'une cellule en plusieurs lignes dans Excel. Il couvre deux scénarios courants : l'un où seule la cellule cible est divisée (en ignorant les autres colonnes), et un autre où la cellule est divisée tout en conservant les données du reste de la ligne. Plusieurs méthodes sont proposées pour gérer efficacement ces deux situations, allant des fonctions natives d'Excel à des outils avancés comme Power Query, VBA et des plugins tiers.
Deux scénarios de division courants
La division des données peut prendre plusieurs formes selon que vous devez conserver ou non les autres colonnes de votre jeu de données. Nous définissons ici les deux approches principales afin que vous puissiez choisir celle qui convient le mieux à vos besoins.
Scénario 1 : Diviser uniquement la cellule cible (ignorer les autres colonnes)
Parfois, tout ce qui vous intéresse, ce sont les valeurs contenues dans une seule cellule. Vous souhaitez les diviser en lignes pour une lecture plus facile, sans dupliquer les données associées dans la même ligne.
Exemple
A,B,C
Résultat :
A
B
C
Cas d'utilisation : Tâches légères de nettoyage ou d'affichage de données où les autres colonnes ne sont pas pertinentes.
Scénario 2 : Diviser la cellule tout en conservant les données des autres colonnes
Dans d'autres cas, vous aurez besoin que chaque valeur divisée soit associée aux données de la ligne d'origine — essentiellement en élargissant la ligne en plusieurs entrées.
Exemple
USA | A,B,C
Résultat :
USA | A
USA | B
USA | C
Cas d'utilisation : Normalisation des données, où il est essentiel de maintenir les relations entre les colonnes.
Quand avez-vous besoin de diviser une cellule en plusieurs lignes ?
Méthodes étape par étape pour diviser les cellules
- Méthode 1 : Fonctions natives d'Excel (Texte en colonnes + Option de collage Transposer)
- Méthode 2 : Fonction TRANSPOSER(TEXTESPLIT)) – (Microsoft 365)
- Méthode 3 : Power Query – Diviser et développer les lignes (Microsoft 2016 et versions ultérieures)
- Méthode 4 : Plugin Kutools – Clics pour diviser
- Méthode 5 : Script VBA – Contrôle complet personnalisé
Quand avez-vous besoin de diviser une cellule en plusieurs lignes ?
Avant de plonger dans les méthodes, il est important de comprendre pourquoi vous pourriez avoir besoin de diviser une cellule. Cette opération fait souvent partie des workflows de nettoyage, de restructuration ou de visualisation des données.
Les cas d'utilisation typiques incluent :
- Plusieurs valeurs dans une seule colonne : telles que des étiquettes, des ID ou des adresses stockées ensemble.
- Tableaux croisés dynamiques ou graphiques : qui fonctionnent mieux avec des formats de données normalisés.
- Nettoyage des données importées : surtout à partir des exports CRM ou des outils d'enquête.
- Standardisation des données : rendant votre table adaptée à la base de données et prête pour les analyses.
Méthodes étape par étape pour diviser les cellules
Maintenant que vous connaissez votre scénario, explorons différentes méthodes pour effectuer la division — des fonctionnalités natives aux automatisations avancées. Choisissez la méthode qui correspond à votre niveau technique et à la fréquence d'utilisation.
Méthode 1 : Fonctions natives d'Excel (Texte en colonnes + Option de collage Transposer)
Il s'agit d'une méthode simple et manuelle pour les cas uniques utilisant uniquement les fonctionnalités natives d'Excel.
Adaptée pour : Scénario 1 Diviser uniquement la cellule cible (ignorer les autres colonnes)
Étapes :
Étape 1. Sélectionnez la cellule.
Étape 2. Sélectionnez Données > Convertir en colonnes.
Étape 3. Dans l'assistant Convertir en colonnes,
Sélectionnez Délimité, cliquez sur Suivant.
Sélectionnez Virgule, cliquez sur Suivant.
Sélectionnez la cellule de destination, cliquez sur Terminer.
La cellule est maintenant divisée par une virgule en plusieurs colonnes.
Étape 4. Sélectionnez les cellules divisées, puis appuyez sur Ctrl + C pour les copier. Faites un clic droit sur la cellule de destination, sous Options de collage, sélectionnez l'icône Transposer.
Le contenu est transposé en lignes.
Avantages | Inconvénients |
|
|
Méthode 2 : Fonction TRANSPOSER(TEXTESPLIT)) – (Microsoft 365)
Cette méthode combine la fonction TEXTESPLIT (qui divise le texte en fonction d'un délimiteur) avec TRANSPOSER pour afficher instantanément les valeurs divisées verticalement en lignes.
Adaptée pour : Scénario 1 Diviser uniquement la cellule cible (ignorer les autres colonnes)
Étapes :
Étape 1. Entrez la formule dans une cellule vide où vous souhaitez que la liste verticale commence.
Exemple de formule : cellule A1 est la cellule cible qui sera divisée en lignes.
Dans ce cas, la formule est
Étape 2. Appuyez sur la touche Entrée.
Avantages | Inconvénients |
|
|
Méthode 3 : Power Query – Diviser et développer les lignes (Microsoft 2016 et versions ultérieures)
Power Query est idéal pour les opérations structurées et répétables. Il est puissant, rafraîchissant et excellent pour les transformations de données par lots.
Adaptée pour : Les deux scénarios 1 (Diviser uniquement la cellule cible) et 2 (Diviser la cellule tout en conservant les données des autres colonnes)
Étapes : Prenons l'exemple du Scénario 1 (Diviser uniquement la cellule cible)
Étape 1. Sélectionnez votre plage, allez dans Données > Obtenir et transformer > À partir d’un tableau/plage.
un
Étape 2. Dans Créer un tableau, cliquez sur OK. (Si votre plage sélectionnée a des en-têtes, cochez Ma table a des en-têtes.)
Étape 3. Dans l'éditeur Power Query, utilisez Diviser la colonne par délimiteur.
Étape 4. Sélectionnez ou entrez le délimiteur, puis cliquez sur Avancé pour développer les options, sélectionnez Diviser en lignes, puis cliquez sur OK.
Étape 5. Sélectionnez Fermer et charger > Fermer et charger ou Fermer et charger vers...
Fermer et charger
Fonction : Charge directement les résultats de la requête à l'emplacement par défaut.
Fermer et charger vers...
Fonction : Offre des options de chargement personnalisables, permettant aux utilisateurs de choisir où et comment les données sont stockées. Idéal pour : Rapports réguliers, grands jeux de données.
Les mêmes étapes s'appliquent au Scénario 2 (Diviser les cellules tout en conservant les données des autres colonnes) - examinons les résultats.
La plage cible des cellules :
Résultat :
Remarque : Dans la fenêtre de l'éditeur Power Query, sélectionnez uniquement la colonne cible que vous souhaitez diviser en lignes, puis cliquez sur Par délimiteur sous Diviser la colonne.
Avantages | Inconvénients |
|
|
Astuce : Ajoutez des étapes de Nettoyage et Suppression pour corriger les espaces blancs ou caractères spéciaux.
Méthode 4 : Plugin Kutools – Clics pour diviser
Si vous divisez fréquemment des cellules et préférez une solution basée sur une interface graphique, Kutools est un excellent outil. Il simplifie des opérations complexes en quelques clics.
Adaptée pour : Les deux scénarios 1 (Diviser uniquement la cellule cible) et 2 (Diviser la cellule tout en conservant les données des autres colonnes)
Étapes : Scénario 1 (Diviser uniquement la cellule cible)
Étape 1. Sélectionnez la cellule cible.
Étape 2. Allez dans Kutools > Fusionner et diviser > Diviser les cellules.
Étape 3. Choisissez Diviser en lignes et définissez le délimiteur. Cliquez sur OK./p>
Étape 4. Sélectionnez une cellule de destination. Cliquez sur OK.
Étapes : Scénario 2 (Diviser la cellule tout en conservant les données des autres colonnes)
Étape 1. Sélectionnez la colonne que vous souhaitez diviser en lignes.
Étape 2. Allez dans Kutools > Fusionner et diviser > Diviser les données en lignes.
Étape 3. Définissez le délimiteur. Cliquez sur OK.
Les mêmes étapes s'appliquent au Scénario 2 (Diviser les cellules tout en conservant les données des autres colonnes) - examinons les résultats.
La plage cible des cellules :
Résultat :
Avantages | Inconvénients |
|
|
Remarque : La version d'essai de 30 jours offre toutes les fonctionnalités pour les tests.
Méthode 5 : Script VBA – Contrôle total personnalisé
Pour les utilisateurs ayant des connaissances en programmation, VBA offre une flexibilité ultime. Vous pouvez personnaliser les scripts pour correspondre exactement à la disposition de vos données et aux règles métier.
Adaptée pour : Les deux scénarios 1 (Diviser uniquement la cellule cible) et 2 (Diviser la cellule tout en conservant les données des autres colonnes)
Étapes :
Étape 1. Appuyez sur Alt + F11 pour ouvrir l'éditeur VBA.
Étape 2. Cliquez sur Insertion > Module.
Étape 3. Copiez et collez le VBA ci-dessous.
Scénario 1 Diviser uniquement la cellule cible
'Update by Extendoffice
Sub SplitCellToRows_Simple()
Dim InputCell As Range
Dim Values As Variant
Dim i As Long
Set InputCell = Range("A1") ' change A1 to your target cell
Values = Split(InputCell.Value, ",")
For i = 0 To UBound(Values)
InputCell.Offset(i, 1).Value = Trim(Values(i))
Next i
End Sub
Scénario 2 Diviser la cellule tout en conservant les données des autres colonnes
'Update by Extendoffice
Sub SplitRowsWithOtherColumns()
Dim ws As Worksheet
Dim LastRow As Long, i As Long
Dim arr As Variant, j As Long
Dim SplitVals As Variant
Dim CurrentRow As Long
Set ws = ActiveSheet
LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 2 Step -1
If InStr(ws.Cells(i, 2).Value, ",") > 0 Then
SplitVals = Split(ws.Cells(i, 2).Value, ",")
For j = UBound(SplitVals) To 1 Step -1
ws.Rows(i + 1).Insert Shift:=xlDown
ws.Cells(i + 1, 1).Value = ws.Cells(i, 1).Value
ws.Cells(i + 1, 2).Value = Trim(SplitVals(j))
Next j
ws.Cells(i, 2).Value = Trim(SplitVals(0))
End If
Next i
End Sub
Remarque : Ce script VBA est configuré pour fonctionner spécifiquement lorsque vos données sont situées dans les colonnes A à B et que les valeurs à diviser sont dans la colonne B.
Inconvénients |
|
💡 Astuces :
- Sauvegardez toujours vos données avant d'exécuter une macro.
- Remplacez "," dans le script par votre délimiteur actuel (par exemple, ";" pour les points-virgules ou "|" pour les caractères pipe) pour correspondre au format de vos données.
Problèmes courants
- Lignes ou colonnes vides : Généralement causées par des délimiteurs en fin de chaîne ou des espaces cachés.
- Erreurs de cellules fusionnées : Démontez les cellules avant de commencer.
- Power Query ne se met pas à jour : N'oubliez pas de cliquer sur Actualiser après avoir modifié les données sources.
- Mauvais délimiteur : Une erreur entre point-virgule et virgule peut fausser tous les résultats.
Articles connexes :
- Utilitaire Transposer plage : Convertissez facilement plusieurs lignes en colonnes et lignes dans Excel
- Outil de conversion d'unités : Convertissez rapidement un nombre décimal en binaire/octal/hexadécimal ou vice versa
Meilleurs outils de productivité Office
Dynamisez vos compétences Excel avec Kutools pour Excel et découvrez une efficacité inégalée. Kutools pour Excel propose plus de300 fonctionnalités avancées pour booster votre productivité et gagner du temps. Cliquez ici pour obtenir la fonctionnalité la plus essentielle pour vous...
Office Tab apporte une interface à onglets à Office, et facilite grandement votre travail
- Activez la modification et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
- Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, au lieu d’ouvrir de nouvelles fenêtres.
- Augmentez votre productivité de50% et réduisez des centaines de clics de souris chaque jour!