Vous devez séparer les noms dans Excel ?
Les ensembles de données contenant les noms des personnes ont généralement le prénom et le nom de famille dans une seule cellule, désignée par un caractère d’espacement.
Bien que cela ne soit pas nécessairement mauvais, il peut être plus utile d’avoir le prénom et le nom de famille dans des colonnes différentes. De cette façon, vous pouvez plus facilement effectuer des analyses supplémentaires sur vos données, comme le tri et le filtrage sur la base du seul nom de famille.
Ce post vous montrera toutes les façons d’extraire les noms et prénoms dans différentes cellules d’Excel !
Séparer les noms et prénoms avec du texte en colonnes
Lorsque vous avez besoin de répartir les noms et prénoms dans vos données de manière ponctuelle, la méthode Texte vers Colonnes doit être utilisée.
La fonction Texte en colonnes vous permettra de répartir les noms dans une nouvelle plage ou de remplacer les données actuelles. Selon ce que vous préférez.
C’est la meilleure solution pour les cas d’utilisation ponctuelle, car vous devrez effectuer à nouveau la division du texte en colonnes pour tout nouveau nom ajouté à la liste.
Voici comment utiliser la fonction « text to column » pour séparer le nom et le prénom.
- Sélectionnez la plage de noms que vous souhaitez diviser.
Il ne peut s’agir que d’une seule colonne. Si vous essayez de procéder avec une sélection de plus d’une colonne, vous obtiendrez un message d’avertissement vous indiquant que Microsoft Excel ne peut convertir qu’une seule colonne à la fois.
- Allez dans l’onglet Données.
- Cliquez sur la commande Texte en colonne dans la section Outils de données.
Cela ouvrira l’assistant de conversion de texte en colonnes qui vous guidera à travers les étapes de la division de vos noms.
- Sélectionnez l’option Délimité pour diviser vos noms.
- Appuyez sur le bouton Suivant.
- Désélectionnez l’option d’onglet par défaut.
- Sélectionnez l’option Espace.
- Cliquez sur le bouton Suivant.
Lorsque vous sélectionnez l’option Espace, vous devriez voir apparaître une ligne noire qui sépare vos noms dans la section Aperçu des données du menu.
- Sélectionnez une cellule pour la destination.
- Cliquez sur le bouton « Terminer ».
La Destination est l’emplacement dans votre feuille où Excel placera les noms et prénoms, elle devra donc être vide. Sélectionnez une seule cellule qui sera la cellule supérieure gauche de la plage où Excel placera tous les noms.
Excel vous avertira si vous essayez d’utiliser une destination où il y a déjà des données en place et vous demandera si vous voulez les remplacer.
Excel divisera les noms en fonction du caractère espace qui les sépare et placera les résultats à l’endroit souhaité.
Séparer les noms et prénoms avec une formule de texte
Une autre façon d’obtenir le nom et le prénom à partir de votre ensemble de noms complets est d’utiliser une combinaison de fonctions texte pour analyser chaque nom.
Vous pouvez utiliser les fonctions LEFT et RIGHT pour obtenir le nombre de caractères souhaité à partir du début ou de la fin de n’importe quelle valeur de texte.
Vous pouvez les utiliser en combinaison avec la fonction FIND pour obtenir l’emplacement du caractère espace et la fonction LEN pour obtenir le nombre total de caractères dans le nom.
Obtenir le prénom
= LEFT ( B3, FIND ( " ", B3 ) - 1 )La formule ci-dessus extrait le prénom du nom complet dans la cellule B3.
La fonction FIND trouvera la place du premier caractère d’espace. En soustrayant 1 à ce résultat, vous obtiendrez la place du dernier caractère du prénom.
La fonction LEFT utilise ensuite cette valeur pour retourner ces premiers caractères du nom.
Obtenir le nom de famille
= RIGHT ( B3, LEN ( B3 ) - FIND ( " ", B3 ) )La formule ci-dessus permet d’obtenir le nom de famille à partir du nom complet dans la cellule B3.
La fonction LEN récupère le nombre total de caractères du nom complet. La fonction FIND récupère l’emplacement du caractère d’espacement qui sépare le prénom et le nom de famille.
En soustrayant la longueur totale du nom de la place du caractère d’espacement, vous obtenez le nombre de caractères du nom de famille.
Celle-ci est ensuite utilisée dans la fonction DROIT pour extraire le nom de famille.
Séparer les noms et prénoms avec la fonction FILTERXML
Un moyen sournois de diviser le texte en fonction du caractère espace est d’utiliser la fonction FILTERXML.
Cela vous permet d’extraire certaines parties des données XML en fonction du nom du nœud.
Mais vous pouvez l’utiliser pour extraire les noms en créant d’abord des données XML à partir de votre nom.
= TRANSPOSE ( FILTERXML ( "<t><s>" & SUBSTITUTE ( B3," ","</s><s>")&"</s></t>","//s"))La formule ci-dessus divisera le nom complet dans la cellule B3 en nom et prénom.
FirstName</s><s>LastNameLa fonction SUBSTITUTE est utilisée pour remplacer l’espace par du texte, comme ci-dessus.
<t><s>FirstName</s><s>LastName</s></t>
Le & est ensuite utilisé pour concaténer le texte au début et à la fin des noms, ce qui donne quelque chose comme ci-dessus.
Il s’agit maintenant d’une structure de données XML et vous pouvez utiliser la fonction FILTERXML pour extraire chacun des noms.
= FILTERXML ( "<t><s>FirstName</s><s>LastName</s></t>","//s")Le filtre //s ci-dessus extraira chacun des éléments contenus dans les balises s qui sont maintenant le nom et le prénom.
Vous obtenez ainsi une colonne de noms et vous pouvez utiliser la fonction TRANSPOSE pour la transformer en une ligne de noms.
Obtenez le prénom uniquement
L’utilisation ci-dessus de la fonction FILTERXML divisera chaque nom et donnera un tableau de deux cellules.
Mais vous pouvez modifier un peu cette formule pour n’obtenir que le premier nom.
= FILTERXML ( "<t><s>" & SUBSTITUTE ( B3, " ", "</s><s>" ) & "</s></t>", "//s[1]" )La version ci-dessus de la formule FILTERXML ne renverra que le premier nom. Le filtre //s[1] ne renverra que le 1er élément d’un s-tag.
Obtenez le nom de famille uniquement
= FILTERXML ( "<t><s>" & SUBSTITUTE ( B3, " ", "</s><s>" ) & "</s></t>", "//s[2]" )De même, la formule ci-dessus utilise le filtre //s[2] pour renvoyer le deuxième élément d’une balise s, à savoir le nom de famille.
Séparer les noms et prénoms avec la fonction TEXTSPLIT
Si vous disposez d’une version plus récente d’Excel, vous pouvez éviter les formules complexes car il existe une fonction dédiée à la division du texte.
Vous pouvez utiliser la fonction TEXTSPLIT pour diviser une valeur de texte en fonction d’un caractère de délimitation.
Cela signifie que vous pouvez utiliser le caractère espace comme délimiteur pour séparer le nom et le prénom,
= TEXTSPLIT ( B3, " " )La formule ci-dessus divisera le nom dans la cellule B3 en fonction du caractère espace.
C’est aussi simple que possible !
Séparer les noms et prénoms avec un remplissage flash
Flash Fill est un moyen pratique de transformer vos données sur la base d’exemples.
Vous fournissez quelques exemples des résultats que vous souhaitez dans la colonne adjacente et Flash Fill détermine le modèle et remplit le reste.
Cela peut facilement séparer vos noms lorsque vous fournissez les résultats de l’exemple.
Vous pouvez consulter ce guide sur le remplissage flash dans Excel pour plus de détails sur cet outil de données polyvalent.
Tapez quelques prénoms seuls dans les cellules situées à droite du nom complet correspondant.
Cela devrait déclencher Flash Fill et vous verrez en gris clair les résultats suggérés. Appuyez sur Entrée pour accepter ces résultats et les placer dans les cellules.
Si le remplissage flash ne se déclenche pas automatiquement après les deux premières entrées, vous devrez peut-être le déclencher manuellement.
Sélectionnez la première cellule vide sous vos exemples de prénoms.
Ensuite, allez dans l’onglet Données et cliquez sur la commande Flash Fill qui se trouve dans la section Outils de données.
💡 Conseil: vous pouvez également utiliser le raccourci clavier Ctrl + E pour utiliser le remplissage flash.
Vous pouvez maintenant répéter le processus dans la colonne suivante pour obtenir les noms de famille.
📝 Remarque: notez simplement que ces valeurs sont statiques et ne seront pas mises à jour si vous mettez à jour la colonne des noms complets. Vous devrez réexécuter la commande Flash Fill pour les mettre à jour.
Séparer les noms et prénoms avec Power Query
Power Query est un outil extraordinaire pour transformer vos données de toutes les manières imaginables.
Diviser vos noms est une tâche simple pour Power Query !
Power Query sera sans aucun doute la solution pour votre tâche consistant à placer les noms et prénoms dans des cellules séparées si vous souhaitez un processus reproductible pour importer et transformer vos données dans Excel.
Avant d’utiliser Power Query, vous devez ajouter vos données de nom à un tableau Excel.
Maintenant vous pouvez utiliser Power Query pour diviser les noms dans votre table.
- Allez dans l’onglet Données.
- Cliquez sur la commande From Table/Range.
Cela ouvrira l’éditeur Power Query.
- Cliquez avec le bouton droit de la souris sur l’en-tête de la colonne contenant les noms à scinder.
- Choisissez Split Column dans les options.
- Choisissez l’option Par délimiteur dans le sous-menu.
Cela ouvrira le menu » Split Column by Delimiter » (diviser la colonne par le délimiteur ) où vous pouvez sélectionner le délimiteur sur lequel diviser les données.
- Sélectionnez Espace dans le menu déroulant Sélectionner ou entrer le délimiteur.
- Appuyez sur le bouton OK.
Voilà, c’est fait. Les noms sont tous séparés et vous devriez avoir deux nouvelles colonnes avec le nom et le prénom.
💡 Conseil: Double-cliquez sur les nouveaux titres de colonne pour les renommer.
Vous pourrez maintenant recharger les données transformées dans Excel.
- Allez dans l’onglet Accueil.
- Cliquez sur Fermer et charger.
Cela ouvrira le menu Importer des données où vous pouvez choisir où vous voulez charger les données.
- Sélectionnez l’option Table.
- Sélectionnez une feuille de calcul existante ou une nouvelle feuille de calcul pour les données à charger.
- Appuyez sur le bouton OK.
Vos noms séparés seront ensuite chargés à l’endroit que vous avez choisi.
💡 Conseil: Si vous modifiez, ajoutez ou supprimez des noms dans la liste source, vous pouvez facilement mettre à jour les noms séparés pour refléter les modifications. Allez dans l’onglet Données et cliquez sur la commande Rafraîchir tout.
Séparer les noms et prénoms avec Power Pivot
Si vous analysez des données qui comprennent un nom complet, vous voudrez peut-être diviser les noms à l’aide de Power Pivot.
Le modèle de données Power Pivot peut contenir un grand nombre de lignes en mémoire, il est donc parfait pour travailler avec n’importe quel grand ensemble de données.
Après avoir chargé votre ensemble de données dans le modèle de données, vous pouvez ajouter des colonnes supplémentaires avec des calculs utilisant le langage de formule DAX.
Le langage de formule DAX a beaucoup de fonctions communes avec Excel et dans ce cas, les fonctions utilisées seront exactement les mêmes que les fonctions texte vues précédemment.
Vous pouvez ajouter vos données au modèle de données. Sélectionnez une cellule dans le tableau qui contient vos noms et allez dans l’onglet Power Pivot puis cliquez sur l’option Ajouter au modèle de données.
Cela ouvrira l’éditeur Power Pivot avec le chargement de la table dans le modèle.
Vous pourrez créer de nouvelles colonnes en cliquant sur l’intitulé Ajouter une colonne à droite de vos données.
= LEFT ( Names[Full Name], FIND ( " ", Names[Full Name] ) - 1 )La formule ci-dessus permet d’obtenir le premier nom de la colonne entière.
= RIGHT ( Names[Full Name], LEN ( Names[Full Name] ) - FIND ( " ", Names[Full Name] ) )La formule ci-dessus renvoie le nom de famille pour l’ensemble de la colonne.
Vous allez maintenant pouvoir utiliser ces colonnes calculées dans un tableau croisé dynamique. Allez dans l’onglet Accueil du complément Power Pivot et cliquez sur la commande PivotTable et choisissez l’option PivotTable dans le menu.
Ces nouvelles colonnes calculées de nom et de prénom seront alors disponibles comme champs dans le menu Champs de votre tableau croisé dynamique.
Séparer les noms et prénoms avec VBA
Vous avez peut-être besoin de séparer souvent les noms et vous voulez une solution en un clic pour cela.
VBA pourrait être la meilleure solution pour vous.
Vous pouvez ajouter une macro à votre classeur qui divisera les noms en colonnes adjacentes.
Cette opération peut être exécutée en ajoutant la macro à la barre d’outils d’accès rapide afin qu’elle soit toujours disponible et utilisable en un clic.
Allez dans l’onglet Développeur et cliquez sur la commande Visual Basic.
Vous pouvez consulter cet article pour plus de détails sur l’activation de l’onglet Développeur dans le ruban. Vous pouvez également appuyer sur Alt + F11 pour ouvrir l’éditeur Visual Basic sans l’onglet Développeur.
Cela ouvrira l’éditeur Visual Basic. Vous pouvez maintenant créer un nouveau module dans l’éditeur pour y ajouter votre code.
Allez dans le menu Insertion de l’éditeur Visual Basic et sélectionnez l’option Module.
Sub SplitNames()
Dim rng As Range
Dim arrNames() As String
Dim colCount As Integer
colCount = Selection.Columns.Count
If colCount <> 1 Then
MsgBox ("Select a single column!")
End
End If
For Each rng In Selection
arrNames = Split(rng.Value, " ")
rng.Offset(0, 1).Value = arrNames(0)
rng.Offset(0, 2).Value = arrNames(1)
Next rng
End Sub
Ajoutez le code ci-dessus à votre module dans l’éditeur Visual Basic.
Ce code vérifie d’abord que vous n’avez sélectionné qu’une plage avec une seule colonne. Si vous avez sélectionné plusieurs colonnes, il vous avertit de ne sélectionner qu’une seule colonne, puis arrête l’exécution.
Lorsqu’une seule colonne est sélectionnée, le code parcourt en boucle la plage sélectionnée et divise chaque cellule en fonction du caractère espace.
La première et la deuxième partie des noms de fractionnement sont saisies dans les cellules situées directement à droite de votre sélection initiale.
Après avoir sélectionné vos noms et exécuté le code, vous obtiendrez la séparation des noms et prénoms dans Excel.
Séparer les noms et prénoms avec des scripts Office
Si vous utilisez principalement Excel en ligne, vous devrez alors utiliser les scripts Office pour créer un code personnalisé afin de séparer vos noms.
Un script Office similaire peut être créé.
Allez dans l’onglet Automate et cliquez sur la commande Nouveau script pour ouvrir l’éditeur de code pour les scripts Office.
function main(workbook: ExcelScript.Workbook) {
//getselected range
let rng = workbook.getSelectedRange();
let rows = rng.getRowCount();
let cols = rng.getColumnCount();
if (cols != 1) {
return;
};
//loop through selected cells
for (let i = 0; i < rows; i++) {
for (let j = 0; j < cols; j++) {
//split name based on space
let txtName = rng.getCell(i, j).getValue().toString();
let arrName = txtName.split(" ");
//enter names in adjacent cells
rng.getCell(i, j + 1).setValue(arrName[0]);
rng.getCell(i, j + 2).setValue(arrName[1]);
};
};
};Ajoutez le code ci-dessus dans l’éditeur de code et appuyez sur le bouton Enregistrer.
Vous pouvez maintenant sélectionner une seule colonne de noms dans votre feuille Excel et appuyer sur le bouton Exécuter.
Le code vérifiera si votre sélection est une colonne unique et arrêtera l’exécution du code si c’est le cas.
Sinon, le code continuera à parcourir en boucle la cellule sélectionnée pour séparer les noms. Le prénom et le nom sont ajoutés dans les colonnes à droite de votre sélection.
Conclusions
De nombreux ensembles de données comprennent un champ de nom. Parfois, il s’agit d’un nom complet dans une seule cellule et vous devrez séparer le nom en deux éléments : le prénom et le nom de famille.
Text to column ou Flash Fill permet d’effectuer le travail en quelques étapes simples et est parfait pour les utilisations ponctuelles.
Une formule représente une solution plus dynamique qui convient mieux lorsque des noms sont ajoutés ou modifiés dans l’ensemble de données source. La formule que vous utiliserez dépendra des fonctions disponibles dans votre version d’Excel.
Pour les ensembles de données plus importants ou les ensembles de données externes qui doivent d’abord être importés dans Excel, la solution Power Query ou Power Pivot est la plus adaptée.