Generische Formel
=RIGHT(domain,LEN(domain)-FIND("*",SUBSTITUTE(domain,".","*",LEN(domain)-LEN(SUBSTITUTE(domain,".","")))))
Zusammenfassung
Um die Top Level Domain (TLD) (dh "com", "net", "org") aus einem Domainnamen oder einer E-Mail-Adresse zu extrahieren, können Sie eine Formel verwenden, die auf mehreren Textfunktionen basiert: MID, RIGHT, FIND, LEN, und ERSATZ. In dem gezeigten Beispiel lautet die Formel in Zelle C5:
=RIGHT(B5,LEN(B5)-FIND("*",SUBSTITUTE(B5,".","*",LEN(B5)-LEN(SUBSTITUTE(B5,".","")))))
Hinweis: In der generischen Form der Formel steht "Domain" für eine Domain oder E-Mail-Adresse wie "www.domain.com".
Erläuterung
Im Beispiel enthält Zelle C5 die folgende Formel:
=RIGHT(B5,LEN(B5)-FIND("*",SUBSTITUTE(B5,".","*",LEN(B5)-LEN(SUBSTITUTE(B5,".","")))))
Im Kern verwendet diese Formel die RECHTS-Funktion, um Zeichen von rechts zu extrahieren. Die anderen Funktionen in dieser Formel machen nur eines: Sie finden heraus, wie viele Zeichen extrahiert werden müssen, n:
=RIGHT(B5,n) // n = ??
Auf hoher Ebene ersetzt die Formel den letzten Punkt "." in der Domäne mit einem Sternchen (*) und verwendet dann die FIND-Funktion, um die Position des Sternchens zu ermitteln. Sobald die Position bekannt ist, kann die RECHTE Funktion die TLD extrahieren.
Woher weiß die Formel, dass nur der letzte Punkt ersetzt wird? Das ist der kluge Teil. Der Schlüssel ist hier:
SUBSTITUTE(B5,".","*",LEN(B5)-LEN(SUBSTITUTE(B5,".","")))
Dieses Snippet ersetzt den letzten Punkt durch ein Sternchen (*).
Der Trick besteht darin, dass die SUBSTITUTE-Funktion ein optionales viertes Argument hat, das angibt, welche "Instanz" des Suchtextes ersetzt werden soll. Wenn nichts angegeben wird, werden alle Instanzen ersetzt. Wenn jedoch beispielsweise die Nummer 2 angegeben wird, wird nur die zweite Instanz ersetzt.
Um herauszufinden, welche Instanz ersetzt werden soll, wird die LEN-Funktion verwendet:
LEN(B5)-LEN(SUBSTITUTE(B5,".",""))
Die Länge der Domäne ohne Punkte wird von der vollen Länge der Domäne abgezogen. Das Ergebnis ist die Anzahl der Punkte in der Domäne.
Im Beispielnamen in B5 befinden sich zwei Punkte in der Domäne, sodass die Nummer 2 wie in der Instanznummer verwendet wird:
SUBSTITUTE(B5," ","*",2)
Dies ersetzt nur den zweiten Punkt durch "*". Der Name sieht dann so aus:
"www.domain * com"
Die FIND-Funktion übernimmt dann, um genau herauszufinden, wo sich das Sternchen im Text befindet:
FIND("*", "www.domain*com")
Das Ergebnis ist 11 (das * befindet sich an der 11. Position), das von der Gesamtlänge der Domäne abgezogen wird:
LEN(B5)-11
Da der Name 15 Zeichen hat, haben wir:
14-11 = 3
Schließlich wird die Nummer 3 von RIGHT wie folgt verwendet:
=RIGHT(B5,3)
Was zu "com" führt
