9 méthodes pour séparer les noms et les prénoms dans Microsoft Excel

13 août 2024 Paolo Bergamaschi Excel 0
Excel Microsoft
Excel Microsoft

Les jeux de données contenant les noms des personnes ont généralement le prénom et le nom dans une seule cellule, séparés par un espace. Bien que cela ne pose pas nécessairement de problème, il peut être plus utile d’avoir le prénom et le nom dans des colonnes distinctes. De cette façon, vous pouvez effectuer plus facilement des analyses de données supplémentaires, comme le tri et le filtrage par nom de famille uniquement.

1. Séparer les noms et prénoms avec Texte en colonnes

Lorsque vous devez diviser les noms et prénoms dans vos données de manière occasionnelle, «Texte en colonnes» devrait être votre méthode de prédilection. La fonction «Texte en colonnes» vous permettra de diviser les noms dans une nouvelle plage ou de remplacer les données actuelles. Selon votre préférence.

Ceci est le plus adapté aux utilisations ponctuelles, car vous devrez réappliquer la division de texte en colonnes pour chaque nouveau nom ajouté à la liste.

Voici comment utiliser «Texte en colonnes» pour séparer les noms et prénoms :

  1. Sélectionnez la plage de noms que vous souhaitez diviser.
  2. Allez dans l’onglet «Données».
  3. Cliquez sur la commande «Convertir en colonnes» dans la section «Outils de données».
  4. Sélectionnez l’option «Délimité» pour diviser vos noms.
  5. Désélectionnez l’option par défaut «Tabulation» et sélectionnez «Espace».
  6. Sélectionnez une cellule pour la «Destination».
  7. Cliquez sur «Terminer».

Excel divisera les noms en fonction de l’espace qui les sépare et placera les résultats à l’emplacement souhaité.

2. Séparer les noms et prénoms avec une formule de texte

Une autre façon d’obtenir le prénom et le nom à partir de votre ensemble de noms complets est d’utiliser une combinaison de fonctions textuelles pour analyser chaque nom.

Vous pouvez utiliser les fonctions «GAUCHE» (LEFT) et «DROITE» (RIGHT) pour obtenir le nombre de caractères souhaité à partir du début ou de la fin de toute valeur textuelle. Vous pouvez les utiliser en combinaison avec la fonction «CHERCHE» (FIND) pour obtenir la position du caractère d’espace et la fonction «NBCAR» (LEN) pour obtenir le nombre total de caractères dans le nom.

Obtenir le prénom

= GAUCHE ( B3, CHERCHE ( " ", B3 ) - 1 )

La formule ci-dessus extraira le prénom de la description complète du nom dans la cellule B3. La fonction «CHERCHE» trouvera la position du premier caractère d’espace. Lorsque vous soustrayez 1 de ce résultat, vous obtenez la position du dernier caractère du prénom. La fonction «GAUCHE» utilise ensuite cette valeur pour renvoyer ces premiers caractères du nom.

Obtenir le nom de famille

= DROITE ( B3, NBCAR ( B3 ) - CHERCHE ( " ", B3 ) )

La formule ci-dessus obtiendra le nom de famille de la description complète du nom dans la cellule B3. La fonction «NBCAR» obtient le compte total des caractères du nom complet. La fonction «CHERCHE» obtient la position du caractère d’espace qui sépare le prénom et le nom de famille. Lorsque vous soustrayez le nombre total de caractères du nom de la position du caractère d’espace, vous obtenez le nombre de caractères du nom de famille. Ces informations sont ensuite utilisées dans la fonction «DROITE» pour extraire le nom de famille.

3. Séparer les noms et prénoms avec la fonction FILTERXML

Une manière astucieuse de diviser le texte en fonction du caractère d’espace est d’utiliser la fonction «FILTERXML». Celle-ci est conçue pour permettre l’extraction de parties spécifiques des données XML en fonction du nom du nœud.

Mais vous pouvez l’utiliser pour extraire des noms en créant d’abord les 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 prénom et nom de famille.

La fonction «SUBSTITUTE» est utilisée pour remplacer l’espace par du texte tel que celui ci-dessus. Cela crée une structure de données XML et vous pouvez utiliser la fonction «FILTERXML» pour extraire chacun des noms.

Obtenir uniquement le prénom

