Dans Excel, il est très facile de diviser le prénom, le deuxième prénom et le nom de famille qui apparaissent dans la même colonne en colonnes distinctes en utilisant différentes manières.
Supposons que vous ayez reçu une liste de contacts avec tous les noms répertoriés sous forme de noms complets dans une seule colonne et que vous deviez probablement séparer le prénom, le deuxième prénom et le nom de famille et les diviser en colonnes séparées. Il existe plusieurs manières différentes de séparer les noms - en utilisant la fonction Texte en colonnes, le remplissage Flash et les formules.
Dans Excel, il est très facile de diviser les noms d'une colonne en deux colonnes ou plus. Dans cet article, nous allons vous montrer comment diviser les noms en différentes colonnes dans Excel en utilisant différentes manières.
Comment diviser les noms dans Excel
Il existe trois façons simples de diviser les noms dans Excel. Selon la structure des données et si vous souhaitez que les noms de fractionnement soient statiques ou dynamiques, choisissez l'une des méthodes suivantes :
- Diviser les noms à l'aide de la fonction Texte en colonnes
- Séparer les noms à l'aide de formules
- Séparez les noms à l'aide de Flash Fill
Séparer les noms à l'aide de l'assistant de texte en colonne
L'Assistant Texte en colonne est l'un des moyens les plus courants de séparer les prénoms et les noms dans Excel ainsi que les deuxièmes prénoms. Assurez-vous qu'il y a des colonnes vides à côté des noms que vous allez diviser car les noms complets seront divisés en colonnes séparées.
Par exemple, vous avez l'ensemble de données ci-dessous avec des noms complets et vous souhaitez diviser/séparer le prénom et le nom et les stocker dans des cellules séparées.
Tout d'abord, mettez en surbrillance la colonne des noms complets que vous souhaitez séparer. Ensuite, allez dans l'onglet « Données » et cliquez sur l'option « Texte dans les colonnes » dans la section « Outils de données ».
L'assistant de conversion de texte en colonnes s'ouvrira. À la première étape de l'assistant, sélectionnez l'option « Délimité » et cliquez sur « Suivant ».
À l'étape 2 sur 3 de l'assistant de conversion de texte en colonnes, sélectionnez le délimiteur qui sépare vos données, supprimez toutes les autres coches et cliquez sur « Suivant ». Dans notre cas, « espace » sépare le prénom et le nom, nous choisissons donc ce délimiteur.
La section Aperçu des données ci-dessous dans la fenêtre montre comment vos noms sont analysés.
À l'étape 3 sur 3, vous sélectionnez le format et la destination des données et cliquez sur « Terminer ».
Normalement, le "Général" par défaut fonctionnera très bien pour la plupart des types de données. Dans le champ « Destination », spécifiez l'emplacement où vous souhaitez que la sortie soit affichée. Vous devez spécifier l'adresse de la première cellule de la colonne où vous voulez les résultats (B2, dans notre cas).
N'oubliez pas que si vous ne spécifiez pas la cellule de destination, l'assistant écrasera les données d'origine, alors assurez-vous de choisir une colonne vide.
Une fois que vous avez cliqué sur le bouton « Terminer », les noms complets seront instantanément séparés en deux colonnes distinctes (Prénom et Nom).
Suivez les mêmes étapes si vous avez un prénom, un deuxième prénom et un nom de famille, et vos noms seront divisés en trois colonnes au lieu de deux.
Noter: Le résultat de cette méthode est statique. Cela signifie que si vous modifiez le nom d'origine, vous devez tout recommencer pour diviser les noms.
Diviser les noms séparés par des virgules
Si le prénom et le nom sont séparés par des virgules, suivez les étapes ci-dessous pour supprimer la virgule et séparer les prénoms et les noms.
Dans l'exemple suivant, les noms sont formatés au format inversé (Nom, Prénom), où le nom de famille vient en premier suivi d'une virgule, puis du prénom.
Sélectionnez les noms et accédez à Données -> Texte vers colonne. À l'étape 1, choisissez le « Délimiteur » et cliquez sur « Suivant ». À l'étape 2, sous Délimiteurs, cochez « virgule » (,) comme délimiteur car vos noms sont séparés par une virgule.
À la dernière étape, vous choisissez le format de données comme « Général », spécifiez la destination et cliquez sur « Terminer ».
Maintenant, vous aurez des noms dans des colonnes séparées.
Séparer les noms à l'aide de formules
L'assistant Text to Columns permet de séparer rapidement et facilement les noms. Cependant, si vous souhaitez modifier les noms d'origine et que vous souhaitez une méthode dynamique qui se mettra automatiquement à jour à chaque fois que vous modifierez les noms, la division des noms avec des formules est le bon choix. Vous pouvez utiliser les fonctions LEFT, RIGHT, MID, LEN et SEARCH ou FIND pour séparer les noms.
Séparez le prénom et le nom dans Excel à l'aide de formules
Obtenez le prénom
Disons que vous avez l'ensemble de données ci-dessous et que vous souhaitez séparer le prénom dans une cellule distincte. Vous devez combiner les fonctions FIND et LEFT en une seule formule pour obtenir le prénom.
Utilisez la formule suivante pour obtenir le prénom :
=GAUCHE(A2,TROUVER(" ",A2)-1)Cette formule utilise la fonction FIND pour trouver la position du caractère espace (" ") entre le prénom et le nom et soustrait 1 pour exclure l'espace lui-même. Ce numéro est ensuite fourni à la fonction LEFT, qui utilise ce numéro de position pour extraire tout le texte qui le précède. Vous pouvez également utiliser la fonction SEARCH au lieu de la fonction FIND.
Après avoir entré la formule dans une cellule vide (B2), faites glisser la poignée de remplissage vers d'autres cellules pour appliquer cette formule, et tous les prénoms ont été divisés dans la colonne B comme indiqué ci-dessous :
Vous pouvez soit imbriquer les fonctions SEARCH et FIND dans la fonction LEFT pour extraire le prénom. La principale différence entre les deux fonctions est que FIND est sensible à la casse, tandis que SEARCH est insensible à la casse.
Si vous avez une virgule (,) au lieu d'un espace entre le prénom et le nom, utilisez une virgule comme premier argument dans la fonction FIND :
=GAUCHE(A2,RECHERCHER(",",A2)-1)Obtenez le nom de famille
Maintenant, si vous devez extraire le nom de famille, utilisez la fonction DROITE. La formule suivante extraira le nom de famille du même ensemble de données :
=DROITE(A2,LEN(A2)-TROUVER(" ",A2))La formule trouve d'abord la position du caractère espace, soustrait ce nombre de la longueur totale de la chaîne (qui est renvoyée par la fonction LEN), et ce nombre est ensuite fourni à la fonction RIGHT pour extraire ce nombre de caractères de la fin de la chaîne (Nom).
Séparez le prénom, le deuxième prénom et le nom de famille dans Excel à l'aide de formules
Le fractionnement des noms qui incluent un deuxième prénom nécessite des formules différentes, selon le format de nom que vous avez.
Pour obtenir le Prénom lorsque vous avez un deuxième prénom ou une initiale du deuxième prénom, utilisez la même formule LEFT FIND que vous connaissez déjà.
Obtenez le nom de famille
La formule RIGHT FIND ci-dessus fonctionne bien lorsqu'il n'y a que le prénom et le nom, elle ne sera pas très utile au cas où vos noms d'origine contiennent un deuxième prénom ou une initiale du deuxième prénom. C'est parce que vous n'avez pas tenu compte de deux espaces dans le nom.
Pour obtenir le nom de famille lorsque vous avez également un deuxième prénom, utilisez cette formule :
=DROITE(A2,LEN(A2)-RECHERCHE(" ",A2,RECHERCHE(" ",A2,1)+1))Pour extraire le nom de famille, déterminez d'abord la position du deuxième caractère espace en utilisant les fonctions RECHERCHE imbriquées, ajoutez 1 à RECHERCHE(” “,A2,1) pour commencer l'extraction avec le caractère suivant. Ensuite, soustrayez la position du 2e espace de la longueur totale de la chaîne et obtenez la longueur du nom de famille comme nombre de résultat. Donnez ensuite ce nombre résultant à la fonction RIGHT pour extraire le nombre de caractères de la fin de la chaîne.
Obtenez le deuxième prénom
La fonction MID utilise trois arguments, le premier argument spécifie le texte ou l'adresse de la cellule, le second spécifie la position de départ et le dernier argument indique le nombre de caractères pour extraire le deuxième prénom de cette position.
La syntaxe :
=MID(texte, start_num, num_chars)Pour obtenir le deuxième prénom, entrez cette formule dans une cellule vide :
=MID(A2,RECHERCHE(" ",A2)+1,RECHERCHE(" ",A2,RECHERCHE(" ",A2)+1)-RECHERCHE(" ",A2)-1)Voyons comment fonctionne cette formule complexe :
Pour extraire le deuxième prénom ou l'initiale du deuxième prénom, vous devez déterminer la position des deux espaces dans le nom complet. Pour trouver la position du premier caractère espace, entrez ce RECHERCHE(" ",A2) fonction dans l'argument 'start_num' et ajoutez 1 pour démarrer l'extraction à partir du caractère suivant.
Ensuite, pour connaître la longueur du deuxième prénom, mettez ceci RECHERCHE(" ",A2,RECHERCHE(" ",A2)+1)-RECHERCHE(" ",A2)-1 fonction imbriquée dans l'argument 'num_chars', qui soustrait la position du 1er espace de la position du 2ème espace, et soustrait 1 du résultat pour supprimer un espace de fin. Le résultat final vous dira combien de caractères extraire.
Maintenant, la fonction MID avec la position de départ du deuxième prénom et les caractères numériques à extraire sépare le deuxième prénom du nom complet (A2).
Séparer les noms dans Excel à l'aide de Flash Fill
Le remplissage flash est principalement utilisé pour remplir automatiquement les données d'un motif spécifique. Il peut également être utilisé pour diviser les noms. Il est uniquement disponible dans Excel 2013, 2016, 2019 et 365.
Disons que vous avez l'ensemble de données ci-dessous et que vous souhaitez obtenir uniquement les prénoms des noms complets.
Dans la cellule adjacente au nom d'origine, tapez le prénom. Dans ce cas, tapez « Steve » dans la cellule B2.
Ensuite, commencez à taper le prénom dans la deuxième cellule de la colonne. Pendant que vous tapez, si Excel détecte un motif, Flash Fill vous affichera automatiquement une liste du prénom dans d'autres cellules (en gris).
Lorsque vous voyez la liste des noms en gris et si ces noms sont corrects, appuyez simplement sur la touche « Entrée » et Flash Fill remplira automatiquement le reste de la colonne avec les prénoms.
Suivez la même procédure pour séparer les noms de famille dans une colonne distincte.
Le résultat:
Flash Fill fonctionne en détectant un modèle dans ces données et en suivant ce modèle tout en vous donnant les données modifiées. Au début, lorsque vous entrez le prénom dans la première cellule, Flash Fill ne reconnaît pas le motif. Mais lorsque vous recommencez à saisir le prénom dans la deuxième cellule, Flash Fill reconnaît le motif et vous montre la suggestion de fractionner les prénoms. Ensuite, appuyez simplement sur la touche « Entrée ».
Généralement, la fonction Flash Fill est activée par défaut. Si cela ne fonctionne pas dans votre Excel, après avoir tapé le prénom dans la première cellule, vous pouvez simplement sélectionner la deuxième cellule et cliquer sur le bouton « Remplissage Flash » du groupe Outils de données sur l'onglet « Données ».
Alternativement, vous pouvez également appuyer sur « Ctrl » + « E » pour obtenir les mêmes résultats.
Maintenant, le reste des cellules sera rempli avec des prénoms.
Parfois, vous pouvez ne pas voir la suggestion de motif en gris, dans ce cas, vous pouvez utiliser la poignée de remplissage pour obtenir le résultat Flash Fill.
Tout d'abord, tapez les noms manuellement dans deux cellules et sélectionnez ces deux cellules. Ensuite, placez votre curseur dans le coin inférieur droit de la sélection. Vous remarquerez que le curseur passe d'un petit carré vert (icône de remplissage) à une icône plus.
Ensuite, double-cliquez sur cette icône plus. Cela remplira le reste des cellules. À ce stade, les résultats sont incorrects, vous verrez que les deux prénoms sont répétés encore et encore. Ensuite, dans le coin inférieur droit des données résultantes, vous verrez une petite icône de remplissage automatique comme illustré ci-dessous. Cliquez sur cette icône « Remplissage automatique » et sélectionnez « Remplissage flash ».
Cela remplirait les prénoms dans toutes les cellules :
Supprimer le deuxième prénom
Vous pouvez également utiliser l'outil Flash Fill pour supprimer le deuxième prénom du nom complet.
Par exemple, disons que vous avez l'ensemble de données ci-dessous et que vous souhaitez obtenir uniquement le prénom et le nom sans le deuxième prénom ou l'initiale du deuxième prénom.
Pour obtenir des noms sans deuxième prénom ni initiale, tapez manuellement « Lord Stark » dans la cellule adjacente. Ensuite, dans la deuxième cellule adjacente, tapez « Daenerys Targaryen ». Pendant que vous tapez, Flash Fill reconnaîtra un motif et vous montrera une liste de noms sans deuxième prénom (en gris).
Si la suggestion est correcte, appuyez sur la touche « Entrée » et Flash Fill remplira automatiquement le reste des cellules avec les noms sans le deuxième prénom.
Si vous souhaitez obtenir uniquement les deuxièmes prénoms sans prénom et nom, entrez le deuxième prénom dans les deux premières cellules et utilisez l'outil Flash Fill pour obtenir les deuxièmes prénoms de tous les noms complets d'une colonne.
Ce didacticiel montre comment séparer les noms tout en manipulant les données de texte. Ces méthodes peuvent également être utiles lorsque vous travaillez avec d'autres formes de données telles que des adresses, des noms de produits, des noms de marque, etc.