Obecný vzorec
=RIGHT(domain,LEN(domain)-FIND("*",SUBSTITUTE(domain,".","*",LEN(domain)-LEN(SUBSTITUTE(domain,".","")))))
souhrn
Chcete-li z domény nebo e-mailové adresy extrahovat doménu nejvyšší úrovně (TLD) (tj. „Com“, „net“, „org“), můžete použít vzorec založený na několika textových funkcích: MID, RIGHT, FIND, LEN, a NÁHRADA. V zobrazeném příkladu je vzorec v buňce C5:
=RIGHT(B5,LEN(B5)-FIND("*",SUBSTITUTE(B5,".","*",LEN(B5)-LEN(SUBSTITUTE(B5,".","")))))
Poznámka: V obecné formě vzorce představuje „doména“ doménu nebo e-mailovou adresu, například „www.domain.com“.
Vysvětlení
V příkladu obsahuje buňka C5 tento vzorec:
=RIGHT(B5,LEN(B5)-FIND("*",SUBSTITUTE(B5,".","*",LEN(B5)-LEN(SUBSTITUTE(B5,".","")))))
Tento vzorec v jádru používá funkci PRÁVA k extrakci znaků začínajících zprava. Ostatní funkce v tomto vzorci dělají jen jednu věc: zjišťují, kolik znaků je třeba extrahovat, n:
=RIGHT(B5,n) // n = ??
Na vysoké úrovni nahradí vzorec poslední tečku "." v doméně s hvězdičkou (*) a poté pomocí funkce FIND vyhledá polohu hvězdičky. Jakmile je pozice známá, funkce RIGHT může extrahovat TLD.
Jak vzorec ví, že nahradí pouze poslední tečku? Toto je chytrá část. Klíč je zde:
SUBSTITUTE(B5,".","*",LEN(B5)-LEN(SUBSTITUTE(B5,".","")))
Tento úryvek provede skutečné nahrazení poslední tečky hvězdičkou (*).
Trik spočívá v tom, že funkce SUBSTITUTE má volitelný čtvrtý argument, který určuje, která "instance" textu hledání by měla být nahrazena. Pokud nic není zadáno, všechny instance jsou nahrazeny. Pokud je však zadáno číslo 2, nahradí se pouze druhá instance.
Chcete-li zjistit, kterou instanci nahradit, používá se funkce LEN:
LEN(B5)-LEN(SUBSTITUTE(B5,".",""))
Délka domény bez jakýchkoli teček se odečte od celé délky domény. Výsledkem je počet teček v doméně.
V názvu příkladu v B5 jsou v doméně dvě tečky, takže číslo 2 se používá jako v čísle instance:
SUBSTITUTE(B5," ","*",2)
Tím se nahradí pouze druhá tečka znakem „*“. Název pak vypadá takto:
„www.domain * com“
Funkce FIND poté převezme výpočet přesné polohy hvězdičky v textu:
FIND("*", "www.domain*com")
Výsledkem je 11 (* je na 11. pozici), které se odečte od celkové délky domény:
LEN(B5)-11
Jelikož název má 15 znaků, máme:
14-11 = 3
Nakonec číslo 3 používá RIGHT takto:
=RIGHT(B5,3)
Výsledkem je „com“
