Domain Bestandteil aus Domain entfernen (3rd Level TLD), um die TLD zu erhalten

[thomasv]

Hi,
ich bin hier neu im Forum und melde mich gleich mit einer Frage -
In der Rubrik "Wichtig: Excel-Tipps für Domainer" habe ich zum "Problem" - Entfernen des Namenbestandteils aus einer domain, so dass nur die Endung übrigbleibt diese Formel gefunden:
=TEIL($A1;FINDEN(".";$A1;1)+1;5)
Das funktioniert auch gut, so lange es sich nicht um eine 3rd Level Endung handelt. Aus domain.co.uk wird so co.uk.

Gibt es auch eine Lösung für den Fall, dass man als Ergebnis nur die TLD, also uk erhalten möchte?

Grüße
Thomas

 

[Freddy]

Hallo Thomas, was ich gerne mache ist Domainlisten in spalten aufteilen, Seperator nehme ich dann den Punkt.
Aber mit den 3rdlevel domains ist das nicht so einfach. Ich sortiere es dann immer und mache die dann nochmals seperat.

 

[M1175]

Hallo Thomas,

so sollte es gehen:

=TEIL(TEIL($A1;FINDEN(".";$A1;1)+1;5);FINDEN(".";TEIL($A1;FINDEN(".";$A1;1)+1;5);1)+1;5)

Grüße Manuel

 

[webplanet]

Hallo Thomas,

so sollte es gehen:

=TEIL(TEIL($A1;FINDEN(".";$A1;1)+1;5);FINDEN(".";TEIL($A1;FINDEN(".";$A1;1)+1;5);1)+1;5)

Grüße Manuel

Ich hatte mal Mathe LK vor 35 Jahren, ich bin da jetzt raus, Formel sieht aber klasse aus.

Gruß Jens
abi.eu

 

[Löwe]

Hi,
ich bin hier neu im Forum und melde mich gleich mit einer Frage -
In der Rubrik "Wichtig: Excel-Tipps für Domainer" habe ich zum "Problem" - Entfernen des Namenbestandteils aus einer domain, so dass nur die Endung übrigbleibt diese Formel gefunden:
=TEIL($A1;FINDEN(".";$A1;1)+1;5)
Das funktioniert auch gut, so lange es sich nicht um eine 3rd Level Endung handelt. Aus domain.co.uk wird so co.uk.

Gibt es auch eine Lösung für den Fall, dass man als Ergebnis nur die TLD, also uk erhalten möchte?

Grüße
Thomas

Auch noch ein Tipp dazu.
Mir ist aufgefallen das die Formel von Manuel nicht funzt falls in der Domainliste auch mal eine nicht Third Level Domain vorkommt.
(Und da es ja nun auch DomainEndungen gibt die (SchauerlicherWeise) etwas länger sind wie die max 5 Zeichen die in der Formel verwendet werden gehe ich mal mit max 99 Zeichen ran, mal schauen für wie lange das ausreicht).

Annahme: Domain in Zelle A1, Formel dahinter in Zelle B1

Formel Aufbau "Erklärung":

TESTNAME.co.uk TESTNAME "=LINKS(A1;FINDEN(".";A1)-1)

So sieht also der Domainnamen aus


TESTNAME.co.uk .co.uk "=TEIL(A1;FINDEN(".";A1);99)

mit diesem wissen kann ich nun die Formel erweitern



TESTNAME.co.uk co.uk "=TEIL(A1;FINDEN(".";A1)+1;99)

1 Zeichen weniger da der User es ja ohne den ersten Dot mag


TESTNAME.co.uk .uk "=TEIL(TEIL(A1;FINDEN(".";A1)+1;99);FINDEN(".";TEIL(A1;FINDEN(".";A1)+1;99));99)

Aha, nun haben wir die Zweite Endung (Third Level (Das Erste Level ist der Namensanteil in der Domain))


=GLÄTTEN(TEIL(TEIL(A1;FINDEN(".";A1)+1;99);FINDEN(".";TEIL(A1;FINDEN(".";A1);99));99))

Wieder mal ein Zeichen weniger, da der User …



FERTIG: (Nö!)

TESTNAME.co.uk uk
"=GLÄTTEN(TEIL(TEIL(A1;FINDEN(".";A1)+1;99);FINDEN(".";TEIL(A1;FINDEN(".";A1)+1;99))+1;99))

