Formule générique
=RIGHT(name,LEN(name)-FIND("*",SUBSTITUTE(name," ","*",LEN(name)-LEN(SUBSTITUTE(name," ","")))))
Sommaire
Si vous avez besoin d'extraire le nom de famille d'un nom complet, vous pouvez le faire avec cette formule plutôt complexe qui utilise plusieurs fonctions. Dans la forme générique de la formule (ci-dessus), nom est un nom complet, avec un espace séparant le prénom des autres parties du nom.
Dans l'exemple, la cellule active contient cette formule:
=RIGHT(B4,LEN(B4)-FIND("*",SUBSTITUTE(B4," ","*",LEN(B4)-LEN(SUBSTITUTE(B4," ","")))))
Explication
Au fond, cette formule utilise la fonction DROITE pour extraire les caractères en commençant par la droite. Les autres fonctions qui composent la partie complexe de cette formule ne font qu'une chose: elles calculent le nombre de caractères à extraire.
À un niveau élevé, la formule remplace le dernier espace du nom par un astérisque "*", puis utilise FIND pour déterminer la position de l'astérisque dans le nom. La position est utilisée pour calculer le nombre de caractères à extraire avec DROITE.
Comment la fonction remplace-t-elle uniquement le dernier espace? C'est la partie la plus intelligente.
Bouclez votre ceinture, l'explication devient un peu technique.
La clé de cette formule est ce bit:
SUBSTITUTE(B4," ","*",LEN(B4)-LEN(SUBSTITUTE(B4," ","")))
Ce qui remplace le dernier espace par "*".
SUBSTITUTE a un quatrième argument (facultatif) qui spécifie quelle "instance" du texte de recherche doit être remplacée. Si rien n'est fourni pour cet argument, toutes les instances sont remplacées. Cependant, si, par exemple, le numéro 2 est fourni, seule la deuxième instance est remplacée. Dans l'extrait de code ci-dessus, l'instance est calculée à l'aide du deuxième SUBSTITUTE:
LEN(B4)-LEN(SUBSTITUTE(B4," ",""))
Ici, la longueur du nom sans aucun espace est soustraite de la longueur réelle du nom. S'il n'y a qu'un seul espace dans le nom, cela produit 1. S'il y a deux espaces, le résultat est 2, et ainsi de suite.
Dans l'exemple de nom en B4, il y a deux espaces dans le nom, nous obtenons donc:
15 - 13 = 2
Et deux est utilisé comme dans le numéro d'instance:
SUBSTITUTE(B4," ","*",2)
qui remplace le deuxième espace par "*". Le nom ressemble alors à ceci:
«Susan Ann * Chang»
La fonction FIND prend alors le relais pour savoir où se trouve le "*" dans le nom:
FIND("*", "Susan Ann*Chang")
Le résultat est 10 (le * est en 10ème position) qui est soustrait de la longueur totale du nom:
LEN(B4)-10
Le nom étant composé de 15 caractères, nous avons:
15-10 = 5
Le nombre 5 est utilisé par DROITE comme ceci:
=RIGHT(B4,5)
Ce qui aboutit à "Chang"
Comme vous pouvez le voir, il y a beaucoup de travail ci-dessus pour calculer ce simple 5!
Gérer les espaces incohérents
Des espaces supplémentaires poseront des problèmes avec cette formule. Une solution consiste à utiliser d'abord la fonction TRIM pour nettoyer les choses, puis à utiliser la formule d'analyse.
