Comment séparer du texte en colonnes dans Excel : noms, adresses et codes

Il est très fréquent de recevoir une feuille où tout est regroupé dans une seule cellule : le prénom et le nom, l’adresse avec le code postal et la ville, ou un code produit qui contient en réalité la famille, le modèle et la couleur. Tant que les données restent ainsi, impossible de trier par nom, de filtrer par ville ou de compter les produits par famille.

Dans ce guide, vous découvrirez trois méthodes pour séparer ce texte en colonnes, quand utiliser chacune et quelles erreurs éviter pour ne pas perdre les zéros en tête ni transformer des données en dates par accident.

Les données d’exemple

Nous utiliserons trois colonnes typiques. Saisissez-les à partir de la cellule A1 pour suivre les exemples :

Avant toute séparation, faites une copie de la feuille et insérez des colonnes vides à droite de celle que vous allez diviser. S’il n’y a pas de place, Excel écrase ce qu’il trouve.

Méthode 1 : Données › Convertir

C’est l’outil classique, disponible dans toutes les versions d’Excel. Il divise une colonne selon un caractère séparateur ou selon des positions fixes, et produit des valeurs statiques.

Séparer selon un délimiteur

1. Sélectionnez la colonne Adresse (B2:B4).
2. Allez dans Données › Convertir.
3. Choisissez Délimité et cliquez sur Suivant.
4. Cochez Virgule et décochez le reste. L’aperçu montre le résultat des colonnes.
5. À la dernière étape, sélectionnez la colonne du code postal dans l’aperçu et choisissez le format Texte pour conserver le zéro initial.
6. Vérifiez la zone Destination : si vous indiquez une autre cellule, par exemple E2, la colonne d’origine reste intacte.
7. Cliquez sur Terminer.

Vous obtenez trois colonnes : rue et numéro, code postal et ville. Comme le séparateur était une virgule suivie d’un espace, les deux dernières colonnes commencent par un espace. Vous pouvez le supprimer avec la fonction SUPPRESPACE ou utiliser la méthode par formules décrite plus bas.

Séparer selon une largeur fixe

Si toutes les valeurs ont la même structure, comme les codes de la colonne C, choisissez Largeur fixe au lieu de Délimité et cliquez dans l’aperçu pour placer les lignes de séparation. C’est pratique avec les fichiers exportés d’anciens systèmes, où chaque champ occupe toujours les mêmes positions.

Méthode 2 : Remplissage instantané (Ctrl+E)

Le Remplissage instantané, disponible depuis Excel 2013, apprend à partir d’un exemple. Il fonctionne très bien avec les noms :

1. En D2, saisissez à la main le prénom de la première personne : Marie.
2. Placez-vous en D3 et appuyez sur Ctrl+E (ou Données › Remplissage instantané).
3. Excel complète le reste de la colonne avec Luc et Claire.
4. Recommencez dans la colonne suivante en saisissant les noms de famille (Dubois Martin) pour extraire tout ce qui suit le premier espace.

Vérifiez toujours le résultat. Si le modèle n’est pas évident, Excel peut se tromper sur certaines lignes. Comme l’outil Convertir, le Remplissage instantané produit des valeurs fixes : si vous modifiez le nom d’origine, la colonne extraite ne se met pas à jour.

Méthode 3 : des formules qui se mettent à jour

Lorsque les données sources changent souvent, mieux vaut utiliser des formules. Ainsi, chaque fois que vous modifiez la cellule d’origine, les colonnes séparées se recalculent.

Excel 365 : TEXTE.AVANT, TEXTE.APRES et FRACTIONNER.TEXTE

Ces fonctions sont disponibles dans Excel pour Microsoft 365 et Excel pour le web. Pour le prénom (Marie) :

=TEXTE.AVANT(A2;" ")

Pour tout ce qui suit le premier espace (Dubois Martin) :

=TEXTE.APRES(A2;" ")

