Formuła ogólna
=RIGHT(name,LEN(name)-FIND("*",SUBSTITUTE(name," ","*",LEN(name)-LEN(SUBSTITUTE(name," ","")))))
Podsumowanie
Jeśli potrzebujesz wyodrębnić nazwisko z pełnego imienia i nazwiska, możesz to zrobić za pomocą tej dość złożonej formuły, która wykorzystuje kilka funkcji. W ogólnej postaci wzoru (powyżej) imię to pełne imię i nazwisko, ze spacją oddzielającą imię od pozostałych części.
W przykładzie aktywna komórka zawiera następującą formułę:
=RIGHT(B4,LEN(B4)-FIND("*",SUBSTITUTE(B4," ","*",LEN(B4)-LEN(SUBSTITUTE(B4," ","")))))
Wyjaśnienie
Zasadniczo ta formuła wykorzystuje funkcję PRAWO do wyodrębniania znaków zaczynających się od prawej strony. Inne funkcje, które składają się na złożoną część tej formuły, robią tylko jedną rzecz: obliczają, ile znaków należy wyodrębnić.
Na wysokim poziomie formuła zastępuje ostatnią spację w nazwie gwiazdką „*”, a następnie używa funkcji ZNAJDŹ, aby określić pozycję gwiazdki w nazwie. Pozycja służy do obliczenia liczby znaków do wyodrębnienia za pomocą PRAWEGO.
W jaki sposób funkcja zastępuje tylko ostatnią spację? To jest sprytna część.
Zapnij pasy, wyjaśnienie staje się nieco techniczne.
Kluczem do tej formuły jest ten bit:
SUBSTITUTE(B4," ","*",LEN(B4)-LEN(SUBSTITUTE(B4," ","")))
Co oznacza faktyczną zamianę ostatniej spacji na „*”.
SUBSTITUTE ma czwarty (opcjonalny) argument, który określa, które „wystąpienie” znalezionego tekstu powinno zostać zastąpione. Jeśli nic nie zostanie podane dla tego argumentu, zostaną zastąpione wszystkie wystąpienia. Jeśli jednak podano, powiedzmy, liczbę 2, zastępowana jest tylko druga instancja. W powyższym fragmencie wystąpienie jest obliczane przy użyciu drugiego SUBSTITUTE:
LEN(B4)-LEN(SUBSTITUTE(B4," ",""))
W tym przypadku długość nazwy bez spacji jest odejmowana od faktycznej długości nazwy. Jeśli w nazwie jest tylko jedna spacja, daje 1. Jeśli są dwie spacje, wynikiem jest 2 i tak dalej.
W przykładowej nazwie w B4 w nazwie są dwie spacje, więc otrzymujemy:
15 - 13 = 2
A dwa są używane jak w numerze instancji:
SUBSTITUTE(B4," ","*",2)
która zastępuje drugą spację znakiem „*”. Nazwa wygląda wtedy następująco:
„Susan Ann * Chang”
Następnie funkcja ZNAJDŹ przejmuje kontrolę nad określeniem, gdzie w nazwie znajduje się znak „*”:
FIND("*", "Susan Ann*Chang")
Wynik to 10 (* jest na dziesiątej pozycji), które odejmuje się od całkowitej długości nazwy:
LEN(B4)-10
Ponieważ nazwa ma 15 znaków, mamy:
15-10 = 5
Liczba 5 jest używana przez PRAWO w ten sposób:
=RIGHT(B4,5)
Co powoduje wyrażenie „Chang”
Jak widać, obliczenie tak prostej liczby 5 wymaga dużo pracy!
Obsługa niespójnych przestrzeni
Dodatkowe spacje spowodują problemy z tą formułą. Jednym z rozwiązań jest użycie najpierw funkcji TRIM do wyczyszczenia, a następnie użycie formuły analizy.
