Comment séparer le nom et prénom dans Excel ?

Dans cet article, vous allez apprendre à séparer un nom et prénom dans Excel.

Attention : les données à traiter doivent avant traitement être organisés en respectant un ordre identique : Nom Prénom ou Prénom Nom  et séparé par un caractère identique (Espace ou tout autres caractères).

1. Dans cet article, nous allons explorer la manière dont Excel peut nous aider à séparer les noms et prénoms en deux colonnes distinctes en utilisant la fonction native « Convertir ».
2. Nous aborderons également d'autres fonctions équivalentes.
3. Enfin, nous terminerons avec une démonstration de code VBA pour des manipulations plus avancées.

Méthodes de Séparation des Noms et Prénoms

1 - Utilisation de la fonction Texte en colonnes.

Nous allons sélectionner les cellules  à diviser puis dans l’onglet Données choisir « Convertir ».

Cette fonction va nous permettre de décomposer une chaine de caractères suivant différents paramètres.

L’assistant vous propose de choisir entre un partage Délimité ou sur une Largeur Fixe. Choisir « Délimité ».

Indiquer dans la fenêtre suivante le caractère sur lequel Excel doit exécuter le partage. Dans notre cas l’espace.

Astuce : par défaut laisser « Interpréter des séparateurs comme uniques. » coché : cela évitera si vous avez deux espaces entre le nom et le prénom de créer un champ vide entre le nom et le prénom. 

Après avoir validé l’étape deux, l’assistant vous propose la chaine qu’il va décomposer et vous demande d’indiquer la destination du partage.

Indiquer la cellule dans laquelle vous souhaitez qu’il mette les données. Dans notre cas nous allons placer les données en E8.

Vous pouvez également choisir le format de chaque colonnes (Standard, Texte, Date) ou non distribuée si vous souhaitez ne pas inclure cette colonne dans votre partage.

Voici le résultat après avoir cliqué sur « Terminer » :

Cette méthode est rapide mais les données doivent se trouver en liste et nous devrons recommencer les différentes étapes pour des données se trouvant à un autre emplacement sur la feuille.

Pour éviter d’avoir à recommencer nous allons utiliser une autre méthode en utilisant deux formules imbriquées Excel et il suffira alors de faire un copier-coller de la formule ainsi créé.

Formules pour séparer les noms et prénoms - utilisation de GAUCHE, DROITE, CHERCHE.

Nous allons dans un premier temps rechercher dans  la chaine de caractère  l’emplacement du caractère sur lequel  nous souhaitons effectuer le partage. Dans notre cas ici c’est toujours l’ « espace ».  Nous avons pour cela deux formules disponibles dans Excel : « TROUVE » ou « CHERCHE ».  TROUVE est sensible à la casse alors ne CHERCHE ne l’est pas. Dans notre exemple nous pouvons utiliser l’une ou l’autre puisque nous allons chercher le caractère « espace ».

Nous allons dans un premier temps rechercher dans la chaine de caractère l’emplacement du caractère sur lequel nous souhaitons effectuer le partage. Dans notre cas ici c’est toujours l’« espace ». 

Nous avons pour cela deux formules disponibles dans Excel : « TROUVE » ou « CHERCHE ».  

• TROUVE est sensible à la casse.
• CHERCHE ne l’est pas.

Dans notre exemple nous pouvons utiliser l’une ou l’autre puisque nous allons chercher le caractère « espace ».

Nous allons insérer sur la cellule E8 la formule « TROUVE » en sélectionnant l’onglet « Formule » et « Insérer une Fonction ».

Exemple

• Texte cherché : l’espace
• Texte : la cellule D8 – L’endroit où se trouve la chaine de caractères.
• N° départ : 1 – Le point de départ

En validant, Excel nous donne comme résultat « 7 ». Le caractère « Espace » se trouve donc en 7iéme position.

Nous allons pouvoir maintenant utiliser la fonction GAUCHE d’Excel qui va extraire à partir de la gauche le nombre de caractères que nous souhaitons :  

 = GAUCHE (Chaine ; Nombre de caractère à conserver)

Et en imbriquant la fonction « TROUVE ».

Ce qui nous donne : 

=GAUCHE (D8;TROUVE (" ";D8;1)-1)

= GAUCHE (Chaine de caractère ; TROUVE («_» ; Chaine de caractère ; 1)-1)

Vus avez ci-dessous le résultat après avoir validé la formule. Le NOM se trouve bien isolé dans la cellule.

Pour le PRENOM, nous allons utiliser les mêmes formules mais il nous faut avant déterminer la longueur de la chaine. Nous allons pour cela utiliser une formule Excel qui va nous fournir cette longueur :

= NBCAR(Cellule).

De cette longueur, nous déduirons le nombre de caractères où se trouve l’espace qui sépare le Nom et Prénom ce qui nous donnera le nombre de caractères à extraire mais depuis la droite.

       =DROITE ($D8;NBCAR ($D8)-TROUVE (" ";$D8;1))

• =Longueur de la chaine
• = Nombre de caractères jusqu'à l’espace
• =DROITE ($D8;16-7)

Nous avons bien extrait les 9 caractères depuis la droite.

Cette méthode nous permet donc de créer deux formules pour extraire le Nom puis le Prénom que nous devrons recopier respectivement vers le bas ou les recopier où nous souhaitons effectuer le partage. 

