Formuła ogólna
=RIGHT(domain,LEN(domain)-FIND("*",SUBSTITUTE(domain,".","*",LEN(domain)-LEN(SUBSTITUTE(domain,".","")))))
Podsumowanie
Aby wyodrębnić domenę najwyższego poziomu (TLD) (tj. „Com”, „net”, „org”) z nazwy domeny lub adresu e-mail, możesz użyć formuły opartej na kilku funkcjach tekstowych: MID, RIGHT, FIND, LEN, i SUBSTITUTE. W przedstawionym przykładzie formuła w komórce C5 to:
=RIGHT(B5,LEN(B5)-FIND("*",SUBSTITUTE(B5,".","*",LEN(B5)-LEN(SUBSTITUTE(B5,".","")))))
Uwaga: w ogólnej postaci formuły „domena” oznacza domenę lub adres e-mail, na przykład „www.domena.com”.
Wyjaśnienie
W tym przykładzie komórka C5 zawiera następującą formułę:
=RIGHT(B5,LEN(B5)-FIND("*",SUBSTITUTE(B5,".","*",LEN(B5)-LEN(SUBSTITUTE(B5,".","")))))
Zasadniczo ta formuła wykorzystuje funkcję PRAWO do wyodrębniania znaków zaczynających się od prawej strony. Pozostałe funkcje w tej formule robią tylko jedną rzecz: ustalają, ile znaków należy wyodrębnić, n:
=RIGHT(B5,n) // n = ??
Na wysokim poziomie formuła zastępuje ostatnią kropkę „”. w domenie oznaczonej gwiazdką (*), a następnie używa funkcji ZNAJDŹ, aby zlokalizować pozycję gwiazdki. Gdy pozycja jest znana, funkcja PRAWA może wyodrębnić TLD.
Skąd formuła ma zastąpić tylko ostatnią kropkę? To jest sprytna część. Klucz jest tutaj:
SUBSTITUTE(B5,".","*",LEN(B5)-LEN(SUBSTITUTE(B5,".","")))
Ten fragment kodu faktycznie zastępuje ostatnią kropkę gwiazdką (*).
Sztuczka polega na tym, że funkcja SUBSTITUTE ma opcjonalny czwarty argument, który określa, które „wystąpienie” znalezionego tekstu powinno zostać zastąpione. Jeśli nic nie zostanie podane, wszystkie wystąpienia zostaną zastąpione. Jeśli jednak podano, powiedzmy, liczbę 2, zastępowana jest tylko druga instancja.
Aby dowiedzieć się, które wystąpienie zamienić, używana jest funkcja LEN:
LEN(B5)-LEN(SUBSTITUTE(B5,".",""))
Długość domeny bez kropek jest odejmowana od pełnej długości domeny. Wynikiem jest liczba kropek w domenie.
W przykładowej nazwie w B5 w domenie są dwie kropki, więc liczba 2 jest używana tak, jak w numerze instancji:
SUBSTITUTE(B5," ","*",2)
Spowoduje to zastąpienie tylko drugiej kropki znakiem „*”. Nazwa wygląda wtedy następująco:
„www.domain * com”
Następnie funkcja ZNAJDŹ przejmuje kontrolę, aby dowiedzieć się, gdzie dokładnie znajduje się gwiazdka w tekście:
FIND("*", "www.domain*com")
Wynik to 11 (* znajduje się na 11. pozycji), które odejmuje się od całkowitej długości domeny:
LEN(B5)-11
Ponieważ nazwa ma 15 znaków, mamy:
14-11 = 3
Wreszcie liczba 3 jest używana przez PRAWO w następujący sposób:
=RIGHT(B5,3)
Które powoduje wyświetlenie „com”