(Leerzeichen im Wort FINDEN,oder Allgemein in den Formeln bitte raus machen)

Falls jemand so witzig war nach der Domain noch ein Leerzeichen zu schreiben, haben wir lieber mal alles geglättet (GLÄTTEN = Vordere und Hintere Leerzeichen entfernen).

Ups, ich Dussel habe natürlich vergessen vorher die Domain zu Fragen ob sie auch einen Zweiten Punkt hat, Fragt man nicht vorher und sucht nur nach im ist das Für Excel logischerweise ein Fehler.

Somit sind die Formel dann im Ganzen so aus:

Fertig:

=GLÄTTEN(TEIL(TEIL(A1;FINDEN(".";A1)+1;99);WENN(ISTFEHLER(FINDEN(".";TEIL(A1;FINDEN(".";A1)+1;99))+1);1;FINDEN(".";TEIL(A1;FINDEN(".";A1)+1;99))+1);99))

funzt auch bei nicht third Level Domains und UltraLangen DomainEndungen

ISTFEHLER ist Hundsaltes Excel, in Neuerem kann das Formelwort auch etwas Anders lauten, aber mein privat Pc hat kein Neueres Excel drauf, kann ich aber sonst in der Firma am Montag gerne nachschauen ...

Falls Jemand "Schon" Excel 2016 nutzt, geht das alles auch Ohne Formeln beim Dateneinlesen und vorher aufbereiten über Excel (mach ich beruflich unter Andrem).

Klar gibt es bei Excel immer sehr viele Lösungen, auch bei den Formeln, um an das Ziel zu kommen, ab 10.000 Zeilen müsste ich die Formel nochmal überdenken,zwecks Performanse.

"@Löwe <nach Korrektur2>

 

[webplanet]

Auch noch ein Tipp dazu.
Mir ist aufgefallen das die Formel von Manuel nicht funzt falls in der Domainliste auch mal eine nicht Third Level Domain vorkommt.
(Und da es ja nun auch DomainEndungen gibt die (SchauerlicherWeise) etwas länger sind wie die max 5 Zeichen die in der Formel verwendet werden gehe ich mal mit max 99 Zeichen ran, mal schauen für wie lange das ausreicht).

Annahme: Domain in Zelle A1, Formel dahinter in Zelle B1

Formel Aufbau "Erklärung":

TESTNAME.co.uk TESTNAME "=LINKS(A1;FINDEN(".";A1)-1) So sieht also der Domainnamen aus

TESTNAME.co.uk .co.uk "=TEIL(A1;FINDEN(".";A1);99) mit diesem wissen der Länge kann ich nun die Formel erweitern

TESTNAME.co.uk co.uk "=TEIL(A1;FINDEN(".";A1)+1;99) 1 Zeichen weniger da der User es ja ohne den ersten Dot mag

TESTNAME.co.uk .uk "=RECHTS(TEIL(A1;FINDEN(".";A1)+1;99);FINDEN(".";TEIL(A1;FINDEN(".";A1)+1;99))) Aha, nun haben wir die Zweite Endung (Third Level (Das Erste Level ist der Namensanteil in der Domain))

TESTNAME.co.uk uk "=RECHTS(TEIL(A1;FINDEN(".";A1)+1;99);FINDEN(".";TEIL(A1;FINDEN(".";A1)+1;99))-1) Wieder mal ein Zeichen weniger, da der User …


FERTIG:

TESTNAME.co.uk uk
"=GLÄTTEN(RECHTS(TEIL(A1;FINDEN(".";A1)+1;99);FINDEN(".";TEIL(A1;FINDEN(".";A1)+1;99))-1))

(Leerzeichen im Wort FINDEN bitte rausmachen)

Falls jemand so witzig war nach der Domain noch ein Leerzeichen zu schreiben, haben wir lieber mal alles geglättet (GLÄTTEN = Vordere und Hintere Leerzeichen entfernen).
funzt auch bei nicht Third Level Domains und UltraLangen DomainEndungen

Falls Jemand "Schon" Exel 2016 nutzt, geht das alles auch Ohne Formeln beim Dateneinlesen und vorher aufbereiten über Excel (mach ich beruflich unter Andrem).

Klar gibt es bei Excel immer sehr viele Lösungen, auch bei den Formeln, um an das Ziel zu kommen, ab 10.000 Zeilen müsste ich die Formel nochmal überdenken,zwecks Performanse.

"@Löwe