= FILTERXML ( "<t><s>" & SUBSTITUTE ( B3, " ", "</s><s>" ) & "</s></t>", "//s[1]" )

La version précédente de la formule FILTERXML renverra uniquement le prénom. Le filtre //s[1] renverra uniquement le premier élément dans une balise s.

Obtenir uniquement le nom de famille

= FILTERXML ( "<t><s>" & SUBSTITUTE ( B3, " ", "</s><s>" ) & "</s></t>", "//s[2]" )

De même, la formule précédente utilise le filtre //s[2] pour renvoyer le deuxième élément dans une balise s, qui est le nom de famille.

4. Séparer les noms et prénoms avec la fonction TEXTSPLIT

Si vous avez une version récente d’Excel, vous pouvez éviter les solutions complexes avec des formules car il existe une fonction dédiée pour diviser le texte.

Vous pouvez utiliser la fonction «TEXTSPLIT» pour diviser une valeur textuelle en fonction d’un caractère délimiteur.

Cela signifie que vous pouvez utiliser le caractère d’espace comme délimiteur pour séparer le prénom et le nom de famille.

= TEXTSPLIT ( B3, " " )

La formule ci-dessus divisera le nom dans la cellule B3 en fonction du caractère d’espace. C’est l’une des méthodes les plus simples qui soient !

5. Séparer les noms et prénoms avec Remplissage instantané

«Remplissage instantané» (Flash Fill) est un moyen pratique de transformer vos données en fonction d’exemples. Fournissez quelques exemples des résultats que vous souhaitez dans la colonne adjacente, et «Remplissage instantané» déterminera le modèle et remplira le reste.

Cela peut facilement séparer vos noms lorsque vous fournissez des exemples de résultats.

Voici comment cela fonctionne :

  1. Tapez quelques prénoms seuls dans les cellules à droite du nom complet correspondant.
  2. Cela devrait déclencher «Remplissage instantané», et vous verrez les résultats suggérés en gris clair. Appuyez sur «Entrée» pour accepter ces résultats et les insérer dans les cellules.
  3. Si «Remplissage instantané» ne s’active pas automatiquement après les deux ou trois premières saisies, vous devrez l’activer manuellement.
  4. Sélectionnez la première cellule vide sous vos exemples de prénoms.
  5. Allez dans l’onglet «Données» et cliquez sur la commande «Remplissage instantané» dans la section «Outils de données».
  6. Répétez le processus dans la colonne suivante pour obtenir les noms de famille.

6. Séparer les noms et prénoms avec Power Query

Power Query est un outil extraordinaire pour transformer vos données de presque toutes les manières imaginables. Diviser vos noms est une tâche simple pour Power Query !

Power Query sera certainement la voie à suivre pour votre tâche d’obtenir le prénom et le nom dans des cellules séparées si vous souhaitez un processus répétable pour importer et transformer vos données dans Excel.

Avant d’utiliser Power Query, vous devrez ajouter vos données de noms dans un Tableau Excel.

Voici comment utiliser Power Query pour diviser les noms dans votre tableau :

  1. Allez dans l’onglet «Données».
  2. Cliquez sur la commande «À partir d’un tableau/d’une plage».
  3. Cela ouvrira l’éditeur Power Query.
  4. Cliquez avec le bouton droit sur l’en-tête de la colonne contenant les noms à diviser et choisissez «Diviser la colonne».
  5. Choisissez l’option «Par délimiteur» dans le sous-menu.
  6. Sélectionnez «Espace» dans le menu déroulant «Sélectionnez ou saisissez un délimiteur».
  7. Appuyez sur le bouton «OK».

Vous aurez maintenant deux nouvelles colonnes avec le prénom et le nom de famille séparés.

7. Séparer les noms et prénoms avec Power Pivot

Si vous analysez des données incluant un nom complet, vous pourriez vouloir diviser les noms à l’aide de Power Pivot.

Le modèle de données de Power Pivot peut contenir un grand nombre de lignes en mémoire, il est donc parfait pour travailler avec d’énormes ensembles 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 à l’aide du langage de formules DAX.

Le langage de formules DAX possède de nombreuses fonctions communes avec Excel et, dans ce cas, les fonctions utilisées seront exactement les mêmes que les fonctions textuelles vues précédemment.

