Extraire les prénoms, deuxièmes prénoms et noms de famille à partir du nom complet dans Excel

Supposons que vous ayez une liste de noms d’utilisateurs et que vous souhaitiez diviser les noms complets en prénoms, deuxièmes prénoms et noms de famille dans des colonnes séparées, comme le montre la capture d’écran suivante. Dans cet article, je vais vous présenter quelques formules pour accomplir cette tâche dans Excel.

• Extraire ou obtenir les prénoms à partir des noms complets dans Excel
• Extraire ou obtenir les noms de famille à partir des noms complets dans Excel
• Extraire ou obtenir les deuxièmes prénoms à partir des noms complets dans Excel
• Extraire ou obtenir les prénoms, deuxièmes prénoms et noms de famille à partir des noms complets avec une fonctionnalité étonnante

Extraire ou obtenir les prénoms à partir des noms complets dans Excel

Pour extraire ou obtenir les prénoms à partir des noms complets, les fonctions GAUCHE et CHERCHE peuvent vous aider. La syntaxe générique est :

• nom : Le nom complet ou la référence de cellule à partir de laquelle vous souhaitez extraire le prénom.

Veuillez copier ou entrer la formule suivante dans une cellule vide où vous souhaitez obtenir le résultat :

Ensuite, faites glisser la poignée de recopie vers le bas jusqu'aux cellules auxquelles vous souhaitez appliquer cette formule, et tous les prénoms auront été extraits comme le montre la capture d'écran ci-dessous :

Explication de la formule :

CHERCHE(" ",A2)-1 : Cette fonction CHERCHE permet d’obtenir la position du premier caractère espace. Soustraire 1 signifie que le nombre de caractères souhaité est inférieur de un à la position de l’espace. Cela donnera le résultat : 5.

GAUCHE(A2,CHERCHE(" ",A2)-1)= GAUCHE("Jerry T. Stone",5) : Cette fonction GAUCHE extraira le texte du nom complet « Jerry T. Stone » depuis la gauche avec 5 caractères qui sont retournés par la fonction CHERCHE.

Extraire ou obtenir les noms de famille à partir des noms complets dans Excel

Pour extraire les noms de famille à partir des noms complets, la combinaison des fonctions DROITE, NBCAR, TROUVE, NBCAR et SUBSTITUE peut vous aider. La syntaxe générique est :

• nom : Le nom complet ou la référence de cellule à partir de laquelle vous souhaitez extraire le nom de famille.

Veuillez copier ou entrer la formule ci-dessous dans une cellule vide où vous souhaitez obtenir les noms de famille :

Ensuite, faites glisser la poignée de recopie vers le bas jusqu'aux cellules auxquelles vous souhaitez appliquer cette formule, et tous les noms de famille auront été extraits des noms complets, voir la capture d'écran :

Explication de la formule :

1. NBCAR(A2)-NBCAR(SUBSTITUE(A2," ","")) : Cette partie sert à obtenir le nombre de caractères espaces dans la cellule A2.

• NBCAR(A2): Cette fonction NBCAR retourne le nombre total de caractères dans la cellule A2. Elle retournera : 14.
• SUBSTITUE(A2," ","") : Cette fonction SUBSTITUE remplace tous les caractères espaces par rien. Vous obtiendrez ce résultat : “JerryT.Stone”.
• NBCAR(SUBSTITUE(A2," ","")): Obtenez la longueur totale du nom complet dans la cellule A2 sans les espaces.
• NBCAR(A2)-NBCAR(SUBSTITUE(A2,"-","")): Soustrayez la longueur du nom complet sans espace de la longueur totale du nom complet pour obtenir le nombre d'espaces, et cela donnera 2.

2. SUBSTITUE(A2," ","*",NBCAR(A2)-NBCAR(SUBSTITUE(A2," ","")))= SUBSTITUE(A2," ","*",2): Cette fonction SUBSTITUE remplace la dernière occurrence de l'espace, qui est retournée par la première partie de la formule, par un caractère *. Et vous obtiendrez ce résultat : “Jerry T.*Stone”.

