Vous avez une colonne Excel remplie de valeurs comme « Durand Nathan », « Paris – France » ou « REF-2024-015 » et vous devez répartir chaque élément dans plusieurs colonnes ? C’est ce qu’on appelle déconcaténer des données.
À l’inverse de la concaténation, qui consiste à rassembler plusieurs informations dans une seule cellule, la déconcaténation sépare un texte selon un ou plusieurs séparateurs : un espace, une virgule, un tiret, un point-virgule, etc.
Dans Excel, plusieurs méthodes permettent d’y parvenir. La plus rapide repose sur la fonction FRACTIONNER.TEXTE dans les versions récentes. Pour les anciennes versions, on peut combiner des fonctions comme GAUCHE, STXT et TROUVE. Enfin, Power Query devient particulièrement pratique dès que les données doivent être nettoyées ou actualisées régulièrement.
Déconcaténer avec la fonction FRACTIONNER.TEXTE
Si vous utilisez Microsoft 365 ou une version récente d’Excel, la fonction FRACTIONNER.TEXTE est généralement la solution la plus simple. Elle permet de découper automatiquement le contenu d’une cellule en plusieurs colonnes ou en plusieurs lignes.
Imaginons que la cellule A2 contienne :
Durand Nathan
Pour séparer le nom et le prénom à partir de l’espace, saisissez la formule suivante :
=FRACTIONNER.TEXTE(A2; » « )
Excel place alors automatiquement Durand dans une cellule et Nathan dans la cellule voisine. Cette répartition automatique utilise le mécanisme de « déversement » des formules dynamiques.
La syntaxe générale est la suivante :
=FRACTIONNER.TEXTE(texte; séparateur_colonnes; [séparateur_lignes]; [ignorer_vide]; [mode_correspondance]; [remplissage])
Dans la plupart des cas, les deux premiers arguments suffisent. Le premier indique la cellule à découper et le second précise le séparateur utilisé.
Choisir le bon séparateur
Le séparateur doit être indiqué entre guillemets. Voici quelques exemples courants :
- =FRACTIONNER.TEXTE(A2; » « ) sépare les mots à chaque espace ;
- =FRACTIONNER.TEXTE(A2; », ») sépare les éléments à chaque virgule ;
- =FRACTIONNER.TEXTE(A2; »; ») utilise le point-virgule comme séparateur ;
- =FRACTIONNER.TEXTE(A2; »-« ) découpe une référence construite avec des tirets ;
- =FRACTIONNER.TEXTE(A2; »/ ») sépare les éléments d’un chemin ou d’une date écrite sous forme de texte.
Par exemple, si A2 contient REF-2024-015, la formule suivante produit trois colonnes :
=FRACTIONNER.TEXTE(A2; »-« )
Le résultat sera REF, 2024 et 015. Attention : selon le format appliqué, Excel peut interpréter 015 comme le nombre 15. Si les zéros initiaux sont importants, il faudra conserver le résultat au format texte.
Décomposer un texte sur plusieurs lignes
La fonction peut également répartir les éléments verticalement. Pour cela, utilisez le troisième argument, correspondant au séparateur de lignes.
Supposons que A2 contienne la liste suivante :
Excel;VBA;Power Query
La formule ci-dessous répartit les trois éléments dans des cellules situées les unes sous les autres :
=FRACTIONNER.TEXTE(A2;; »; »)
Le premier séparateur est laissé vide, car nous ne souhaitons pas créer plusieurs colonnes. Le point-virgule, indiqué en troisième argument, sert de séparateur de lignes.
Cette possibilité est intéressante pour transformer une liste stockée dans une cellule en une liste exploitable dans un tableau Excel, notamment pour effectuer ensuite un filtre, un tri ou une recherche.
Utiliser plusieurs séparateurs dans une même formule
Les données réelles sont rarement parfaitement homogènes. Certaines cellules peuvent utiliser une virgule, d’autres un point-virgule ou un espace. La fonction FRACTIONNER.TEXTE accepte plusieurs séparateurs sous forme de constante matricielle.
Par exemple :
=FRACTIONNER.TEXTE(A2;{« ; »; », »})
Cette formule découpe le contenu de A2 lorsqu’elle rencontre un point-virgule ou une virgule.
Pour séparer avec un espace ou un tiret, utilisez :
=FRACTIONNER.TEXTE(A2;{ » « ; »-« })
Cette méthode est pratique lorsque les données proviennent de sources différentes. Elle évite de multiplier les formules ou de modifier manuellement la colonne source.
Ignorer les cellules vides
Un problème fréquent apparaît lorsqu’un texte contient deux séparateurs consécutifs. Prenons l’exemple suivant :
Durand;;Nathan
Le deuxième argument de séparation est le point-virgule. Sans réglage particulier, Excel peut créer une cellule vide entre Durand et Nathan.
Pour ignorer les éléments vides, utilisez le quatrième argument avec la valeur VRAI :
=FRACTIONNER.TEXTE(A2; »; »;;VRAI)
Les arguments laissés vides sont signalés par deux points-virgules consécutifs. Cette écriture peut sembler un peu curieuse au début, mais elle permet de conserver la position des arguments facultatifs sans les renseigner.
Déconcaténer avec les fonctions classiques d’Excel
Vous ne disposez pas de la fonction FRACTIONNER.TEXTE ? Pas de panique. Il est possible de décomposer une cellule avec les fonctions Excel traditionnelles. La formule sera souvent plus longue, mais elle fonctionne dans de nombreuses versions anciennes.
Supposons que A2 contienne Durand Nathan. Pour récupérer le premier mot, utilisez :
=GAUCHE(A2;TROUVE( » « ;A2)-1)
La fonction TROUVE repère la position du premier espace. La fonction GAUCHE extrait ensuite tous les caractères situés avant cet espace.
Pour récupérer le texte situé après le premier espace :
=STXT(A2;TROUVE( » « ;A2)+1;NBCAR(A2))
STXT commence juste après l’espace et extrait le reste du contenu. Le nombre de caractères demandé peut être supérieur à la longueur réelle du texte : Excel s’arrête automatiquement à la fin de la cellule.
Cette méthode convient pour séparer deux éléments, mais elle devient moins confortable si le texte contient trois, quatre ou davantage de parties.
Éviter les erreurs lorsque le séparateur est absent
Que se passe-t-il si certaines cellules ne contiennent pas d’espace ? La fonction TROUVE renvoie alors une erreur, ce qui entraîne également une erreur dans la formule.
Pour afficher une cellule vide plutôt qu’un message d’erreur, vous pouvez utiliser SIERREUR :
=SIERREUR(GAUCHE(A2;TROUVE( » « ;A2)-1); » »)
La formule tente de récupérer le premier élément. Si aucun espace n’est trouvé, elle renvoie une chaîne vide.
Vous pouvez aussi prévoir un résultat différent, par exemple le contenu complet de la cellule :
=SIERREUR(GAUCHE(A2;TROUVE( » « ;A2)-1);A2)
Ce réflexe est important dans un fichier destiné à être alimenté régulièrement. Une formule qui fonctionne sur dix lignes peut très bien produire des erreurs dès qu’une valeur atypique apparaît.
Utiliser TEXTEAVANT et TEXTEAPRES
Dans les versions récentes d’Excel, les fonctions TEXTEAVANT et TEXTEAPRES offrent une autre approche très lisible.
Pour récupérer tout ce qui se trouve avant le premier espace :
=TEXTEAVANT(A2; » « )
Pour récupérer tout ce qui se trouve après le premier espace :
=TEXTEAPRES(A2; » « )
Si A2 contient Durand Nathan Pierre, TEXTEAVANT renvoie Durand et TEXTEAPRES renvoie Nathan Pierre.
Vous pouvez demander à Excel de travailler avec une occurrence précise du séparateur. Par exemple, pour récupérer le texte situé après le deuxième tiret :
=TEXTEAPRES(A2; »-« ;2)
Ces fonctions sont particulièrement utiles lorsque vous souhaitez extraire une partie précise d’une référence, d’un identifiant ou d’une adresse e-mail.
Déconcaténer avec l’outil Convertir
Pour une opération ponctuelle, l’outil intégré Convertir peut être suffisant. Il ne nécessite aucune formule.
- Sélectionnez la colonne contenant les données à décomposer ;
- ouvrez l’onglet Données ;
- cliquez sur Convertir ;
- choisissez l’option Délimité ;
- sélectionnez le séparateur utilisé : espace, virgule, point-virgule ou autre ;
- indiquez éventuellement la destination du résultat ;
- validez pour répartir les données dans plusieurs colonnes.
Cette solution est rapide, mais elle présente une limite importante : l’opération n’est pas dynamique. Si vous modifiez les données sources ou ajoutez de nouvelles lignes, Excel ne rejoue pas automatiquement la séparation. Pour un traitement récurrent, une formule ou Power Query sera plus adaptée.
Déconcaténer avec Power Query
Power Query est particulièrement efficace pour traiter une colonne importée depuis un fichier CSV, une base de données ou un autre classeur Excel. Son avantage principal : les étapes de transformation sont mémorisées et peuvent être rejouées lors de l’actualisation des données.
Imaginons une table contenant une colonne Nom complet avec des valeurs comme :
Durand Nathan
Martin Claire
Bernard Thomas
Pour séparer cette colonne :
- transformez la plage en tableau avec Ctrl + T ;
- ouvrez l’onglet Données ;
- sélectionnez À partir d’un tableau ou d’une plage ;
- dans l’éditeur Power Query, cliquez sur la colonne Nom complet ;
- ouvrez Transformer, puis Fractionner la colonne ;
- choisissez Par délimiteur ;
- sélectionnez l’espace comme séparateur ;
- choisissez une séparation à chaque occurrence ou à la première occurrence ;
- renommez les colonnes obtenues, puis cliquez sur Fermer et charger.
Power Query crée une étape de transformation. Si le fichier source reçoit de nouvelles lignes, il suffit d’actualiser la requête pour appliquer à nouveau la déconcaténation.
Gérer les espaces superflus dans Power Query
Les données copiées depuis un logiciel ou un export peuvent contenir des espaces inutiles. Une valeur comme Durand Nathan peut produire des résultats inattendus lors du fractionnement.
Avant de séparer la colonne, utilisez la commande Transformer > Format > Nettoyer ou Supprimer les espaces, selon la version d’Excel. Vous pouvez également appliquer une étape de remplacement pour transformer les doubles espaces en espaces simples.
Cette préparation évite de créer des colonnes vides et rend la requête plus fiable. Dans un processus d’import régulier, ce nettoyage automatique représente un gain de temps appréciable.
Quelle méthode choisir ?
- FRACTIONNER.TEXTE convient pour une formule moderne, rapide et dynamique ;
- TEXTEAVANT et TEXTEAPRES sont idéales pour extraire une partie précise d’un texte ;
- les fonctions GAUCHE, STXT et TROUVE restent utiles dans les anciennes versions d’Excel ;
- l’outil Convertir est adapté à une opération ponctuelle ;
- Power Query est préférable pour les imports, les fichiers volumineux et les traitements à répéter.
Une dernière précaution : vérifiez toujours que les cellules situées à droite ou sous la formule sont libres. Les formules dynamiques et Power Query ont besoin d’espace pour afficher les résultats. Si une cellule bloque le déversement, Excel affichera une erreur au lieu de répartir les données.
Avec la bonne méthode, déconcaténer une colonne Excel ne demande donc que quelques secondes. Et lorsque la source est régulièrement mise à jour, Power Query transforme cette petite opération manuelle en une véritable automatisation Excel.