Extraire prénom, deuxième prénom et Nom de famille à partir de Nom complet dans Excel
Supposons que vous disposiez d’une liste de noms complets et que vous souhaitiez les séparer en prénom, deuxième prénom et nom de famille dans des colonnes distinctes, comme illustré dans la capture d’écran ci-dessous. Cet article présente plusieurs formules Excel pour réaliser cette opération.
- Extraire ou obtenir Prénom à partir de Nom complet dans Excel
- Extraire ou obtenir Nom de famille à partir de Nom complet dans Excel
- Extraire ou obtenir Deuxième prénom à partir de Nom complet dans Excel
- Extraire ou obtenir le prénom, le deuxième prénom et Nom de famille à partir de Nom complet grâce à une fonctionnalité remarquable
Extraire ou obtenir Prénom à partir de Nom complet dans Excel
Pour extraire le prénom à partir d’un nom complet, les fonctions GAUCHE et CHERCHE sont là pour vous aider. Voici la syntaxe générique :
- nameLe nom complet ou la référence de cellule à partir de laquelle vous souhaitez extraire le prénom.
Veuillez copier ou saisir la formule suivante dans une cellule vide où vous souhaitez afficher le résultat :
Ensuite, faites glisser la poignée de recopie vers le bas jusqu’aux cellules auxquelles vous souhaitez appliquer cette formule. Tous les prénoms seront alors extraits, comme illustré ci-dessous :
Explication de la formule :
CHERCHE(« »,A2)-1 : Cette fonction CHERCHE identifie la position du premier espace. En soustrayant 1, on obtient un nombre de caractères correspondant à tout ce qui précède cet espace. Le résultat est : 5.
GAUCHE(A2,CHERCHE(« »,A2)-1)= LEFT(«Jerry T. Stone»,[[PH_89]]) : Cette fonction GAUCHE extrait les 5 premiers caractères du nom complet « Jerry T. Stone » à partir de la gauche, tels que renvoyés par la fonction CHERCHE.
Extraire ou obtenir Nom de famille à partir de Nom complet dans Excel
Pour extraire le nom de famille à partir du nom complet, une combinaison des fonctions DROITE, NBCAR, TROUVE et SUBSTITUE peut vous être utile. La syntaxe générique est la suivante :
- nameLe nom complet ou la référence de cellule à partir de laquelle vous souhaitez extraire le nom de famille.
Veuillez copier ou saisir la formule ci-dessous dans une cellule vide afin d’obtenir le Nom de famille :
Ensuite, faites glisser la poignée de recopie vers le bas pour remplir les cellules avec cette formule. Tous les noms de famille seront ainsi extraits du nom complet, comme illustré ci-dessous :
Explication de la formule :
1. NBCAR(A2)-NBCAR(SUBSTITUE(A2,« »,«»)) : Cette formule calcule le nombre d’espaces contenus dans la cellule A2.
- NBCAR(A2): Cette fonction NBCAR renvoie le nombre total de caractères contenus dans la cellule A2. Résultat : 14.
- SUBSTITUE(A2,« »,«») : Cette fonction SUBSTITUE remplace tous les espaces par une chaîne vide, ce qui vous donne le résultat suivant : «JerryT.Stone».
- NBCAR(SUBSTITUE(A2,« »,«»)) : Renvoie la longueur totale du nom complet dans la cellule A2, sans les espaces.
- NBCAR(A2)-NBCAR(SUBSTITUE(A2,« »,«»)) : Soustrait la longueur du nom complet sans espaces de la longueur totale du nom complet pour obtenir le nombre d’espaces. Le résultat est : 2.
2. SUBSTITUE(A2,« »,«*»,NBCAR(A2)-NBCAR(SUBSTITUE(A2,« »,«»)))= SUBSTITUTE(A2,« »,«*»,[[PH_94]]): Cette fonction SUBSTITUE remplace la dernière occurrence de l’espace (identifiée par la première partie de la formule) par un caractère « * ». Vous obtenez alors le résultat suivant : « Jerry T.*Stone ».
3. TROUVE(«*»,SUBSTITUE(A2,« »,«*»,NBCAR(A2)-NBCAR(SUBSTITUE(A2,« »,«»)))) = FIND(«*»,«Jerry T.*Stone») :La fonction TROUVE renvoie la position du caractère « * » dans la chaîne de texte générée par la fonction SUBSTITUE. Le résultat est : 9.
4. NBCAR(A2)-TROUVE(«*»,SUBSTITUE(A2,« »,«*»,NBCAR(A2)-NBCAR(SUBSTITUE(A2,« »,«»))))= NBCAR(A2)-9 : Cette partie de la formule calcule le nombre de caractères situés après le dernier espace. Le résultat est 5, ce qui constitue l’argument « num_caractères » de la fonction DROITE.
5. DROITE(A2,NBCAR(A2)-TROUVE(«*»,SUBSTITUE(A2,« »,«*»,NBCAR(A2)-NBCAR(SUBSTITUE(A2,« »,«»)))))=DROITE(A2, 5): Enfin, la fonction DROITE extrait les 5 derniers caractères de la chaîne située dans la cellule A2, tels que calculés par la formule de l’étape 4.
Remarque: Si votre liste de Nom complet ne contient que le prénom et le Nom de famille, vous pouvez appliquer la formule simple suivante pour obtenir le Nom de famille souhaité :
Extraire ou obtenir Deuxième prénom à partir de Nom complet dans Excel
Pour extraire le deuxième prénom à partir du nom complet, utilisez cette formule élaborée à l’aide des fonctions STXT et CHERCHE. Sa syntaxe générique est la suivante :
- nameLe nom complet ou la référence de cellule à partir de laquelle vous souhaitez extraire le deuxième prénom.
Veuillez saisir ou copier la formule suivante dans une cellule vide afin d’afficher le résultat :
Ensuite, faites glisser la poignée de recopie vers le bas jusqu’aux cellules pour appliquer cette formule, et tous les Deuxième prénom sont également extraits, voir la capture d’écran :
Explication de la formule :
Pour extraire le deuxième prénom, commencez par repérer la position des deux espaces dans le nom complet, puis utilisez la fonction STXT (MID) pour extraire le texte situé entre eux.
1. CHERCHE(« »; A2) + 1 : Cette fonction CHERCHE renvoie la position du premier espace ; en ajoutant 1, on indique que l’extraction commence au caractère suivant, ce qui donne le chiffre 7. Ce résultat constitue l’argument no_départ de la fonction STXT (MID).
2. CHERCHE(« »; A2; CHERCHE(« »; A2)+1) - CHERCHE(« »; A2)-1 : Cette partie correspond à l’argument nb_car de la fonction STXT (MID).
- CHERCHE(« », A2, CHERCHE(« », A2)+1) : Cette partie de la formule permet d’obtenir la position du deuxième espace. Le résultat est : 9.
- CHERCHE(« », A2) : Cette fonction CHERCHE renvoie la position du premier espace dans la cellule A2. Le résultat est : 6.
- CHERCHE(« », A2, CHERCHE(« », A2)+1) - CHERCHE(« », A2)-1=9-6-1 : Soustrait la position du premier espace de celle du deuxième espace, puis retire 1 au résultat pour éliminer l’espace initial. Le résultat final est : 2.
3. STXT(A2; CHERCHE(« »; A2) + 1; CHERCHE(« »; A2; CHERCHE(« »; A2)+1) - CHERCHE(« »; A2)-1)=STXT(A2;7,2) : Enfin, la fonction STXT (équivalent de MID) extrait deux caractères situés au milieu de la cellule A2, à partir du septième caractère.
Remarque: S’il n’y a que le prénom et le Nom de famille dans le Nom complet, la formule ci-dessus renverra une valeur d’erreur, comme illustré dans la capture d’écran ci-dessous :
Pour résoudre ce problème, vous devez intégrer la formule ci-dessus dans la fonction SIERREUR (IFERROR). Veuillez appliquer la formule suivante :
Extraire ou obtenir le prénom, le nom du milieu et le Nom de famille à partir de Nom complet grâce à une fonctionnalité remarquable
Si les formules ci-dessus vous semblent complexes, je vous recommande un outil simple :Kutools pour Excel, doté de sa fonctionnalité Diviser le nom, qui vous permet d’extraire simultanément tous les prénoms, deuxièmes prénoms et Nom de famille dans des cellules distinctes.Cliquez ici pour télécharger Kutools pour Excel et profiter d’un essai gratuit !
Fonctions associées utilisées :
- LEFT:
- La fonction GAUCHE extrait, depuis le début (à gauche) d’une chaîne de texte donnée, le nombre indiqué de caractères.
- RIGHT:
- La fonction DROITE extrait un nombre donné de caractères depuis la fin (à droite) d’une chaîne de texte.
- MID:
- La fonction STXT (ou MID en anglais) permet d’extraire et de renvoyer un nombre précis de caractères à partir du milieu d’une chaîne de texte donnée.
- LEN:
- La fonction NBCAR renvoie le nombre total de caractères d’une chaîne de texte.
- FIND:
- La fonction TROUVE localise une chaîne de caractères à l’intérieur d’une autre et renvoie la position de départ de cette sous-chaîne dans la chaîne principale.
- SEARCH:
- La fonction CHERCHE vous permet de repérer facilement la position d’un caractère ou d’une sous-chaîne précise au sein d’un texte donné.
- SUBSTITUTE:
- La fonction SUBSTITUE d’Excel remplace un texte ou des caractères spécifiques dans une chaîne par un autre texte ou d’autres caractères.
Plus d’articles :
- Extraire la première initiale et Nom de famille à partir de Nom complet
- Supposons que vous disposiez d’une liste de noms complets et que vous souhaitiez en extraire la première initiale du prénom ainsi que le nom de famille, ou bien le prénom accompagné de la première lettre du nom de famille, comme illustré dans les captures d’écran ci-dessous. Comment réaliser ces opérations dans Excel ?
- Inverser ou permuter prénom et Nom de famille dans une liste Excel
- Si vous avez une liste de Nom complet formatés sous la forme « Nom de famille et Prénom », et que vous souhaitez inverser l’ordre afin d’obtenir « prénom et nom » comme sur la capture d’écran ci-dessous, cet article présente plusieurs formules pour effectuer cette opération dans Excel.
- Extraire plusieurs lignes à partir d’une cellule
- Si vous disposez d’une liste de chaînes de texte séparées par des retours à la ligne (insérés en appuyant sur Alt + Entrée lors de la saisie) et que vous souhaitez extraire chaque ligne dans des cellules distinctes, comme illustré ci-dessous, comment procéder à l’aide d’une formule Excel ?
- Extraire le nième mot d’une chaîne de texte dans Excel
- Si vous disposez d’une liste de chaînes de texte ou de phrases et que vous souhaitez extraire un mot spécifique (le *n*ième), comme illustré dans la capture d’écran ci-dessous, cet article vous présente plusieurs méthodes pour réaliser cette opération dans Excel.
Les meilleurs outils de productivité Office
Kutools pour Excel – Vous permet de vous démarquer de la foule
Kutools pour Excel propose plus de 300 fonctionnalités,garantissant que ce dont vous avez besoin est toujours à portée de clic...
Office Tab – Active la lecture et l’édition par onglets dans Microsoft Office (y compris Excel)
- Passez d’un document à l’autre en une seconde, même avec des dizaines de documents ouverts !
- Réduisez de centaines vos clics de souris chaque jour et dites adieu au syndrome de la main de la souris.
- Augmentez votre productivité de 50 % en consultant et en modifiant simultanément plusieurs documents.
- Apporte à Office (y compris Excel) une efficacité comparable à celle des navigateurs tels que Chrome, Edge et Firefox.