Všeobecný vzorec
=RIGHT(name,LEN(name)-FIND("*",SUBSTITUTE(name," ","*",LEN(name)-LEN(SUBSTITUTE(name," ","")))))
Zhrnutie
Ak potrebujete extrahovať priezvisko z celého mena, môžete to urobiť pomocou tohto pomerne zložitého vzorca, ktorý používa niekoľko funkcií. Vo všeobecnej forme vzorca (vyššie) je meno celé meno s medzerou oddeľujúcou meno od ostatných častí mena.
V príklade obsahuje aktívna bunka tento vzorec:
=RIGHT(B4,LEN(B4)-FIND("*",SUBSTITUTE(B4," ","*",LEN(B4)-LEN(SUBSTITUTE(B4," ","")))))
Vysvetlenie
Tento vzorec používa v jadre funkciu RIGHT na extrakciu znakov začínajúcich sprava. Ostatné funkcie, ktoré tvoria zložitú časť tohto vzorca, robia iba jednu vec: vypočítajú, koľko znakov je potrebné extrahovať.
Na vysokej úrovni nahradí vzorec poslednú medzeru v názve hviezdičkou „*“ a potom pomocou nástroja FIND zistí pozíciu hviezdičky v názve. Pozícia sa používa na zistenie počtu znakov, ktoré sa majú extrahovať pomocou PRÁVA.
Ako funkcia nahradí iba posledný priestor? Toto je šikovná časť.
Pripútajte sa, vysvetlenie bude trochu technické.
Kľúčom k tomuto vzorcu je tento bit:
SUBSTITUTE(B4," ","*",LEN(B4)-LEN(SUBSTITUTE(B4," ","")))
Čo znamená skutočné nahradenie posledného priestoru znakom „*“.
SUBSTITUTE má štvrtý (voliteľný) argument, ktorý určuje, ktorá „inštancia“ hľadaného textu sa má nahradiť. Ak pre tento argument nie je uvedené nič, všetky inštancie sa nahradia. Ak je však zadané číslo 2, nahradí sa iba druhá inštancia. V úryvku vyššie sa inštancia počíta pomocou druhej SUBSTITUTE:
LEN(B4)-LEN(SUBSTITUTE(B4," ",""))
Tu sa dĺžka mena bez medzier odpočíta od skutočnej dĺžky mena. Ak je v názve iba jedna medzera, vytvorí sa 1. Ak sú dve medzery, výsledkom je 2 atď.
V príklade názvu v B4 sú v názve dve medzery, takže dostaneme:
15 - 13 = 2
A dve sa používajú ako v čísle inštancie:
SUBSTITUTE(B4," ","*",2)
ktorý nahrádza druhý priestor znakom „*“. Názov potom vyzerá takto:
„Susan Ann * Chang“
Funkcia FIND potom prevezme zisťovanie, kde je v názve znak „*“:
FIND("*", "Susan Ann*Chang")
Výsledok je 10 (* je na 10. pozícii), ktorá sa odpočíta od celkovej dĺžky mena:
LEN(B4)-10
Keďže názov má 15 znakov, máme:
15-10 = 5
Číslo 5 používa RIGHT takto:
=RIGHT(B4,5)
Čo vedie k výrazu „Chang“
Ako vidíte, vyššie jednoduché počítanie týchto jednoduchých 5 je veľa práce!
Manipulácia s nekonzistentnými priestormi
Extra medzery spôsobia problémy s týmto vzorcom. Jedným z riešení je najskôr použiť funkciu TRIM na vyčistenie vecí a potom použiť vzorec na analýzu.