Vous pouvez ajouter vos données au modèle de données. Sélectionnez une cellule dans le tableau contenant 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 tableau chargé dans le modèle.

Vous pourrez ajouter de nouvelles colonnes en cliquant sur l’en-tête «Ajouter une colonne» à droite de vos données.

= GAUCHE ( Noms[Nom Complet], CHERCHE ( " ", Noms[Nom Complet] ) - 1 )

La formule ci-dessus obtiendra le prénom pour toute la colonne.

= DROITE ( Noms[Nom Complet], NBCAR ( Noms[Nom Complet] ) - CHERCHE ( " ", Noms[Nom Complet] ) )

La formule ci-dessus renverra le nom de famille pour toute la colonne.

Vous pourrez maintenant utiliser ces colonnes calculées dans un tableau croisé dynamique.

8. Séparer les noms et prénoms avec VBA

Peut-être devez-vous séparer les noms souvent et souhaitez une solution en un clic pour cela.

VBA pourrait être la meilleure solution pour vous.

Vous pouvez ajouter une macro à votre feuille de calcul qui divisera les noms dans des colonnes adjacentes.

Cela peut être exécuté en ajoutant la macro à la barre d’outils Accès rapide afin qu’elle soit toujours disponible pour une utilisation en un clic.

Voici comment :

  1. Allez dans l’onglet «Développeur» et cliquez sur la commande «Visual Basic».
  2. Créez un nouveau module dans l’éditeur Visual Basic et ajoutez le code suivant :
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

Ce code vérifie d’abord que vous avez sélectionné une seule colonne. Si vous avez sélectionné plusieurs colonnes, il vous avertira de sélectionner une seule colonne et arrêtera l’exécution.

Lorsqu’une seule colonne est sélectionnée, le code parcourra la plage sélectionnée et divisera chaque cellule en fonction du caractère d’espace.

La première et la deuxième partie des noms divisés sont placées dans les cellules directement à droite de votre sélection d’origine.

Une fois que vous avez sélectionné vos noms et exécuté le code, vous aurez le prénom et le nom de famille divisés dans Excel.

9. Séparer les noms et prénoms avec Office Scripts

Si vous utilisez principalement Excel en ligne, vous devrez utiliser Office Scripts pour créer un code personnalisé afin de séparer vos noms.

Vous pouvez créer un script Office similaire :

  1. Allez dans l’onglet «Automatiser» et cliquez sur la commande «Nouveau script» pour ouvrir l’éditeur «Code Editor» pour Office Scripts.
  2. Ajoutez le code suivant à l’éditeur : 
    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 à l’éditeur «Code Editor» et appuyez sur le bouton «Enregistrer».

  1. Vous pouvez maintenant sélectionner une plage de colonne unique de noms dans votre feuille Excel et appuyer sur le bouton «Exécuter».
  2. Le code vérifiera si votre sélection est une colonne unique et arrêtera l’exécution du code dans le cas contraire.

Sinon, le code continuera et parcourra vos cellules sélectionnées pour diviser les noms. Le prénom et le nom de famille sont ajoutés dans les colonnes à droite de votre sélection.

Conclusions

De nombreux jeux de données incluront un champ de nom. Parfois, cela sera fourni sous forme de nom complet dans une seule cellule, et vous devrez séparer le nom en composants prénom et nom de famille. «Texte en colonne» ou «Remplissage instantané» résoudront le problème en quelques étapes simples et sont parfaits pour ces utilisations ponctuelles.

Une solution par formule représente une solution plus dynamique, mieux adaptée lorsque le jeu de données source ajoutera ou modifiera des noms. La formule que vous utiliserez dépendra des fonctions disponibles dans votre version d’Excel. Pour les jeux de données plus volumineux ou les jeux de données externes qui doivent d’abord être importés dans Excel, la solution Power Query ou Power Pivot sera plus efficace. Avez-vous déjà eu besoin de séparer des noms ? Comment avez-vous résolu le problème ? Faites-le moi savoir dans les commentaires ci-dessous !

Pubblicato in
13 août 2024 Paolo Bergamaschi Excel 0

Se vuoi rimanere aggiornato su 9 méthodes pour séparer les noms et les prénoms dans Microsoft Excel iscriviti alla nostra newsletter settimanale

Soyez le premier à commenter

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée.


*