Séparer les noms des prénoms avec une formule Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Séparer le nom du prénom

Avec cette nouvelle astuce Excel, nous allons apprendre à travailler les chaînes de caractères de manière à détacher les noms des prénoms et ce, à l'aide d'une seule formule matricielle à répliquer sur toutes les lignes du tableau. C'est la différence de casse des noms inscrits en majuscules qui doit permettre ce raisonnement matriciel de stopper son analyse pour déterminer la position de l'espace de séparation.

Classeur source
Pour la mise en place des démonstrations, nous proposons d'appuyer l'étude sur un classeur offrant des prénoms et noms rassemblés.

• Télécharger le classeur detacher-nom-et-prenom.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Deux tableaux sont présents sur l'unique feuille de ce classeur.



Le premier tableau est constitué d'une colonne. Il liste des clients avec pour chacun le prénom suivi du nom. Les noms sont effectivement inscrits en majuscules. Et comme vous pouvez le voir, certains prénoms et noms sont composés. Les deux colonnes du second tableau attendent les informations détachées sur le prénom et sur le nom.

Détacher le prénom du nom
Pour débuter, nous souhaitons donc isoler le prénom de chaque nom en colonne E. La formule doit donc prélever le texte en partant du début de la chaîne de caractères. Pour cela, il existe la fonction Excel Gauche. Les lettres doivent être analysées de façon récursive sur une certaine longueur. Et pour chaque groupe, nous devons les comparer avec leur équivalent en majuscule (Fonction Majuscule). Si la correspondance exacte (Fonction Exact) est avérée, nous saurons que nous avons atteint le nom. Nous en déduirons donc la position de fin (Fonction Equiv) pour le prélèvement du prénom.

• Sélectionner la case du premier prénom à trouver en cliquant sur la cellule E4,
• Taper le symbole égal (=) pour initier la syntaxe de la formule matricielle,
• Inscrire la fonction pour prélever du début, suivie d'une parenthèse, soit : Gauche(,
• Désigner le texte à tronquer en cliquant sur le premier nom en cellule C4,
• Taper un point-virgule (;) pour passer dans l'argument de la longueur à prélever,

Cette longueur dépend de la position à partir de laquelle une suite de caractères en majuscules est décelée. Et pour trouver cette position, nous devons employer la fonction Excel Equiv.

• Inscrire la fonction de recherche de position, suivie d'une parenthèse, soit : Equiv(,

Dans ce raisonnement matriciel, en guise d'élément cherché, nous devons lui passer une valeur booléenne (Vrai). Car si ce critère est rencontré, nous saurons que nous aurons atteint les lettres en majuscules du nom.

• Inscrire le booléen Vrai,
• Puis, taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,

Ce tableau de recherche ne doit être rien d'autre qu'un tableau des lettres parcourues les unes après les autres. Pour cela, nous allons exploiter la fonction de découpe Stxt sur une certaine longueur.

