Diviser du texte en colonnes dans Excel
Divisez des noms, des codes ou du texte délimité en colonnes avec Convertir, TEXTSPLIT ou le remplissage instantané, puis nettoyez le résultat.
Méthode 1 : utiliser TEXTSPLIT pour un résultat dynamique
TEXTSPLIT, TEXTBEFORE et TEXTAFTER nécessitent Microsoft 365 ou Excel 2024. Avec une région et un code enregistrés sous la forme « North, N-14 » en A2, =TEXTSPLIT(A2,", ") coupe à la virgule suivie d’un espace et déverse les morceaux sur la ligne. Utilisez exactement le séparateur présent dans les données : couper sur "," seul laisserait un espace au début de chaque deuxième morceau. Un séparateur de lignes déverse au contraire vers le bas : =TEXTSPLIT(A2,,";").
Comme TEXTSPLIT est dynamique, les modifications en A2 mettent à jour les résultats. Gardez la zone de déversement vide : une cellule non vide sur le trajet produit #SPILL! (#DÉVERSEMENT! dans Excel en français). Dans une version plus ancienne, utilisez LEFT, MID, RIGHT et FIND pour des schémas stables, ou Convertir pour une conversion ponctuelle.
Méthode 2 : utiliser la commande Convertir
La commande Convertir (Text to Columns) convient le mieux à une division ponctuelle. Sélectionnez uniquement la colonne source, puis choisissez Données > Convertir. Choisissez Délimité pour les virgules, tabulations, points-virgules ou espaces, ou Largeur fixe quand les champs occupent des positions de caractères connues. Prévisualisez les coupures avant de terminer et définissez une destination avec assez de colonnes vides.
La commande écrit des valeurs, pas des formules. Si la destination contient déjà des données, Excel demande s’il faut les remplacer ; choisissez Annuler et indiquez une destination vide, ou travaillez sur une copie. La dernière étape de l’assistant permet aussi de définir une colonne au format Texte, Date ou Standard. Utilisez Texte pour les codes qui commencent par des zéros.
Méthode 3 : remplissage instantané ou formules d’extraction
Le remplissage instantané reconnaît un schéma que vous avez montré. Saisissez le premier résultat à côté de la source et choisissez Données > Remplissage instantané (Ctrl+E). C’est pratique pour des données homogènes, mais il produit des valeurs figées et peut déduire un mauvais schéma sur les exceptions ; vérifiez donc la première ligne, la dernière, les lignes vides et les lignes inhabituelles.
Pour extraire un seul morceau d’une chaîne à séparateur unique, =TEXTBEFORE(A2,", ") renvoie la partie qui précède et =TEXTAFTER(A2,", ") celle qui suit. Si le séparateur est absent, les deux renvoient #N/A, ce qui est un signal utile pour repérer les lignes incomplètes. Des formules compatibles avec d’anciennes versions peuvent utiliser =LEFT(A2,FIND(",",A2)-1) pour le premier morceau et une expression MID pour le reste.
Erreurs fréquentes
Un séparateur espace coupe les noms composés et les adresses en trop de morceaux, et un export peut utiliser un point-virgule là où un autre utilise une virgule : en France, un export CSV emploie souvent le point-virgule, parce que la virgule sert de séparateur décimal. Ne terminez jamais l’assistant sans vérifier son aperçu et sa destination. Si la source est un vrai fichier CSV, l’importer via Données > À partir du texte/CSV ou avec Power Query est plus sûr que de le découper à la main, car un véritable importateur respecte les champs entre guillemets. Conservez les codes sous forme de texte quand les zéros de tête comptent.
À vous de jouer
En B2, divisez A2 à la virgule suivie d’un espace avec TEXTSPLIT. La région arrive en B2 et le code se déverse en C2.
| A | B | C | |
|---|---|---|---|
| 1 | Combined | Region | Code |
| 2 | North, N-14 | ||
| 3 | West, W-08 | ||
| 4 | East, E-22 |
Réponse : =TEXTSPLIT(A2,", ")
Exemple vérifié
Des paires région et code en A2:A4. La dernière ligne n’a pas de séparateur : TEXTBEFORE le signale au lieu de deviner.
| A | |
|---|---|
| 1 | Combined |
| 2 | North, N-14 |
| 3 | West, W-08 |
| 4 | Central |
| Formule | Résultat |
|---|---|
=TEXTSPLIT(A2,", ") | "North" |
=TEXTBEFORE(A3,", ") | "West" |
=TEXTAFTER(A2,", ") | "N-14" |
=LEFT(A3,FIND(",",A3)-1) | "West" |
=COUNTA(TEXTSPLIT(A2,", ")) | 2 |
=TEXTBEFORE(A4,", ") | #N/A |
Erreurs fréquentes
- Écraser des colonnes voisines avec le résultat de l’assistant.
- Choisir Espace alors que des espaces peuvent exister à l’intérieur d’un champ.
- Laisser Excel convertir automatiquement des codes ou des dates ambiguës.
Questions fréquentes
Pourquoi TEXTSPLIT renvoie-t-elle #SPILL! (#DÉVERSEMENT!) ?
Une ou plusieurs cellules de la zone de résultat prévue ne sont pas vides. Videz ou déplacez ces cellules.
Convertir se met-il à jour quand la source change ?
Non. La commande écrit des valeurs figées. Utilisez TEXTSPLIT quand vous avez besoin d’un résultat dynamique.
Quelles versions d’Excel proposent TEXTSPLIT ?
TEXTSPLIT, TEXTBEFORE et TEXTAFTER nécessitent Microsoft 365 ou Excel 2024. Les versions antérieures demandent Convertir ou des formules LEFT/FIND/MID.
Comment conserver les zéros de tête ?
Dans l’assistant, définissez ce champ de sortie au format Texte, ou formatez la destination en Texte à l’avance, le cas échéant.
Mettre la compétence en pratique
Poursuivez avec une leçon FormulaCamp vérifiée (en anglais) : Duplicates and Text to Columns (en anglais) Gratuit
Ouvrir la leçon gratuite (en anglais)