Pour le dernier mot, le numéro d’instance accepte des valeurs négatives, qui comptent à partir de la fin (la formule renvoie Martin) :

=TEXTE.APRES(A2;" ";-1)

FRACTIONNER.TEXTE répartit le texte sur plusieurs colonnes en une seule fois. Elle accepte aussi des séparateurs de plusieurs caractères, ce qui règle le problème de l’espace après la virgule dans les adresses :

=FRACTIONNER.TEXTE(B2;", ")

Elle renvoie 8 rue des Lilas, 01000 et Bourg-en-Bresse dans trois cellules contiguës, sans espaces superflus et avec le code postal en texte. Avec les codes produits, c’est pareil : =FRACTIONNER.TEXTE(C2;"-") renvoie PRD, 2041 et AZ. Laissez les cellules de droite vides pour que le résultat puisse se propager.

Versions antérieures : GAUCHE, STXT, DROITE et CHERCHE

Si vous utilisez Excel 2019 ou une version antérieure, combinez les fonctions de texte classiques. Le prénom correspond à ce qui précède le premier espace :

=GAUCHE(A2;CHERCHE(" ";A2)-1)

Et le reste, à tout ce qui part du caractère suivant l’espace :

=STXT(A2;CHERCHE(" ";A2)+1;NBCAR(A2))

Avec des codes de longueur fixe, il suffit d’indiquer des positions. Pour PRD-2041-AZ :

• Famille : =GAUCHE(C2;3) renvoie PRD.
• Modèle : =STXT(C2;5;4) renvoie 2041 (en texte).
• Couleur : =DROITE(C2;2) renvoie AZ.

Quelle méthode choisir

En pratique : pour un nettoyage ponctuel, Convertir ou le Remplissage instantané ; pour une feuille alimentée chaque semaine avec de nouvelles données, les formules.

Erreurs fréquentes lors de la séparation

• Des zéros en tête qui disparaissent : un code postal comme 01000 devient 1000 si la colonne reste au format Standard. Choisissez Texte dans l’assistant.
• Des dates inattendues : des fragments comme 3-12 ou 1/5 peuvent devenir des dates. Utilisez là aussi le format Texte pour ces colonnes.
• Prénoms composés : avec « Marie Anne Dubois », séparer au premier espace laisse « Anne Dubois » comme nom de famille. Aucune formule n’est parfaite ici ; vérifiez à la main les cas douteux.
• Doubles espaces : deux espaces consécutifs créent des colonnes vides. Nettoyez d’abord avec SUPPRESPACE ou cochez l’option qui traite les séparateurs consécutifs comme un seul.
• Cellules sans séparateur : si une cellule ne contient pas d’espace, CHERCHE renvoie #VALEUR! et TEXTE.AVANT renvoie #N/A. Englobez-les dans SIERREUR, par exemple =SIERREUR(TEXTE.AVANT(A2;" ");A2).

Après la séparation : exporter des données propres

Séparer les données est souvent l’étape qui précède leur import dans un autre logiciel, comme un CRM, une boutique en ligne ou un outil de facturation, qui demandent presque toujours un CSV. Une fois chaque donnée dans sa colonne, vous pouvez utiliser notre outil gratuit pour convertir Excel en CSV et obtenir un fichier prêt à importer. Dans le cas inverse, si vous recevez un CSV qui s’ouvre avec tout dans une seule colonne, vous pouvez convertir le CSV en Excel ou appliquer Convertir avec le bon séparateur.

Conclusion

Séparer du texte en colonnes est l’une des tâches de nettoyage les plus fréquentes. L’outil Convertir règle la plupart des cas ponctuels, le Remplissage instantané est idéal pour les noms et les formules sont la bonne option quand les données changent. Choisissez le format Texte pour les codes et les nombres avec des zéros en tête, et vérifiez toujours le résultat avant de valider la feuille.

Questions fréquentes