Yleinen kaava
=RIGHT(name,LEN(name)-FIND("*",SUBSTITUTE(name," ","*",LEN(name)-LEN(SUBSTITUTE(name," ","")))))
Yhteenveto
Jos haluat purkaa sukunimen koko nimestä, voit tehdä sen tällä melko monimutkaisella kaavalla, joka käyttää useita toimintoja. Kaavan (yllä) yleisessä muodossa nimi on koko nimi, jossa välilyönti erottaa etunimen nimen muista osista.
Esimerkissä aktiivinen solu sisältää tämän kaavan:
=RIGHT(B4,LEN(B4)-FIND("*",SUBSTITUTE(B4," ","*",LEN(B4)-LEN(SUBSTITUTE(B4," ","")))))
Selitys
Ytimessä tämä kaava käyttää OIKEA-funktiota poimimaan merkit oikealta alkaen. Muut funktiot, jotka muodostavat tämän kaavan monimutkaisen osan, tekevät vain yhden asian: ne laskevat, kuinka monta merkkiä on purettava.
Korkealla tasolla kaava korvaa nimen viimeisen välilyönnin tähdellä "*" ja määrittää sitten tähden sijainnin nimessä FIND. Sijaintia käytetään selvittämään, kuinka monta merkkiä OIKEA-toiminnolla puretaan.
Kuinka funktio korvaa vain viimeisen välilyönnin? Tämä on fiksu osa.
Solki ylös, selitys on hieman tekninen.
He avaimet tähän kaavaan ovat tämä bitti:
SUBSTITUTE(B4," ","*",LEN(B4)-LEN(SUBSTITUTE(B4," ","")))
Mikä korvaa viimeisen tilan varsinaisella merkillä "*".
SUBSTITUTE: lla on neljäs (valinnainen) argumentti, joka määrittää, mikä hakutekstin "instanssi" tulisi korvata. Jos tälle argumentille ei toimiteta mitään, kaikki esiintymät korvataan. Jos kuitenkin sanotaan, että numero 2 toimitetaan, vain toinen esiintymä korvataan. Yllä olevassa katkelmassa esiintymä lasketaan käyttämällä toista SUBSTITUTE:
LEN(B4)-LEN(SUBSTITUTE(B4," ",""))
Tällöin nimen pituus ilman välilyöntejä vähennetään nimen todellisesta pituudesta. Jos nimessä on vain yksi välilyönti, se tuottaa 1. Jos on kaksi välilyöntiä, tulos on 2 ja niin edelleen.
B4: n esimerkinimessä nimessä on kaksi välilyöntiä, joten saamme:
15 - 13 = 2
Ja kahta käytetään kuten esiintymän numerossa:
SUBSTITUTE(B4," ","*",2)
joka korvaa toisen välilyönnin "*". Nimi näyttää tältä:
"Susan Ann * Chang"
Etsitään sitten funktio selvittääksesi missä "*" on nimessä:
FIND("*", "Susan Ann*Chang")
Tulos on 10 (* on 10. paikassa), joka vähennetään nimen kokonaispituudesta:
LEN(B4)-10
Koska nimi on 15 merkkiä, meillä on:
15-10 = 5
Numeroa 5 käyttää RIGHT kuten:
=RIGHT(B4,5)
Mikä johtaa "Chang"
Kuten näette, edellä on paljon työtä laskettaessa yksinkertainen 5!
Epäjohdonmukaisten tilojen käsittely
Ylimääräiset välilyönnit aiheuttavat ongelmia tämän kaavan kanssa. Yksi ratkaisu on käyttää TRIM-toimintoa ensin asioiden puhdistamiseen ja sitten jäsentämiskaavan käyttämiseen.
