Formule générique
=MID(A1,FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1))+1,100)
Sommaire
Pour extraire les deux derniers mots d'une cellule, vous pouvez utiliser une formule construite avec plusieurs fonctions Excel, notamment MID, FIND, SUBSTITUTE et LEN. Dans l'exemple illustré, la formule en C5 est:
=MID(B5,FIND("@",SUBSTITUTE(B5," ","@",LEN(B5)-LEN(SUBSTITUTE(B5," ",""))-1))+1,100)
Explication
Au fond, cette formule utilise la fonction MID pour extraire les caractères à partir de l'avant-dernier espace. La fonction MID prend 3 arguments: le texte avec lequel travailler, la position de départ et le nombre de caractères à extraire.
Le texte provient de la colonne B et le nombre de caractères peut être n'importe quel grand nombre qui garantira que les deux derniers mots sont extraits. Le défi est de déterminer la position de départ, qui est juste après l'avant-dernier espace. Le travail intelligent se fait principalement avec la fonction SUBSTITUTE, qui a un argument facultatif appelé numéro d'instance. Cette fonction permet de remplacer l'avant-dernier espace du texte par le caractère «@», qui est alors localisé avec la fonction FIND.
En travaillant de l'intérieur vers l'extérieur, l'extrait ci-dessous indique le nombre d'espaces dans le texte total, dont 1 est soustrait.
LEN(B5)-LEN(SUBSTITUTE(B5," ",""))-1
Dans l'exemple illustré, il y a 5 espaces dans le texte, donc le code ci-dessus renvoie 4. Ce numéro est introduit dans la fonction SUBSTITUTE externe comme numéro d'instance:
SUBSTITUTE(B5," ","@",4)
Cela provoque SUBSTITUTE pour remplacer le quatrième caractère d'espace par "@". Le choix de @ est arbitraire. Vous pouvez utiliser n'importe quel caractère qui n'apparaîtra pas dans le texte d'origine.
Ensuite, le FIND localise le caractère "@" dans le texte:
FIND("@","A stitch in time@saves nine")
Le résultat de FIND est 17, auquel 1 est ajouté pour obtenir 18. Ceci est la position de départ, et entre dans la fonction MID comme deuxième argument. Pour plus de simplicité, le nombre de caractères à extraire est codé en dur comme 100. Ce nombre est arbitraire et peut être ajusté pour s'adapter à la situation.
Extraire les N derniers mots de la cellule
Cette formule peut être généralisée pour extraire les N derniers mots d'une cellule en remplaçant le 1 codé en dur dans l'exemple par (N-1). En outre, si vous extrayez de nombreux mots, vous souhaiterez peut-être remplacer l'argument codé en dur dans MID, 100, par un plus grand nombre. Pour vous garantir que le nombre est suffisamment grand, vous pouvez simplement utiliser la fonction LEN comme suit:
=MID(B5,FIND("@",SUBSTITUTE(B5," ","@",LEN(B5)-LEN(SUBSTITUTE(B5," ",""))-(N-1)))+1,LEN(B5))
