Excel Formel E-Mail auslesen?
Hallo ich habe eine Frage und hoffe mir kann der ein oder andere Excel/Numbers Kenner behilflich sein.
Ich habe eine Spalte mit ganz vielen Zellen wo Texte wie Z.B sowas stehen:
Max Mustermann 16 Jahre max.mustermann@web.de text 123 Nutzername @maxmustermann ...
Ich möchte nun aus diesen Zellen lediglich die E-mail in eine andere Spalte exportieren. Ich hatte mir gedacht mit einer Funktion nach dem @Zeichen zu suchen und dann nach links und rechts alles bis zum Leerzeichen zu kopieren. Das klappt allerdings nicht bei den Zellen wo auch noch ein @benutzername ist (abgesehen davon, dass ich nichtmal den ersten Teil der Formel hinbekommen habe)
Nun habe ich mir gedacht, dass die Formel beinhalten müsste, dass mindestens vor und nach dem @-Zeichen ein Buchstabe sein muss.
Kann mir jemand eine Formel aufstellen die sowas macht?
Als weitere Infos: Die Texte sind immer durcheinander, mal fehlt der Name, mal steht er woanders, mal gibt es zwei Nutzernamen, mal nur einen. Man kann also in der Hinsicht keine feste stelle lokalisieren, wo die E-mail steht.
Vielen Dank schonmal :)
2 Antworten
Kann man denn davon ausgehen, dass die gesuchte Adresse immer das ERSTE Vorkommen von @ beinhaltet oder kann das auch vertauscht sein? Und ist vor dem anderen @ immer ein Leerzeichen?
Gib am besten mal ein paar mehr Beispiele, möglichst verschieden.
OK. Das ist recht heftig, und daher ist auch meine Formel recht heftig geworden.
Teste die bitte mal GANZ ausführlich mit Deinen Werten.
Mit
Max Mustermann 16 Jahre max.mustermann@web.de text 123 Nutzername @maxmustermann ...
klappt sie, aber ich weiß nicht, ob ich alle Eventualitäten berücksichtigt habe.
Falls sie bei bestimmten Einträgen nicht funktioniert, dann kopiere die bitte hier in Deinen Kommentar, dann schaue ich mal, ob ich das noch hinbekomme.
Hier die Formel:
=TEIL(A1;SUCHEN("###";WECHSELN(LINKS(A1;SUCHEN("?@";WECHSELN(A1;" @";"")));" ";"###";LÄNGE(WECHSELN(LINKS(A1;SUCHEN("?@";WECHSELN(A1;" @";"")));" ";" #"))-LÄNGE(LINKS(A1;SUCHEN("?@";WECHSELN(A1;" @";""))))))+1;SUCHEN("##";WECHSELN(A1;" ";"##";LÄNGE(WECHSELN(LINKS(A1;SUCHEN("?@";WECHSELN(A1;" @";"")));" ";" #"))-LÄNGE(LINKS(A1;SUCHEN("?@";WECHSELN(A1;" @";""))))+1))-SUCHEN("###";WECHSELN(LINKS(A1;SUCHEN("?@";WECHSELN(A1;" @";"")));" ";"###";LÄNGE(WECHSELN(LINKS(A1;SUCHEN("?@";WECHSELN(A1;" @";"")));" ";" #"))-LÄNGE(LINKS(A1;SUCHEN("?@";WECHSELN(A1;" @";"")))))))
Ich weiß, ist zu einem Monstrum angewachsen. Wahrscheinlich kann man das noch optimieren, aber funktionieren sollte sie eigentlich.
Oder?
Erstmal Danke für die Formel, Mega, dass du dir so eine Mühe machst!
Ich habe das Ganze bei einem selbst erstellten Beispiel mal getestet, da hat es wunderbar funktioniert. Die Intention war es eigentlich aus social media Profilen, die E-mail rauszufiltern. Dort bekomme ich sowas heraus (Ich wollte nicht irgendwelche Daten hier rein kopieren, deshalb dachte ich es funktioniert auch mit einem selbst erstellten Beispiel)...
Bsp.:
Du bist neugierig?
💗Dann folge mir!
👩🏼Text
👦🏼Text
👭Text
✉️Text: maxmustermann@test.de
Frage Text?
Textr:
💗Text
🥰Text
🌎Text
&Text!🍾
✉️Text: maxmustermann@test.de
🌸 Text, Text & Text 🌸
🌸Text Text Text 💸💸
💌 maxmustermann@test.de
❗️Text
Also so in der Richtung bekomme ich die Profildaten. Nun sind dort halt auch noch Absätze und Emojis drinnen. Kann es sein, dass die Formel deshalb nicht geht oder hab ich sie vielleicht falsch angewendet? Bei mir klappt es bei diesen Beispielen leider nicht.
Danke und Grüße
Es sind 3 Beispiele, je Beispiel ist eine E-Mail enthalten
Und was mir aufgefallen ist, teilweise steht die E-mail am ende, deshalb ist dort manchmal kein Leerzeichen
Also, wenn ich das von hier nach Excel kopiere, dann landet das in mehreren Zellen, was eine ganz andere Problemstellung wäre.
Zeige doch bitte mal konkret, wie das bei Dir in Excel aussieht.
Entweder mit einem Screenshot auf dem mehrere Fälle zu sehen sind, oder - besser - mit einer Beispieldatei, die Du bei einem Uploadservice hochlädst und hier verlinkst.
Das ist exemplarische, gibt noch mehr Beispiele, wenn du welche benötigst ...
Das sollte reichen. Ist aber ein ganz schönes Chaos.
Ich fürchte da kann ich auch nur teilweise helfen. EIN Problem ist, wie Du schon erkannt hast das fehlende Leerzeichen, wenn die Adresse als letzter Teil aufgeführt ist.
DA kann ich helfen.
Schreibe in C3:
=B3&" "
und kopiere das runter.
Dann in D3:
=TEIL(C3;SUCHEN("###";WECHSELN(LINKS(C3;SUCHEN("?@";WECHSELN(C3;" @";"")));" ";"###";LÄNGE(WECHSELN(LINKS(C3;SUCHEN("?@";WECHSELN(C3;" @";"")));" ";" #"))-LÄNGE(LINKS(C3;SUCHEN("?@";WECHSELN(C3;" @";""))))))+1;SUCHEN("##";WECHSELN(C3;" ";"##";LÄNGE(WECHSELN(LINKS(C3;SUCHEN("?@";WECHSELN(C3;" @";"")));" ";" #"))-LÄNGE(LINKS(C3;SUCHEN("?@";WECHSELN(C3;" @";""))))+1))-SUCHEN("###";WECHSELN(LINKS(C3;SUCHEN("?@";WECHSELN(C3;" @";"")));" ";"###";LÄNGE(WECHSELN(LINKS(C3;SUCHEN("?@";WECHSELN(C3;" @";"")));" ";" #"))-LÄNGE(LINKS(C3;SUCHEN("?@";WECHSELN(C3;" @";"")))))))
und runterkopieren.
Damit hast Du schon mal 12 Adressen mehr als ohne das angehängte " ".
Das wären schon mal 31 von 46 Fällen, in denen meine Formel etwas anzeigt.
Teilweise musst Du da noch etwas löschen, das ohne Leerzeichen vor oder hinter der Adresse steht und bei den restlichen 12 mit #WERT! musst Du auch manuell nacharbeiten. Bei einigen ist aber eh keine sinnvolle Adresse enthalten.
Ich fürchte, weiter kann ich Dir bei diesem Chaos nicht helfen.
Wenn das ei Struktur ist
"Max Mustermann 16 Jahre max.mustermann@web.de "
Dann kannst Du durch "Daten - Text in Spalten - Getrennt - Leerzeichen" auflösen und danach die E-Mail-Adressen z.B., ab 5. Spalte auslesen.
Sollte die Struktur anderes sein. Melde ich nochmal
.
Falls Du VBA beherrscht, schau mal hier
Nein leider ist es völlig durcheinander. Also im Endeffekt ein zufälliger Text mit Zahlen, Buchstaben und Symbolen wo zwischendrin eine E-Mail Adresse ist.
Du könntest dennoch mit "'Text in Spalten" die Texte auflösen und über Autofilter aus den jeweiligen Spalten danach die E-Mail-Adressen durch suchen in den jeweiligen Spalten nach "@" die Mal-Adressen in mehreren Kopiervorgängen in eine Hilfsspalte/neue Spalten kopieren.
Danke für den Tipp, ich probiers mal ... Eine Formel wäre aber denke ich effizienter
Klar. Ich vermute mal, dass es nicht allzu viele Spalten betreffen wird.
Für eine VBA-Lösung hatte ich Dir ja einen Link reingestellt. (... ungeprüft)
Viel Erfolg
Nein das kann auch vertauscht sein also es kann @Nutzername und dann E-Mail kommen. Vor dem @ von Nutzernamen ist i.d.R immer ein Leerzeichen.