OK, Setzen 6...Scherz beiseite, 15 Punkte.

Gruß Jens
division.de

 

[Löwe]

äm, Jens Du hast mich Falsch Zitiert, Genauer Gesagt: 3 Minuten zu Früh... ( und dann nochmalige Korrektur2).
War noch am Laut denken (oder in der Firma :hmmmm2

Auch noch ein Tipp dazu.
Mir ist aufgefallen das die Formel von Manuel nicht funzt falls in der Domainliste auch mal eine nicht Third Level Domain vorkommt.
(Und da es ja nun auch DomainEndungen gibt die (SchauerlicherWeise) etwas länger sind wie die max 5 Zeichen die in der Formel verwendet werden gehe ich mal mit max 99 Zeichen ran, mal schauen für wie lange das ausreicht).

Annahme: Domain in Zelle A1, Formel dahinter in Zelle B1

Formel Aufbau "Erklärung":

TESTNAME.co.uk TESTNAME "=LINKS(A1;FINDEN(".";A1)-1)

So sieht also der Domainnamen aus


TESTNAME.co.uk .co.uk "=TEIL(A1;FINDEN(".";A1);99)

mit diesem wissen kann ich nun die Formel erweitern



TESTNAME.co.uk co.uk "=TEIL(A1;FINDEN(".";A1)+1;99)

1 Zeichen weniger da der User es ja ohne den ersten Dot mag


TESTNAME.co.uk .uk "=TEIL(TEIL(A1;FINDEN(".";A1)+1;99);FINDEN(".";TEIL(A1;FINDEN(".";A1)+1;99));99)

Aha, nun haben wir die Zweite Endung (Third Level (Das Erste Level ist der Namensanteil in der Domain))


=GLÄTTEN(TEIL(TEIL(A1;FINDEN(".";A1)+1;99);FINDEN(".";TEIL(A1;FINDEN(".";A1);99));99))

Wieder mal ein Zeichen weniger, da der User …



FERTIG: (Nö!)

TESTNAME.co.uk uk
"=GLÄTTEN(TEIL(TEIL(A1;FINDEN(".";A1)+1;99);FINDEN(".";TEIL(A1;FINDEN(".";A1)+1;99))+1;99))

(Leerzeichen im Wort FINDEN,oder Allgemein in den Formeln bitte raus machen)

Falls jemand so witzig war nach der Domain noch ein Leerzeichen zu schreiben, haben wir lieber mal alles geglättet (GLÄTTEN = Vordere und Hintere Leerzeichen entfernen).

Ups, ich Dussel habe natürlich vergessen vorher die Domain zu Fragen ob sie auch einen Zweiten Punkt hat, Fragt man nicht vorher und sucht nur nach im ist das Für Excel logischerweise ein Fehler.

Somit sind die Formel dann im Ganzen so aus:

Fertig:

=GLÄTTEN(TEIL(TEIL(A1;FINDEN(".";A1)+1;99);WENN(ISTFEHLER(FINDEN(".";TEIL(A1;FINDEN(".";A1)+1;99))+1);1;FINDEN(".";TEIL(A1;FINDEN(".";A1)+1;99))+1);99))

funzt auch bei nicht third Level Domains und UltraLangen DomainEndungen

ISTFEHLER ist Hundsaltes Excel, in Neuerem kann das Formelwort auch etwas Anders lauten, aber mein privat Pc hat kein Neueres Excel drauf, kann ich aber sonst in der Firma am Montag gerne nachschauen ...

Falls Jemand "Schon" Excel 2016 nutzt, geht das alles auch Ohne Formeln beim Dateneinlesen und vorher aufbereiten über Excel (mach ich beruflich unter Andrem).

Klar gibt es bei Excel immer sehr viele Lösungen, auch bei den Formeln, um an das Ziel zu kommen, ab 10.000 Zeilen müsste ich die Formel nochmal überdenken,zwecks Performanse.

"@Löwe <nach Korrektur2>

 

[Freddy]

Sieht fast wie ein mathe-wettbewerb.de aus!

 

[thomasv]

Hi Löwe,
sorry, dass ich mich erst so spät auf Deine und die anderen Beiträge melde!

Vielen Dank für Deine coole Formel und die Erklärungen dazu. Hat mich schwer beeindruckt.

Meine Tests haben aber ein kleines Manko erkannt. Die Formel funzt so lange die Second Level TLD 2 Zeichen hat, also bei CO.UK zum Beispiel (Ergebnis UK).
Sobald diese aber 3 Zeichen hat, wie zum Beispiel bei COM.BR wird im Ergebnis der Punkt mit angezeigt, also .br.
Wäre es möglich, die Formel so zu erweitern, dass sie unabhängig von der Länge der Second Level TLD das Ergebnis ohne Punkt ausgibt?

Viele Grüße
Thomas

 

[Löwe]

Hi,
ich bin hier neu im Forum und melde mich gleich mit einer Frage -
In der Rubrik "Wichtig: Excel-Tipps für Domainer" habe ich zum "Problem" - Entfernen des Namenbestandteils aus einer domain, so dass nur die Endung übrigbleibt diese Formel gefunden:
=TEIL($A1;FINDEN(".";$A1;1)+1;5)
Das funktioniert auch gut, so lange es sich nicht um eine 3rd Level Endung handelt. Aus domain.co.uk wird so co.uk.

Gibt es auch eine Lösung für den Fall, dass man als Ergebnis nur die TLD, also uk erhalten möchte?

Grüße
Thomas

Annahme: Domain in Zelle A1, Formel dahinter in Zelle B1

Formel Aufbau "Erklärung":

TESTNAME.com.br TESTNAME "=LINKS($A1;FINDEN(".";$A1)-1)

Von Links so viele Zeichen von dem Wert in Zelle A1 bis der Punkt im Wert der Zelle A1 gefunden wird (inkl. darum -1 Zeichen). Es soll ja MitMenschen geben die den Namensanteil der Domain wissen wollen (First Level).


TESTNAME.com.br TESTNAME "=GLÄTTEN(LINKS($A1;FINDEN(".";$A1)-1))

Mir GLÄTTEN mal wieder Alles, Falls vor oder/und hinter dem Namen ein Leerzeichen versteckt ist (GLÄTTEN entfernt hintere und vordere Leerzeichen).


TESTNAME.com.br TESTNAME "=WENN($A1<>"";GLÄTTEN(LINKS($A1;FINDEN(".";$A1)-1));"")

Für Menschen die die Formel gerne weiter runter ziehen wie z. B in der Ersten Spalte "Werte" stehen; versucht der mittlere FormelAnteil nur zur rechnen, WENN die Zelle in Spalte A, auf Schwäbisch: it, also nicht das ist zwischen den Anführungszeichen, nämlich: Leer, (<> bedeutet ungleich); Denn dann macht Sie "" (leer).



TESTNAME.com.br com.br "=TEIL($A1;FINDEN(".";$A1)+1;99)

So Sieht Also die DomainEndung aus, das +1 in der Formel bedeutet: nicht ab dem gefunden Ersten Punkt (von links wird immer gesucht für das FINDEN), sondern Ein Zeichen später (Somit wird das Ergebnis ohne den Erstem Punkt ausgewiesen, Also Erst Ein Zeichen später)


TESTNAME.com.br br "=TEIL(TEIL($A1;FINDEN(".";$A1)+1;99);FINDEN(".";TEIL($A1;FINDEN(".";$A1)+1;99))+1;99)

Nun FINDEN Wir, nur in Diesem Anteil, einen Punkt, und weisen den TEIL Erst ab hier aus.


TESTNAME.com.br br "=TEIL(TEIL($A1;FINDEN(".";$A1)+1;99);WENN(ISTFEHLER(FINDEN(".";TEIL($A1;FINDEN(".";$A1)+1;99))+1);1;FINDEN(".";TEIL($A1;FINDEN(".";$A1)+1;99))+1);99)

Da aber Excel es als Fehler (#WERT! ) empfindet nach etwas zu suchen das gar nicht da ist, Fragen Wir es zuerst ob das suchen und damit FINDEN ISTFEHLER; Wenn Ja dann einfach 99 Zeichen ausweisen, ab dem ersten Punkt; sonst ab dem in Diesem TEIL gefundenen Punkt.


TESTNAME.com.br br "=GLÄTTEN(TEIL(TEIL($A1;FINDEN(".";$A1)+1;99);WENN(ISTFEHLER(FINDEN(".";TEIL($A1;FINDEN(".";$A1)+1;99))+1);1;FINDEN(".";TEIL($A1;FINDEN(".";$A1)+1;99))+1);99))

Mir GLÄTTEN mal wieder Alles, …, geglättet (Vordere und hintere Leerzeichen entfernen, falls welche da sind.


FERTIG:

TESTNAME.com.br br "=WENN($A1<>"";GLÄTTEN(TEIL(TEIL($A1;FINDEN(".";$A1)+1;99);WENN(ISTFEHLER(FINDEN(".";TEIL($A1;FINDEN(".";$A1)+1;99))+1);1;FINDEN(".";TEIL($A1;FINDEN(".";$A1)+1;99))+1);99));"")

FormelMitte bitte nur Arbeiten WENN die Zelle davor (in Diesem Beispiel A1) ungleich (<>) leer ("") ist; sonst leer ("").


(falls, beim hier raus kopieren, an Irgendeiner Stelle in einer Formel ein Leerzeichen auftaucht, dieses bitte raus machen)

Mein bug im Ersten posting (Gottseidank heißt das was wir beim "programmieren" tun, nicht Fehler): FINDEN sucht immer von Links, ich habe dann aber von RECHTS mit dem Ergebnis weiter gerechnet und es war sozusagen purer Zufall das da was zuerst glaubhaftes raus gekommen ist.

funzt nun, wie schon gesagt mit schauerlich langen .Domain Endungen, Domain mit unterschiedlich Langen third Level im second Level Bereich und beliebig lange third Level Anteilen, aber auch bei was normalem wie .de, .ch oder .com :Smart:

Wie schon gesagt: ISTFEHLER ist Hundsaltes Excel, in Neuerem kann das Formelwort auch etwas Anders lauten, aber mein privat Pc hat kein Neueres Excel drauf, kann ich aber sonst in der Firma gerne nachschauen …

"@Löwe, (kpl.überprüft habe ich das natürlich wieder nich :flute

Achso: Das "Dollar" vor dem A1, bedeutet, egal in welche hintere Spalte ich die Formel kopiere, nimm immer den Wert aus Spalte A.

:sheep:Eselsbrücke: Lege einen Dollar VOR die Spalte, dann darfst du bleiben .

Thomas, danke für Deinen Hinweis. <Korrektur2>

 

[Freddy]

Ich mag deine Eselsbrücke!

 

[thomasv]

Hi Löwe,
wieder vielen Dank für Deinen Input und Deine neue Formel. Mit den Third Levels´funktioniert es nun tatsächlich tatdellos. Allerdings stimmt das Ergebnis bei den 2nd Levels nun nicht mehr; hier kommt keine Ausgabe; Beispiel:

domainname.com.br br
domainname.co.uk uk
domainname.donezk.ua ua
domainname.br
domain.de
domainname.com.mx mx
domainname.com.br br

Viele Grüße
Thomas

 

[Löwe]

Hi Löwe,
wieder vielen Dank für Deinen Input und Deine neue Formel. Mit den Third Levels´funktioniert es nun tatsächlich tatdellos. Allerdings stimmt das Ergebnis bei den 2nd Levels nun nicht mehr; hier kommt keine Ausgabe; Beispiel:

domainname.com.br br
domainname.co.uk uk
domainname.donezk.ua ua
domainname.br
domain.de
domainname.com.mx mx
domainname.com.br br

Viele Grüße
Thomas

Excel, Letztes Level einer Domain ausweisen:

Annahme: Domain in Zelle A1, Formel Eine Spalte Dahinter in Zelle B1
ISTFEHLER ist Altes Excel, bei Bedarf die Neuere Formelbezeichnung suchen.

=WENN($A1<>"";GLÄTTEN(TEIL(TEIL($A1;FINDEN(".";$A1)+1;99);WENN(ISTFEHLER(FINDEN(".";TEIL($A1;FINDEN(".";$A1)+1;99))+1);1;FINDEN(".";TEIL($A1;FINDEN(".";$A1)+1;99))+1);99));"")

falls beim hier raus kopieren der Formel ein Leerzeichen in der Formel auftaucht, diese entfernen.

Ich hatte an Falscher Stelle eine 99 zuerst in der Formel, bei nicht Third Level soll sie ja ab dem Ersten Zeichen ab dem Punkt alles ausweisen und nicht erst ab dem NeunUndNeunZigsten (Als Länge aber müsste 99 Zeichen für mache DomainEndungen noch ausreichen).

@Löwe

 

[thomasv]

Hi Löwe,
klasse, einwandfreie Arbeit!

Danke für die Lösung!
Thomas