Generisk formel
=RIGHT(name,LEN(name)-FIND("*",SUBSTITUTE(name," ","*",LEN(name)-LEN(SUBSTITUTE(name," ","")))))
Sammanfattning
Om du behöver extrahera efternamnet från ett fullständigt namn kan du göra det med denna ganska komplexa formel som använder flera funktioner. I den generiska formen av formeln (ovan) är namnet ett fullständigt namn, med ett mellanslag som skiljer förnamnet från andra delar av namnet.
I exemplet innehåller den aktiva cellen följande formel:
=RIGHT(B4,LEN(B4)-FIND("*",SUBSTITUTE(B4," ","*",LEN(B4)-LEN(SUBSTITUTE(B4," ","")))))
Förklaring
I grunden använder denna formel RIGHT-funktionen för att extrahera tecken som börjar från höger. De andra funktionerna som utgör den komplexa delen av denna formel gör bara en sak: de beräknar hur många tecken som behöver extraheras.
På hög nivå ersätter formeln det sista utrymmet i namnet med en asterisk "*" och använder sedan HITTA för att bestämma asteriskens position i namnet. Positionen används för att räkna ut hur många tecken som ska extraheras med RIGHT.
Hur ersätter funktionen bara det sista utrymmet? Det här är den smarta delen.
Spänn upp, förklaringen blir lite teknisk.
De är nyckeln till denna formel är den här biten:
SUBSTITUTE(B4," ","*",LEN(B4)-LEN(SUBSTITUTE(B4," ","")))
Vilket gör det faktiska utbytet av det sista utrymmet med "*".
SUBSTITUTE har ett fjärde (valfritt) argument som anger vilken "instans" av söktexten som ska ersättas. Om inget tillhandahålls för detta argument byts alla instanser ut. Men om, säg att siffran 2 tillhandahålls, ersätts endast den andra instansen. I kodavsnittet ovan beräknas instans med den andra SUBSTITUTE:
LEN(B4)-LEN(SUBSTITUTE(B4," ",""))
Här subtraheras längden på namnet utan några mellanslag från den faktiska längden på namnet. Om det bara finns ett mellanslag i namnet producerar det 1. Om det finns två mellanslag blir resultatet 2 och så vidare.
I exemplets namn i B4 finns det två mellanslag i namnet, så vi får:
15 - 13 = 2
Och två används som i instansnumret:
SUBSTITUTE(B4," ","*",2)
som ersätter det andra mellanslaget med "*". Namnet ser då ut så här:
"Susan Ann * Chang"
HITTA-funktionen tar sedan över för att ta reda på var "*" är i namnet:
FIND("*", "Susan Ann*Chang")
Resultatet är 10 (* är i 10: e position) som subtraheras från den totala längden på namnet:
LEN(B4)-10
Eftersom namnet är 15 tecken har vi:
15-10 = 5
Siffran 5 används av RIGHT så:
=RIGHT(B4,5)
Vilket resulterar i "Chang"
Som du kan se är det mycket arbete ovan för att beräkna den enkla 5!
Hantering av inkonsekventa utrymmen
Extra mellanslag kommer att orsaka problem med denna formel. En lösning är att först använda TRIM-funktionen för att städa upp saker och sedan använda analyseringsformeln.
