=TEXTE.AVANT(A2;" ") renvoie tout ce qui précède la première espace, donc Ana Silva donne Ana, et =TEXTE.APRES(A2;" ") renvoie tout ce qui la suit, Silva. Recopiez les deux formules vers le bas et chaque nom complet est séparé en deux colonnes. Dans un Excel anglais, ces fonctions s'appellent TEXTBEFORE et TEXTAFTER ; le tableau affiche les formules sous cette forme anglaise, avec des virgules, mais vous pouvez aussi les taper comme dans un Excel français, avec des points-virgules : =TEXTE.AVANT(A2;" ").
| A | B | C | |
|---|---|---|---|
| 1 | Full name | First | Last |
| 2 | Ana Silva | Ana | Silva |
| 3 | Ben Okafor | Ben | Okafor |
| 4 | Chen Wu | Chen | Wu |
| 5 | Dara Kelly | Dara | Kelly |
| 6 | Eli Novak | Eli | Novak |
=TEXTE.AVANT(A2;" ")Changez un nom dans la colonne A et les deux colonnes suivent. TEXTE.AVANT, TEXTE.APRES et FRACTIONNER.TEXTE exigent Microsoft 365 ou Excel 2024. Pour Excel 2021 et les versions plus anciennes, la version avec GAUCHE et TROUVE se trouve plus bas.
FRACTIONNER.TEXTE : découper une cellule en colonnes
=FRACTIONNER.TEXTE(A2;" ") (TEXTSPLIT en anglais) coupe le texte à chaque espace et répartit chaque morceau dans sa propre cellule. Une seule formule remplit autant de colonnes qu'il y a de morceaux :
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Text | ||||
| 2 | Ana Maria Silva | Ana | Maria | Silva | |
| 3 | Paris, Lyon, Nice | Paris | Lyon | Nice |
=FRACTIONNER.TEXTE(A2;" ")- Le délimiteur peut faire plus d'un caractère :
", "coupe à une virgule suivie d'une espace, donc les morceaux ne commencent pas par une espace. - Laissez de la place pour le résultat. Si une cellule à droite n'est pas vide, la formule affiche #EPARS! (#SPILL! en anglais) à la place.
- Les morceaux sont toujours du texte, chiffres compris, donc SOMME les ignore. Mettez deux signes moins devant,
=--FRACTIONNER.TEXTE(A2;","), quand il vous faut des nombres.
Le troisième argument est un délimiteur de ligne. Avec les deux, FRACTIONNER.TEXTE construit un petit tableau à partir d'une seule cellule : les virgules séparent les colonnes et les points-virgules les lignes.
| A | B | |
|---|---|---|
| 1 | Teams | |
| 2 | Ana,Red;Ben,Blue;Chen,Green | |
| 3 | ||
| 4 | Ana | Red |
| 5 | Ben | Blue |
| 6 | Chen | Green |
=FRACTIONNER.TEXTE(A2;",";";")Deux autres formes qui fonctionnent dans Excel, montrées ici sous forme de texte :
=TEXTSPLIT(A2,,", ") one piece per row: the column delimiter is left out
=TEXTSPLIT(A2,{";",","}) several delimiters in braces: splits at ; or ,
La première transforme excel, formulas, text en une colonne de trois cellules, ce qui permet de passer en lignes une liste tapée dans une seule cellule. La seconde, montrée sous sa forme anglaise, transforme red;green,blue en red, green, blue. Dans un Excel français, la première s'écrit =FRACTIONNER.TEXTE(A2;;", ").
Deuxièmes prénoms et "Nom, Prénom"
TEXTE.APRES(A2;" ") renvoie tout ce qui suit la première espace, donc pour Ana Maria Silva elle renvoie Maria Silva. Pour obtenir le dernier mot, comptez depuis la fin avec un numéro d'occurrence de -1. Pour des noms écrits Silva, Ana, coupez plutôt à la virgule et à l'espace :
| A | B | C | |
|---|---|---|---|
| 1 | Full name | First | Last |
| 2 | Ana Maria Silva | Ana | Silva |
| 3 | Ben Okafor | Ben | Okafor |
| 4 | Chen Li Wu | Chen | Wu |
| 5 | Cher | Cher | |
| 6 | |||
| 7 | Last, First | First | Last |
| 8 | Silva, Ana | Ana | Silva |
=TEXTE.APRES(A2;" ";-1)Quand le délimiteur est absent, TEXTE.AVANT et TEXTE.APRES renvoient #N/A, et un nom d'un seul mot comme Cher n'a pas d'espace. La ligne 5 utilise le sixième argument, la valeur à renvoyer à la place : =TEXTE.AVANT(A5;" ";;;;A5) renvoie le nom entier et TEXTE.APRES(...;"") renvoie une cellule vide.
| A | B | C | |
|---|---|---|---|
| 1 | User | Domain | |
| 2 | ana.silva@coddy.tech | ana.silva |
À vous : En C2, renvoyez le domaine de l'adresse e-mail de A2 : tout ce qui suit le @.
Séparer des noms dans les anciennes versions d'Excel
Sans TEXTE.AVANT ni TEXTE.APRES, trouvez la position de l'espace avec TROUVE et coupez autour avec GAUCHE, STXT et DROITE :
| A | B | C | |
|---|---|---|---|
| 1 | Full name | First | Last |
| 2 | Ana Silva | Ana | Silva |
| 3 | Ben Okafor | Ben | Okafor |
| 4 | Chen Wu | Chen | Wu |
=GAUCHE(A2;TROUVE(" ";A2)-1)TROUVE(" ";A2)est la position de l'espace : 4 dansAna Silva.GAUCHE(A2;4-1)prend les 3 caractères qui la précèdent.STXT(A2;4+1;100)prend jusqu'à 100 caractères à partir de la position qui suit l'espace. 100 veut juste dire "plus que n'importe quel nom" ;=DROITE(A2;NBCAR(A2)-TROUVE(" ";A2))donne le même résultat avec une longueur exacte.
Ces formules renvoient #VALEUR! (#VALUE! en anglais) quand il n'y a pas d'espace. Entourez-les de SIERREUR, =SIERREUR(GAUCHE(A2;TROUVE(" ";A2)-1);A2), pour renvoyer toute la cellule à la place.
| A | B | |
|---|---|---|
| 1 | Code | Prefix |
| 2 | KT-1043-BLUE |
À vous : Renvoyez en B2 la partie du code produit qui précède le premier tiret, avec GAUCHE et TROUVE.
Convertir et Remplissage instantané
Pour séparer une colonne une seule fois, sans formules :
- Sélectionnez la colonne des noms complets. Vérifiez que les colonnes à sa droite sont vides, car Convertir les écrase.
- Données > Convertir.
- Choisissez Délimité, cliquez sur Suivant, cochez Espace (ou Virgule, ou tapez un autre caractère dans Autre), cliquez sur Suivant.
- Le premier morceau remplace les noms complets. Pour les garder, réglez Destination sur la cellule vide à leur droite, puis cliquez sur Terminer.
Le Remplissage instantané est plus rapide pour un modèle : tapez Ana à côté de Ana Silva, appuyez sur Entrée, puis sur Ctrl+E ou Données > Remplissage instantané, et Excel remplit les prénoms des autres lignes. Les deux outils écrivent des valeurs fixes. Si les noms changent plus tard, le découpage ne suit pas ; une formule, si.
Questions fréquentes
Comment séparer le nom et le prénom dans Excel ?
Avec un nom comme Ana Silva en A2, =TEXTE.AVANT(A2;" ") donne Ana et =TEXTE.APRES(A2;" ";-1) donne Silva, même avec un deuxième prénom. Dans Excel 2021 et les versions plus anciennes, utilisez =GAUCHE(A2;TROUVE(" ";A2)-1) pour le prénom.
Quelles versions d'Excel ont FRACTIONNER.TEXTE ?
FRACTIONNER.TEXTE, TEXTE.AVANT et TEXTE.APRES existent dans Microsoft 365, Excel pour le web et Excel 2024. Excel 2021 et les versions plus anciennes affichent #NOM? ; utilisez-y GAUCHE, STXT, DROITE et TROUVE, ou Données > Convertir.
Comment séparer une cellule à chaque virgule dans Excel ?
=FRACTIONNER.TEXTE(A2;", ") répartit chaque morceau dans sa propre colonne. Pour placer les morceaux en lignes, laissez le délimiteur de colonne vide : =FRACTIONNER.TEXTE(A2;;", ").
Pourquoi TEXTE.AVANT renvoie-t-elle #N/A ?
Le délimiteur n'est pas dans la cellule, par exemple un nom seul sans espace. Donnez une valeur pour ce cas dans le sixième argument : =TEXTE.AVANT(A2;" ";;;;A2) renvoie toute la cellule quand il n'y a pas d'espace.
