Generisk formel
=RIGHT(name,LEN(name)-FIND("*",SUBSTITUTE(name," ","*",LEN(name)-LEN(SUBSTITUTE(name," ","")))))
Sammendrag
Hvis du trenger å trekke ut etternavnet fra et fullt navn, kan du gjøre det med denne ganske komplekse formelen som bruker flere funksjoner. I den generiske formen av formelen (over) er navnet et fullt navn, med et mellomrom som skiller fornavnet fra andre deler av navnet.
I eksemplet inneholder den aktive cellen denne formelen:
=RIGHT(B4,LEN(B4)-FIND("*",SUBSTITUTE(B4," ","*",LEN(B4)-LEN(SUBSTITUTE(B4," ","")))))
Forklaring
I kjernen bruker denne formelen RIGHT-funksjonen til å trekke ut tegn fra høyre. De andre funksjonene som utgjør den komplekse delen av denne formelen, gjør bare en ting: de beregner hvor mange tegn som må trekkes ut.
På høyt nivå erstatter formelen den siste plassen i navnet med en stjerne "*" og bruker deretter FINN for å bestemme plasseringen til stjernen i navnet. Posisjonen brukes til å finne ut hvor mange tegn du skal trekke ut med RIGHT.
Hvordan erstatter funksjonen bare den siste plassen? Dette er den smarte delen.
Spenn opp, forklaringen blir litt teknisk.
De er nøkkelen til denne formelen er denne biten:
SUBSTITUTE(B4," ","*",LEN(B4)-LEN(SUBSTITUTE(B4," ","")))
Som gjør den faktiske erstatningen av den siste plassen med "*".
SUBSTITUTE har et fjerde (valgfritt) argument som spesifiserer hvilken "forekomst" av finne-teksten som skal erstattes. Hvis ingenting er gitt for dette argumentet, erstattes alle forekomster. Imidlertid, hvis, si tallet 2, blir bare den andre forekomsten erstattet. I utdraget ovenfor beregnes forekomsten ved hjelp av den andre SUBSTITUTE:
LEN(B4)-LEN(SUBSTITUTE(B4," ",""))
Her trekkes lengden på navnet uten mellomrom fra den faktiske lengden på navnet. Hvis det bare er ett mellomrom i navnet, produserer det 1. Hvis det er to mellomrom, er resultatet 2, og så videre.
I eksempelnavnet i B4 er det to mellomrom i navnet, så vi får:
15 - 13 = 2
Og to brukes som i forekomstnummeret:
SUBSTITUTE(B4," ","*",2)
som erstatter den andre plassen med "*". Navnet ser da slik ut:
"Susan Ann * Chang"
FINN-funksjonen tar deretter over for å finne ut hvor "*" er i navnet:
FIND("*", "Susan Ann*Chang")
Resultatet er 10 (* er i 10. posisjon) som trekkes fra den totale lengden på navnet:
LEN(B4)-10
Siden navnet er 15 tegn, har vi:
15-10 = 5
Nummeret 5 brukes av RIGHT slik:
=RIGHT(B4,5)
Som resulterer i "Chang"
Som du kan se, er det mye arbeid ovenfor for å beregne den enkle 5!
Håndtering av inkonsekvente mellomrom
Ekstra mellomrom vil føre til problemer med denne formelen. En løsning er å bruke TRIM-funksjonen først til å rydde opp i ting, og deretter bruke analyseringsformelen.