Dans le chapitre suivant, nous allons créer une formule personnalisée à l’aide du Code VBA qui nous permettra d’extraire directement le Nom et le Prénom.  

     Function SPLIT_NOM(ByVal Nom_Prenom As Variant) As Variant

    Dim Espace As Integer
    
    Application.Volatile ' Permet le recalcul de la fonction automatiquement

    ' Détermine l'endroit où se trouve le caractère espace
    Espace = InStr(1, Nom_Prenom, " ")
    
    ' Si un espace est trouvé, extraire le NOM, sinon retourner la chaîne originale
    If Espace > 0 Then
        SPLIT_NOM = Left(Nom_Prenom, Espace - 1)
    Else
        SPLIT_NOM = Nom_Prenom
    End If
    
End Function

Function SPLIT_PRENOM(ByVal Nom_Prenom As Variant) As Variant

    Dim Espace As Integer
    
    Application.Volatile ' Permet le recalcul de la fonction automatiquement

    ' Détermine l'endroit où se trouve le caractère espace
    Espace = InStr(1, Nom_Prenom, " ")
    
    ' Si un espace est trouvé, extraire le PRÉNOM, sinon retourner une chaîne vide
    If Espace > 0 Then
        SPLIT_PRENOM = Mid(Nom_Prenom, Espace + 1)
    Else
        SPLIT_PRENOM = ""
    End If
    
End Function

B. Conseils pour l’utilisation répétée et efficace des méthodes

Pour rendre l'automatisation de la séparation des noms et prénoms encore plus efficace, voici quelques astuces utiles :

1. Utilisation des raccourcis clavier : Apprendre et utiliser les raccourcis clavier peut considérablement accélérer votre travail. Par exemple, utiliser Ctrl + C pour copier et Ctrl + V pour coller peut vous faire gagner du temps. De plus, Alt + A + E vous permet d'accéder rapidement à la fonction Texte en colonnes.
2. Création de scripts personnalisés : Développer des scripts VBA personnalisés permet de traiter de grandes quantités de données sans intervention manuelle. Une fois créés, ces scripts peuvent être réutilisés autant de fois que nécessaire, assurant une constance et une efficacité accrues.
3. Utilisation des modèles Excel : Créez des modèles Excel préconfigurés avec les formules et les macros nécessaires pour séparer les noms et prénoms. Ainsi, vous pouvez simplement copier vos données dans le modèle et appliquer les fonctions automatiquement.
4. Répétition des tâches avec les Macros : Enregistrez des macros pour les tâches que vous effectuez fréquemment. Cela peut inclure la séparation des noms et prénoms, le nettoyage des données, et d'autres manipulations courantes. Les macros peuvent être exécutées en un clic, réduisant ainsi le temps et l'effort nécessaires.
5. Vérification des données : Utilisez des fonctions telles que SIERREUR pour gérer les erreurs dans vos formules. Cela garantit que vos données restent propres et exploitables, même lorsque des anomalies se produisent.

IV. Cas Spéciaux et Gestion des Exceptions

A. Traitement des noms composés ou des noms avec des titres (M., Dr., etc.)

La gestion des noms composés ou des noms avec des titres peut être délicate. Voici quelques solutions :

1. Utilisation de fonctions conditionnelles : Les fonctions telles que SI, CHERCHE, et EXTRAIRE peuvent être utilisées pour identifier et séparer les titres des noms et prénoms. Par exemple, une formule peut rechercher des titres courants comme "Dr.", "Mr.", "Mme" et les traiter séparément.
2. Macros VBA : Développez des macros VBA qui peuvent détecter et gérer automatiquement les titres et les noms composés. Ces macros peuvent inclure des listes de titres à rechercher et des règles pour les traiter.
3. Nettoyage des données avant séparation : Avant de séparer les noms et prénoms, nettoyez vos données pour standardiser les titres et les formats. Cela peut inclure la suppression des espaces en trop, la conversion des majuscules/minuscules, et l'harmonisation des titres.

B. Solutions pour les formats non standards

Les formats de noms non standards nécessitent souvent des solutions personnalisées. Voici quelques approches :

1. Formules complexes : Utilisez des combinaisons de fonctions Excel comme GAUCHE, DROITE, CHERCHE, et STXT pour gérer des formats de noms variés. Par exemple, une formule peut être conçue pour traiter des noms avec des caractères spéciaux ou des formats inhabituels.
2. Scripts VBA : Écrivez des scripts VBA capables de traiter des formats de noms non standards. Ces scripts peuvent inclure des règles spécifiques pour différents formats, garantissant ainsi une séparation correcte des noms et prénoms.
3. Outils de nettoyage de données : Utilisez des outils de nettoyage de données disponibles dans Excel ou des logiciels tiers pour préparer vos données avant la séparation. Ces outils peuvent identifier et corriger les anomalies dans les formats de noms.

---

En résumé, cet article vous a présenté diverses techniques pour séparer les noms et prénoms dans Excel, des méthodes manuelles aux solutions automatisées.

En pratiquant ces techniques, vous deviendrez plus efficace dans la gestion de vos données.

L'automatisation, les formules complexes, et les scripts VBA vous permettront de traiter rapidement et précisément de grandes quantités de données, même lorsqu'elles présentent des formats variés ou des exceptions.

N'hésitez pas à explorer et à adapter ces méthodes pour répondre aux besoins spécifiques de vos projets.