Få efternavn fra navn

Hvis du har brug for at udtrække efternavnet fra et fuldt navn, kan du gøre det med denne ret komplekse formel, der bruger flere funktioner. I den generiske form af formlen (ovenfor) er navnet et fuldt navn med et mellemrum, der adskiller fornavnet fra andre dele af navnet.

I eksemplet indeholder den aktive celle denne formel:

slå rulle-lås-vinduer fra 10

= RIGHT (name, LEN (name)- FIND ('*', SUBSTITUTE (name,' ','*', LEN (name)- LEN ( SUBSTITUTE (name,' ','')))))

I kernen bruger denne formel RIGHT -funktionen til at udtrække tegn, der starter fra højre. De andre funktioner, der udgør den komplekse del af denne formel, gør bare én ting: de beregner, hvor mange tegn der skal udtrækkes.

På et højt niveau erstatter formlen det sidste mellemrum i navnet med en stjerne '*' og bruger derefter FIND til at bestemme placeringen af ​​stjernen i navnet. Positionen bruges til at regne ud, hvor mange tegn der skal udtrækkes med HØJRE.

Hvordan erstatter funktionen kun det sidste mellemrum? Dette er den smarte del.

Spænd op, forklaringen bliver lidt teknisk.

De nøglen til denne formel er denne bit:

 
= RIGHT (B4, LEN (B4)- FIND ('*', SUBSTITUTE (B4,' ','*', LEN (B4)- LEN ( SUBSTITUTE (B4,' ','')))))

Hvilket gør den faktiske udskiftning af det sidste mellemrum med '*'.

SUBSTITUTE har et fjerde (valgfrit) argument, der angiver, hvilken 'forekomst' af søgeteksten skal udskiftes. Hvis der ikke leveres noget til dette argument, erstattes alle instanser. Men hvis f.eks. Tallet 2 er angivet, udskiftes kun den anden instans. I kodestykket ovenfor beregnes forekomsten ved hjælp af den anden SUBSTITUTE:

 
 SUBSTITUTE (B4,' ','*', LEN (B4)- LEN ( SUBSTITUTE (B4,' ','')))

Her trækkes navnets længde uden mellemrum fra navnet på den faktiske længde. Hvis der kun er et mellemrum i navnet, producerer det 1. Hvis der er to mellemrum, er resultatet 2 og så videre.

I eksempelnavnet i B4 er der to mellemrum i navnet, så vi får:

15 - 13 = 2

Og to bruges som i forekomstnummeret:

 
 LEN (B4)- LEN ( SUBSTITUTE (B4,' ',''))

som erstatter det andet mellemrum med '*'. Navnet ser derefter sådan ud:

'Susan Ann * Chang'

FIND -funktionen overtager derefter for at finde ud af, hvor '*' er i navnet:

 
 SUBSTITUTE (B4,' ','*',2)

Resultatet er 10 ( * er i 10. position), som trækkes fra den samlede længde af navnet:

 
 FIND ('*', 'Susan Ann*Chang')

Da navnet er 15 tegn, har vi:

15-10 = 5

Tallet 5 bruges af RIGHT sådan:

 
 LEN (B4)-10

Hvilket resulterer i 'Chang'

hvordan man viser ligninger i excel

Som du kan se, er det meget arbejde ovenfor at beregne den simple 5!

Håndtering af inkonsekvente rum

Ekstra mellemrum vil forårsage problemer med denne formel. En løsning er at bruge TRIM funktion først for at rydde op, og derefter bruge parsingsformlen.