Generieke formule
=RIGHT(name,LEN(name)-FIND("*",SUBSTITUTE(name," ","*",LEN(name)-LEN(SUBSTITUTE(name," ","")))))
Samenvatting
Als u de achternaam uit een volledige naam moet extraheren, kunt u dat doen met deze nogal complexe formule die verschillende functies gebruikt. In de generieke vorm van de formule (hierboven) is naam een volledige naam, met een spatie tussen de voornaam en andere delen van de naam.
In het voorbeeld bevat de actieve cel deze formule:
=RIGHT(B4,LEN(B4)-FIND("*",SUBSTITUTE(B4," ","*",LEN(B4)-LEN(SUBSTITUTE(B4," ","")))))
Uitleg
In de kern gebruikt deze formule de functie RECHTS om tekens te extraheren die van rechts beginnen. De andere functies waaruit het complexe deel van deze formule bestaat, doen maar één ding: ze berekenen hoeveel tekens er moeten worden geëxtraheerd.
Op een hoog niveau vervangt de formule de laatste spatie in de naam door een asterisk "*" en gebruikt dan FIND om de positie van de asterisk in de naam te bepalen. De positie wordt gebruikt om uit te rekenen hoeveel tekens er met RECHTS moeten worden geëxtraheerd.
Hoe vervangt de functie alleen de laatste spatie? Dit is het slimme deel.
Doe je gordel om, de uitleg wordt een beetje technisch.
De sleutel tot deze formule is dit stukje:
SUBSTITUTE(B4," ","*",LEN(B4)-LEN(SUBSTITUTE(B4," ","")))
Wat doet de daadwerkelijke vervanging van de laatste spatie door "*".
SUBSTITUTE heeft een vierde (optioneel) argument dat specificeert welke "instantie" van de zoektekst moet worden vervangen. Als er niets wordt opgegeven voor dit argument, worden alle instanties vervangen. Als echter bijvoorbeeld het nummer 2 wordt opgegeven, wordt alleen het tweede exemplaar vervangen. In het bovenstaande fragment wordt de instantie berekend met behulp van de tweede SUBSTITUTE:
LEN(B4)-LEN(SUBSTITUTE(B4," ",""))
Hier wordt de lengte van de naam zonder spaties afgetrokken van de werkelijke lengte van de naam. Als de naam maar één spatie bevat, levert dit 1 op. Als er twee spaties zijn, is het resultaat 2, enzovoort.
In de voorbeeldnaam in B4 zijn er twee spaties in de naam, dus we krijgen:
15 - 13 = 2
En twee wordt gebruikt zoals in het instantienummer:
SUBSTITUTE(B4," ","*",2)
die de tweede spatie vervangt door "*". De naam ziet er dan als volgt uit:
"Susan Ann * Chang"
De FIND-functie neemt het vervolgens over om erachter te komen waar de "*" in de naam staat:
FIND("*", "Susan Ann*Chang")
Het resultaat is 10 (de * staat op de 10e positie) die wordt afgetrokken van de totale lengte van de naam:
LEN(B4)-10
Omdat de naam 15 tekens lang is, hebben we:
15-10 = 5
Het cijfer 5 wordt door RIGHT als volgt gebruikt:
=RIGHT(B4,5)
Wat resulteert in "Chang"
Zoals u kunt zien, is het hierboven een hoop werk om die simpele 5 te berekenen!
Omgaan met inconsistente ruimtes
Extra spaties geven problemen met deze formule. Een oplossing is om eerst de TRIM-functie te gebruiken om dingen op te schonen en vervolgens de parseerformule te gebruiken.
