Séparez le prénom et le nom dans Excel (diviser les noms à l'aide de formules)

Regarder la vidéo - Diviser les noms dans Excel (en prénom, deuxième prénom et nom de famille)

Excel est un outil incroyable lorsqu'il s'agit de découper et de découper des données de texte.

Il existe de nombreuses formules utiles ainsi que des fonctionnalités que vous pouvez utiliser pour travailler avec des données textuelles dans Excel.

L'une des questions les plus courantes que je reçois à propos de la manipulation de données textuelles est : "Comment séparer le prénom et le nom (ou le prénom, le deuxième prénom et le nom de famille) dans Excel ?“.

Il existe plusieurs façons simples de diviser les noms dans Excel. La méthode que vous choisirez dépendra de la façon dont vos données sont structurées et si vous souhaitez que le résultat de cowothe soit statique ou dynamique.

Commençons donc et voyons différentes façons de diviser les noms dans Excel.

Diviser les noms à l'aide de texte en colonnes

La fonctionnalité Texte en colonnes dans Excel vous permet de diviser rapidement les valeurs de texte en cellules distinctes d'affilée.

Par exemple, supposons que vous ayez l'ensemble de données comme indiqué ci-dessous et que vous souhaitiez séparer le prénom et le nom et les obtenir dans des cellules séparées.

Vous trouverez ci-dessous les étapes pour séparer le prénom et le nom à l'aide de Text to Columns :

1. Sélectionnez tous les noms dans la colonne (A2:A10 dans cet exemple)
2. Cliquez sur l'onglet « Données »
3. Dans le groupe « Outils de données », cliquez sur l'option « Texte dans les colonnes ».
4. Apportez les modifications suivantes dans l'assistant de conversion de texte en colonne :
   1. Étape 1 sur 3 : Sélectionnez Délimité (cela vous permet d'utiliser l'espace comme séparateur) et cliquez sur Suivant
   2. Étape 2 sur 3: Sélectionnez l'option Espace et cliquez sur Suivant
   3. Étape 3 sur 3 : définissez B2 comme cellule de destination (sinon, il écrasera les données existantes)
5. Cliquez sur Terminer

Les étapes ci-dessus diviseraient instantanément les noms en prénom et nom (avec les prénoms dans la colonne B et le nom dans la colonne C).

Remarque : au cas où il y aurait déjà des données dans les cellules (celles où la sortie Text to Columns est attendue), Excel vous affichera un avertissement vous informant qu'il y a déjà des données dans les cellules. Vous pouvez choisir d'écraser les données ou d'annuler Text to Columns et de les supprimer manuellement d'abord.

Une fois cela fait, vous pouvez supprimer les données du nom complet si vous le souhaitez.

Quelques points à savoir lors de l'utilisation de Text to Columns pour séparer les prénoms et les noms dans Excel :

1. Le résultat est statique. Cela signifie que si vous avez de nouvelles données ou que les données d'origine ont des modifications, vous devez tout recommencer pour diviser les noms.
2. Si vous voulez uniquement le prénom ou uniquement le nom, vous pouvez ignorer les colonnes que vous ne voulez pas à l'étape 3 de la boîte de dialogue « Assistant de texte en colonne ». Pour ce faire, sélectionnez la colonne dans l'aperçu que vous souhaitez ignorer, puis sélectionnez l'option « Ne pas importer la colonne (ignorer) ».
3. Si vous ne spécifiez pas la cellule de destination, Text to Column écrasera la colonne actuelle

L'option Text to Columns est la mieux adaptée lorsque vous disposez de données cohérentes (par exemple, tous les noms ont un prénom et un nom uniquement ou tous les noms ont un prénom, un deuxième prénom et un nom de famille).

Dans cet exemple, je vous ai montré comment séparer les noms qui ont un espace comme délimiteur. Si le délimiteur est une virgule ou une combinaison de virgule et d'espace, vous pouvez toujours utiliser les mêmes étapes. Dans ce cas, vous pouvez spécifier le délimiteur à l'étape 2 sur 3 de l'assistant. Il existe déjà une option pour utiliser la virgule comme délimiteur, ou vous pouvez sélectionner l'option "Autre" et spécifier également un délimiteur personnalisé.

Bien que Text to Columns soit un moyen rapide et efficace de diviser les noms, il ne convient que lorsque vous souhaitez que la sortie soit un résultat statique. Si vous disposez d'un ensemble de données susceptible de s'étendre ou de changer, il est préférable d'utiliser des formules pour séparer les noms.

Séparez le prénom, le deuxième prénom et le nom de famille à l'aide de formules

Les formules vous permettent de découper et de découper les données textuelles et d'extraire ce que vous voulez.

Dans cette section, je vais partager diverses formules que vous pouvez utiliser pour séparer les données de nom (en fonction de la structure de vos données).

Vous trouverez ci-dessous les trois formules que vous pouvez utiliser pour séparer le prénom, le deuxième prénom et le nom de famille (expliquées en détail plus loin dans les sections suivantes).

Formule pour obtenir le prénom :

=GAUCHE(A2,RECHERCHE(" ",A2)-1)

Formule pour obtenir le deuxième prénom :

=MID(A2,RECHERCHE(" ",A2)+1,RECHERCHE(" ",REMPLACER(A2," ","@",1))-RECHERCHE(" ",A2))

Formule pour obtenir le nom de famille :

=DROITE(A15,LEN(A15)-RECHERCHE("@",REMPLACER(A15," ","@",LEN(A15)-LEN(REMPLACER(A15," ","")))))

Obtenez le prénom

Supposons que vous ayez l'ensemble de données comme indiqué ci-dessous et que vous souhaitiez séparer rapidement le prénom dans une cellule et le nom de famille dans une cellule.

La formule ci-dessous vous donnera le prénom :

=GAUCHE(A2,RECHERCHE(" ",A2)-1)

La formule ci-dessus utilise la fonction SEARCH pour obtenir la position du caractère espace entre le prénom et le nom. La fonction GAUCHE utilise ensuite ce numéro de position d'espace pour extraire tout le texte qui le précède.

Il s'agit d'une utilisation assez simple pour extraire une partie de la valeur du texte. Étant donné que tout ce que nous devons faire est d'identifier la première position du caractère espace, peu importe que le nom ait un deuxième prénom ou non. La formule ci-dessus fonctionnera très bien.

Maintenant, allons un peu plus loin avec chaque exemple.

Obtenez le nom de famille

Disons que vous avez le même ensemble de données et que cette fois vous devez obtenir le nom de famille.

La formule ci-dessous extraira le nom de famille de l'ensemble de données ci-dessus :

=DROITE(A2,LEN(A2)-RECHERCHE(" ",A2))

Encore une fois, assez simple.

Cette fois, nous trouvons d'abord la position du caractère espace, qui est ensuite utilisé pour connaître le nombre de caractères qui restent après l'espace (qui serait le nom de famille).

Ceci est réalisé en soustrayant la valeur de position du caractère espace avec le nombre total de caractères dans le nom.

Ce numéro est ensuite utilisé dans la fonction RIGHT pour récupérer tous ces caractères à droite du nom.

Bien que cette formule fonctionne très bien lorsqu'il n'y a que le prénom et le nom, cela ne fonctionnerait pas si vous avez également un deuxième prénom. En effet, nous n'avons pris en compte qu'un seul caractère espace (entre le prénom et le nom). Avoir un deuxième prénom ajoute plus de caractères d'espace au nom.

Pour récupérer le nom de famille lorsque vous avez également un deuxième prénom, utilisez la formule ci-dessous :

=DROITE(A15,LEN(A15)-RECHERCHE("@",REMPLACER(A15," ","@",LEN(A15)-LEN(REMPLACER(A15," ","")))))

Maintenant, cela a commencé à devenir un peu complexe… n'est-ce pas ?

Laissez-moi vous expliquer comment cela fonctionne.

La formule ci-dessus trouve d'abord le nombre total de caractères d'espace dans le nom. Cela se fait en obtenant la longueur du nom avec et sans le caractère espace, puis en soustrayant celui sans espace de celui avec espace. Cela donne le nombre total de caractères d'espacement.

La fonction SUBSTITUTE est ensuite utilisée pour remplacer le dernier caractère espace par un symbole « @ » (vous pouvez utiliser n'importe quel symbole - quelque chose qui est peu susceptible de se produire dans le cadre du nom).

Une fois que le symbole @ a été remplacé à la place du dernier caractère espace, vous pouvez facilement trouver la position de ce symbole @. Cela se fait à l'aide de la fonction RECHERCHE.

Il ne vous reste plus qu'à extraire tous les caractères à droite de ce symbole @. Cela se fait en utilisant la fonction DROITE.

Obtenez le deuxième prénom

Supposons que vous ayez l'ensemble de données comme indiqué ci-dessous et que vous souhaitiez extraire le deuxième prénom.

La formule suivante fera cela :

=MID(A2,RECHERCHE(" ",A2)+1,RECHERCHE(" ",REMPLACER(A2," ","@",1))-RECHERCHE(" ",A2))

La formule ci-dessus utilise la fonction MID, qui vous permet de spécifier une position de départ et le nombre de caractères à extraire de cette position.

La position de départ est facile à trouver à l'aide de la fonction RECHERCHE.

La partie difficile est de trouver combien de caractères extraire après cette position de départ. Pour obtenir cela, vous devez identifier le nombre de caractères entre la position de départ et le dernier caractère espace.

Cela peut être fait en utilisant la fonction SUBSTITUTE et en remplaçant le dernier caractère espace par un symbole '@'. Une fois cela fait, vous pouvez facilement utiliser la fonction RECHERCHE pour trouver la position de ce dernier caractère espace.

Maintenant que vous avez la position de départ et la position du dernier espace, vous pouvez facilement récupérer le deuxième prénom en utilisant la fonction MID.

L'un des avantages d'utiliser une formule pour séparer les noms est que le résultat est dynamique. Ainsi, au cas où votre ensemble de données se développe et que d'autres noms y soient ajoutés ou si certains noms changent dans l'ensemble de données d'origine, vous n'avez pas à vous soucier des données résultantes.

Séparer les noms à l'aide de Rechercher et remplacer

J'aime la flexibilité offerte par « Rechercher et remplacer » - car vous pouvez utiliser des caractères génériques.

Permettez-moi d'abord d'expliquer ce qu'est un caractère générique.

Un caractère générique est quelque chose que vous pouvez utiliser à la place de n'importe quel texte. Par exemple, vous pouvez utiliser un astérisque (*) et il représentera n'importe quel nombre de caractères dans Excel. Pour vous donner un exemple, si je veux trouver tous les noms qui commencent par l'alphabet A, je peux utiliser A* dans rechercher et remplacer. Cela trouvera et sélectionnera toutes les cellules où le nom commence par A.

Si vous n'êtes toujours pas clair, ne vous inquiétez pas. Continuez à lire et les quelques exemples suivants indiqueront clairement quels sont les caractères génériques et comment les utiliser pour séparer rapidement les noms (ou toute valeur de texte dans Excel).

Dans tous les exemples couverts ci-dessous, assurez-vous de créer une copie de sauvegarde de l'ensemble de données. Rechercher et remplacer modifie les données sur lesquelles il est utilisé. Il est préférable de copier et coller d'abord les données, puis d'utiliser Rechercher et remplacer sur l'ensemble de données copié.

Obtenez le prénom

Supposons que vous ayez un ensemble de données comme indiqué ci-dessous et que vous souhaitiez obtenir uniquement le prénom.

Vous trouverez ci-dessous les étapes à suivre :

1. Copiez les données du nom dans la colonne A et collez-les dans la colonne B.
2. Une fois les données de la colonne B sélectionnées, cliquez sur l'onglet Accueil
3. Dans le groupe Édition, cliquez sur Rechercher et sélectionner.
4. Cliquez sur Remplacer. Cela ouvrira la boîte de dialogue « Rechercher et remplacer ».
5. Dans la boîte de dialogue « Rechercher et remplacer », entrez ce qui suit
   1. Trouver quoi : * (caractère espace suivi du symbole astérisque)
   2. Remplacer par : laissez ce champ vide
6. Cliquez sur Remplacer tout.

Les étapes ci-dessus vous donneraient le prénom et supprimeraient tout après le prénom.

Cela fonctionne même si vous avez des noms qui ont un deuxième prénom.

Conseil de pro: Le raccourci clavier pour ouvrir la boîte de dialogue Rechercher et remplacer est Contrôle + H (maintenez la touche Ctrl enfoncée puis appuyez sur la touche H).

Obtenez le nom de famille

Supposons que vous ayez un ensemble de données comme indiqué ci-dessous et que vous souhaitiez obtenir uniquement le nom de famille.

Vous trouverez ci-dessous les étapes à suivre :

1. Copiez les données du nom dans la colonne A et collez-les dans la colonne B.
2. Une fois les données de la colonne B sélectionnées, cliquez sur l'onglet Accueil
3. Dans le groupe Édition, cliquez sur Rechercher et sélectionner.
4. Cliquez sur Remplacer. Cela ouvrira la boîte de dialogue « Rechercher et remplacer ».
5. Dans la boîte de dialogue « Rechercher et remplacer », entrez ce qui suit
   1. Trouver quoi : * (symbole astérisque suivi d'un espace)
   2. Remplacer par : laissez ce champ vide
6. Cliquez sur Remplacer tout.

Les étapes ci-dessus vous donneraient le nom de famille et supprimeraient tout avant le prénom.

Cela fonctionne même si vous avez des noms qui ont un deuxième prénom.

Supprimer le deuxième prénom

Si vous souhaitez uniquement vous débarrasser du deuxième prénom et n'avoir que le prénom et le nom, vous pouvez le faire en utilisant Rechercher et remplacer.

Supposons que vous ayez un ensemble de données comme indiqué ci-dessous et que vous souhaitiez supprimer le deuxième prénom de ceux-ci.

Vous trouverez ci-dessous les étapes à suivre :

1. Copiez les données du nom dans la colonne A et collez-les dans la colonne B.
2. Une fois les données de la colonne B sélectionnées, cliquez sur l'onglet Accueil
3. Dans le groupe Édition, cliquez sur Rechercher et sélectionner.
4. Cliquez sur Remplacer. Cela ouvrira la boîte de dialogue « Rechercher et remplacer ».
5. Dans la boîte de dialogue « Rechercher et remplacer », entrez ce qui suit
   1. Trouver quoi : * (caractère espace suivi du symbole astérisque suivi du caractère espace)
   2. Remplacez par : (il suffit de mettre un espace ici)
6. Cliquez sur Remplacer tout.

Les étapes ci-dessus supprimeraient le deuxième prénom d'un nom complet. Au cas où certains noms n'auraient pas de deuxième prénom, ils ne seraient pas modifiés.

Séparer les noms à l'aide de Flash Fill

Le remplissage Flash a été introduit dans Excel 2013 et permet de modifier ou de nettoyer très facilement un ensemble de données de texte.

Et lorsqu'il s'agit de séparer les données des noms, c'est dans l'allée de Flash Fill.

La chose la plus importante à savoir lors de l'utilisation de Flash Fill est qu'il faut un motif que le Flash Fill puisse identifier. Une fois qu'il a identifié le modèle, il vous aidera facilement à diviser les noms dans Excel (vous obtiendrez plus de clarté à ce sujet lorsque vous parcourrez quelques exemples ci-dessous).

Obtenez le prénom ou le nom à partir du nom complet

Supposons que vous ayez un ensemble de données comme indiqué ci-dessous et que vous souhaitiez obtenir uniquement le prénom.

1. Dans la cellule adjacente, saisissez manuellement le prénom à partir du nom complet. Dans cet exemple, je taperais Rick.
2. Dans la deuxième cellule, tapez manuellement le premier nom du nom de la cellule adjacente. Pendant que vous tapez, vous verrez Flash Fill vous montrer automatiquement une liste du prénom (en gris).
3. Lorsque vous voyez les noms en gris, parcourez-les rapidement pour vous assurer qu'il affiche les bons noms. Si elles sont correctes, appuyez sur la touche Entrée et Flash Fill remplira automatiquement le reste des cellules avec le prénom.

Flash Fill a besoin que vous lui donniez un modèle qu'il puisse suivre en vous donnant les données modifiées. Dans notre exemple, lorsque vous tapez le prénom dans la première cellule, Flash Fill ne peut pas comprendre le motif.

Mais dès que vous commencez à entrer le prénom dans la deuxième cellule, Flash Fill comprend le modèle et vous montre une suggestion. Si la suggestion est correcte, appuyez simplement sur la touche Entrée.

Et si ce n'est pas correct, vous pouvez essayer de saisir manuellement quelques cellules supplémentaires et vérifier si Flash Fill est capable de discerner le motif ou non.

Parfois, vous pouvez ne pas voir le motif en gris (comme indiqué à l'étape 2 ci-dessus). Si tel est le cas, suivez les étapes ci-dessous pour obtenir le résultat Flash Fill :

1. Saisissez le texte manuellement dans deux cellules.
2. Sélectionnez ces deux cellules
3. Passez le curseur sur la partie inférieure droite de la sélection. Vous remarquerez que le curseur se transforme en icône plus
4. Double-cliquez dessus (touche gauche de la souris). Cela remplira toutes les cellules. À ce stade, les résultats sont probablement incorrects et ne correspondent pas à ce que vous attendiez.
5. En bas à droite des données résultantes, vous verrez une petite icône de remplissage automatique. Cliquez sur cette icône de remplissage automatique
6. Cliquez sur Remplissage Flash

Les étapes ci-dessus vous donneraient le résultat de Flash Fill (basé sur le motif qu'il a déduit).

Vous pouvez également utiliser Flash Fill pour obtenir le nom ou le deuxième prénom. Dans les deux premières cellules, entrez le nom de famille (ou le deuxième prénom) et le remplissage flash pourra comprendre le motif

Réorganiser le nom à l'aide de Flash Fill

Flash Fill est un outil intelligent et il peut également déchiffrer un motif légèrement complexe

Par exemple, supposons que vous ayez un ensemble de données comme indiqué ci-dessous et que vous souhaitiez réorganiser le nom de Rick Novak à Novak, Rick (où le nom de famille vient en premier suivi d'une virgule, puis du prénom).

Vous trouverez ci-dessous les étapes à suivre :

1. Dans la cellule adjacente, tapez manuellement Novak, Rick
2. Dans la deuxième cellule, tapez manuellement Connor, Susan. Pendant que vous tapez, vous verrez Flash Fill vous montrer une liste des noms dans le même format (en gris).
3. Lorsque vous voyez les noms en gris, parcourez-les rapidement pour vous assurer qu'il affiche les bons noms. Si ceux-ci sont corrects, appuyez sur la touche Entrée et Flash Fill remplira automatiquement le reste des cellules avec les noms dans le même format.

Supprimez le deuxième prénom (ou obtenez simplement le deuxième prénom)

Vous pouvez également utiliser Flash Fill pour vous débarrasser du deuxième prénom ou n'obtenir que le deuxième prénom.

Par exemple, supposons que vous ayez un ensemble de données comme indiqué ci-dessous et que vous souhaitiez obtenir uniquement le prénom et le nom et non le deuxième prénom.

Vous trouverez ci-dessous les étapes à suivre :

1. Dans la cellule adjacente, tapez manuellement Rick Novak
2. Dans la deuxième cellule, tapez manuellement Susan Connor. Pendant que vous tapez, vous verrez Flash Fill vous montrer une liste des noms dans le même format (en gris).
3. Lorsque vous voyez les noms en gris, parcourez-les rapidement pour vous assurer qu'il affiche les bons noms. Si elles sont correctes, appuyez sur la touche Entrée et Flash Fill remplira automatiquement le reste des cellules avec les noms sans le deuxième prénom.

De même, si vous souhaitez uniquement obtenir le deuxième prénom, saisissez le deuxième prénom dans les deux premières cellules et utilisez Flash Fill pour obtenir le deuxième prénom de tous les autres noms.

Les exemples présentés dans ce didacticiel utilisent des noms lors de la manipulation des données de texte. Vous pouvez utiliser les mêmes concepts pour travailler également avec d'autres formats de données (tels que les adresses, les noms de produits, etc.)