Obecný vzorec
=RIGHT(name,LEN(name)-FIND("*",SUBSTITUTE(name," ","*",LEN(name)-LEN(SUBSTITUTE(name," ","")))))
souhrn
Pokud potřebujete extrahovat příjmení z celého jména, můžete tak učinit pomocí tohoto poměrně složitého vzorce, který používá několik funkcí. V obecné formě vzorce (výše) je name celé jméno s mezerou oddělující křestní jméno od ostatních částí názvu.
V příkladu obsahuje aktivní buňka tento vzorec:
=RIGHT(B4,LEN(B4)-FIND("*",SUBSTITUTE(B4," ","*",LEN(B4)-LEN(SUBSTITUTE(B4," ","")))))
Vysvětlení
Tento vzorec v jádru používá funkci PRÁVA k extrakci znaků začínajících zprava. Ostatní funkce, které tvoří složitou část tohoto vzorce, dělají jen jednu věc: vypočítají, kolik znaků je třeba extrahovat.
Na vyšší úrovni nahradí vzorec poslední mezeru v názvu hvězdičkou „*“ a poté pomocí funkce FIND určí polohu hvězdičky v názvu. Pozice se používá k výpočtu počtu znaků, které se mají extrahovat pomocí PRAVÉHO.
Jak funkce nahradí pouze poslední mezeru? Toto je chytrá část.
Připoutejte se, vysvětlení bude trochu technické.
Klíčem k tomuto vzorci je tento bit:
SUBSTITUTE(B4," ","*",LEN(B4)-LEN(SUBSTITUTE(B4," ","")))
Což znamená skutečné nahrazení posledního prostoru znakem „*“.
SUBSTITUTE má čtvrtý (volitelný) argument, který určuje, která „instance“ textu hledání by měla být nahrazena. Pokud pro tento argument není zadáno nic, jsou nahrazeny všechny instance. Pokud je však zadáno číslo 2, nahradí se pouze druhá instance. Ve fragmentu výše se instance počítá pomocí druhého SUBSTITUTE:
LEN(B4)-LEN(SUBSTITUTE(B4," ",""))
Zde je délka jména bez mezer odečtena od skutečné délky jména. Pokud je v názvu pouze jedna mezera, vytvoří 1. Pokud jsou dvě mezery, výsledkem je 2 atd.
V příkladu názvu v B4 jsou v názvu dvě mezery, takže dostaneme:
15 - 13 = 2
A dva se používají jako v čísle instance:
SUBSTITUTE(B4," ","*",2)
který nahradí druhý prostor znakem „*“. Název pak vypadá takto:
„Susan Ann * Chang“
Funkce FIND poté převezme zjištění, kde je v názvu znak „*“:
FIND("*", "Susan Ann*Chang")
Výsledkem je 10 (* je na 10. pozici), které se odečte od celkové délky jména:
LEN(B4)-10
Jelikož název má 15 znaků, máme:
15-10 = 5
Číslo 5 používá RIGHT takto:
=RIGHT(B4,5)
Což vede k výrazu „Chang“
Jak vidíte, výše uvedená spousta práce spočítat tuto jednoduchou 5 je hodně práce!
Manipulace s nekonzistentními mezerami
Extra mezery způsobí problémy s tímto vzorcem. Jedním z řešení je nejprve použít funkci TRIM k vyčištění věcí a poté použít vzorec pro analýzu.