3. TROUVE("*",SUBSTITUE(A2," ","*",NBCAR(A2)-NBCAR(SUBSTITUE(A2," ",""))))= TROUVE("*","Jerry T.*Stone" ): La fonction TROUVE retournera la position du caractère * dans la chaîne de texte qui est retournée par la fonction SUBSTITUE, et vous obtiendrez le chiffre 9.

4. NBCAR(A2)-TROUVE("*",SUBSTITUE(A2," ","*",NBCAR(A2)-NBCAR(SUBSTITUE(A2," ",""))))= NBCAR(A2)-9: Cette partie de la formule déterminera combien de caractères il y a après le dernier espace. Cela donnera le chiffre 5. Ce sera reconnu comme l'argument num_chars dans la fonction DROITE.

5. DROITE(A2,NBCAR(A2)-TROUVE("*",SUBSTITUE(A2," ","*",NBCAR(A2)-NBCAR(SUBSTITUE(A2," ","")))))=DROITE(A2, 5): Enfin, la fonction DROITE extraira 5 caractères qui sont retournés par la formule à l'étape 4 depuis la droite de la chaîne de texte dans la cellule A2.

Remarque : Si votre liste de noms complets ne contient que les prénoms et noms de famille, vous pouvez appliquer la formule simple suivante pour obtenir le nom de famille selon vos besoins :

Extraire ou obtenir les deuxièmes prénoms à partir des noms complets dans Excel

Si vous devez extraire les deuxièmes prénoms à partir des noms complets, cette formule créée par les fonctions STXT et CHERCHE peut vous aider. La syntaxe générique est :

• nom : Le nom complet ou la référence de cellule à partir de laquelle vous souhaitez extraire le deuxième prénom.

Veuillez entrer ou copier la formule suivante dans une cellule vide pour afficher le résultat :

Ensuite, faites glisser la poignée de recopie vers le bas jusqu'aux cellules auxquelles vous souhaitez appliquer cette formule, et tous les deuxièmes prénoms seront également extraits, voir la capture d'écran :

Explication de la formule :

Pour extraire le deuxième prénom, vous devez d'abord trouver la position des deux espaces entre le nom complet, puis appliquer la fonction STXT pour extraire le texte au milieu de la chaîne de texte.

1. CHERCHE(" ", A2) + 1 : Cette fonction CHERCHE obtiendra la position du premier espace, ajouter 1 signifie commencer l'extraction à partir du caractère suivant. Cela donnera le chiffre 7. Cette partie est reconnue comme l'argument départ_num dans la fonction STXT.

2. CHERCHE(" ", A2, CHERCHE(" ", A2)+1) - CHERCHE(" ", A2)-1 : Cette partie est reconnue comme l'argument num_car dans la fonction STXT.

• CHERCHE(" ", A2, CHERCHE(" ", A2)+1) : Cette partie de la formule sert à obtenir la position du deuxième espace, elle donnera le chiffre 9.
• CHERCHE(" ", A2) : Cette fonction CHERCHE obtiendra la position du premier espace dans la cellule A2. Elle donnera le chiffre 6.
• CHERCHE(" ", A2, CHERCHE(" ", A2)+1) - CHERCHE(" ", A2)-1=9-6-1 : Soustrayez la position du premier espace de la position du deuxième espace, puis soustrayez 1 du résultat pour supprimer un espace initial. Le résultat sera 2.

3. STXT(A2, CHERCHE(" ", A2) + 1, CHERCHE(" ", A2, CHERCHE(" ", A2)+1) - CHERCHE(" ", A2)-1)=STXT(A2,7,2) : Enfin, la fonction STXT extraira 2 caractères au milieu de la cellule A2 qui commence au septième caractère.

Remarque : S'il n'y a que des prénoms et noms de famille dans les noms complets, la formule ci-dessus donnera une valeur d'erreur comme le montre la capture d'écran ci-dessous :

Pour résoudre ce problème, vous devriez inclure la formule ci-dessus dans la fonction SIERREUR, veuillez appliquer la formule suivante :

Extraire ou obtenir les prénoms, deuxièmes prénoms et noms de famille à partir des noms complets avec une fonctionnalité étonnante

Fonctions relatives utilisées :

Plus d'articles :