• Inscrire tout d'abord la fonction Exact suivie d'une parenthèse, soit : Exact(,
• Puis, inscrire la fonction de découpe suivie d'une parenthèse, soit : Stxt(,
• Désigner le texte à prélever en cliquant de nouveau sur la cellule C4,
• Taper un point-virgule (;) pour passer dans l'argument de la position de départ,

Comme la chaîne doit être analysée de façon récursive jusqu'à trouver la présence du nom, nous devons faire varier cette position de découpe, en débutant à partir du premier caractère. Mais comme nous ne connaissons pas la longueur du texte à analyser, nous allons construire une matrice virtuelle de chiffres suffisamment grande.

• Inscrire la matrice virtuelle suivante : Ligne($1:$255),

La fonction Ligne embarquée dans ce raisonnement matriciel va d'abord retourner l'indice 1 puis le 2 et le 3 etc... Les dollars devant les indices numériques sont importants. Nous répliquerons ensuite la formule matricielle sur les lignes du dessous. Ces bornes ne doivent pas être incrémentées avec le déplacement, donc nous les figeons.

• Taper un point-virgule suivi du chiffre 3, soit : ;3, pour la longueur de découpe,

Cette longueur est arbitraire mais nous partons du principe qu'un nom comme un prénom ne peut pas être composé de moins de trois lettres.

• Fermer alors la parenthèse de la fonction Stxt,
• Taper un point-virgule (;) pour passer dans le second argument de la fonction Exact,

Nous devons lui indiquer le texte à comparer avec celui découpé et passé en premier argument. Ce texte est le même mais à forcer en majuscules pour savoir si nous avons atteint le nom. C'est donc la même construction sur la base de la fonction Stxt qui doit être imbriquée dans la fonction Majuscule.

• Inscrire la fonction de casse suivie d'une parenthèse, soit : Majuscule(,
• Dès lors, reproduire la construction de la fonction Stxt : Stxt(C4;Ligne($1:$255);3),
• Fermer la parenthèse de la fonction Majuscule,
• Puis, fermer la parenthèse de la fonction Exact,

De fait, nous sommes de retour dans les bornes de la fonction Equiv. Si cette fonction Exact trouve deux fragments strictement identiques, soit une suite de lettres effectivement en majuscules, elle retourne le booléen Vrai. Ce booléen recoupe la valeur cherchée par la fonction Equiv qui elle va de fait retourner la position dans la chaîne à partir de laquelle cette découverte est un succès.

• Taper un point-virgule suivi du chiffre zéro : ;0 pour réaliser une recherche exacte,
• Fermer la parenthèse de la fonction Equiv,
• Puis, fermer la parenthèse de la fonction Gauche,
• Enfin, valider nécessairement la formule matricielle par le raccourci CTRL + MAJ + Entrée,

Le premier résultat tombe et il s'agit bien du premier prénom séparé de son nom.

• Cliquer et glisser la poignée de la cellule jusqu'en bas du tableau, soit jusqu'à E15,

Comme vous pouvez l'apprécier, une seule formule a permis d'isoler tous les prénoms malgré la présence de certaines constructions composées :

{=GAUCHE(C4; EQUIV(VRAI; EXACT(STXT(C4; LIGNE($1:$255); 3); MAJUSCULE(STXT(C4; LIGNE($1:$255); 3))); 0))}



Et dans cette syntaxe qui peut paraître relativement complexe, la construction la plus longue autour de la fonction Stxt est répétée à deux reprises. Le mécanisme reste donc tout à fait abordable.

Détacher le nom du prénom
Désormais, pour isoler le nom du prénom, puisque le prénom a déjà été identifié, une méthode triviale existe. Elle consiste à exploiter la fonction Excel Substitue pour remplacer le prénom par un texte vide dans la chaîne de départ. Ainsi, seul le nom subsistera.

• Cliquer sur la cellule F4 pour la sélectionner,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire la fonction de remplacement suivie d'une parenthèse, soit : Substitue(,
• Désigner le texte à épurer en cliquant sur la cellule C4 du premier client,
• Taper un point-virgule (;) pour passer dans l'argument du texte à remplacer,
• Désigner le prénom à supprimer en cliquant sur sa cellule E4,
• Taper un point-virgule (;) pour passer dans l'argument du texte de remplacement,
• Inscrire deux guillemets ("") pour supprimer le prénom de la chaîne,
• Fermer la parenthèse de la fonction Substitue,
• Puis, valider la formule avec le raccourci clavier CTRL + Entrée,

Grâce à cette astuce, nous gardons active la cellule du résultat pour l'exploiter dans l'enchaînement. Le premier nom est effectivement restitué indépendamment de son prénom.

• Double cliquer sur la poignée de la cellule pour répliquer la logique sur tout le tableau,

Sachez néanmoins que s'il était question d'isoler le nom du prénom d'un premier jet, un raisonnement matriciel est tout à fait possible. Il emprunte d'ailleurs grandement la syntaxe de la précédente formule matricielle. Mais il doit utiliser la fonction Stxt à la place de la fonction Gauche. En effet, il n'est plus question de prélever à partir du début de la chaîne mais à partir de la position de la présence trouvée pour le nom :

{=STXT(C4; EQUIV(VRAI; EXACT(STXT(C4; LIGNE($1:$255); 3); MAJUSCULE(STXT(C4; LIGNE($1:$255); 3))); 0)+1; 200)}

L'incrémentation d'une unité après la fonction Equiv consiste à sauter l'espace séparant le nom du prénom. Notez de même le dernier argument fixé à la longueur 200. Nous prévoyons suffisamment large dans le cas de noms très longs. En cas de dépassement de longueur, la fonction Stxt ajuste le prélèvement sur la fin de la chaîne.

Et si un doute vous taraudait encore, vous pouvez remplacer les tirets des prénoms composés par un espace pour constater que les résultats sont exactement les mêmes. C'est une vraie différenciation qui est réalisée entre les prénoms et les noms. Nous ne nous contentons pas de trouver le premier séparateur pour définir les textes à isoler, comme nous l'avions envisagé dans une formation passée.