Power Query: E-Mail-Adressen aus Zeichenketten auslesen

Daten, die in Freitextfelder eingegeben wurden, enthalten oft Details, die gebraucht werden. Beispielsweise E-Mail-Adressen wie in der folgenden Abbildung. Doch wie lassen sich gezielt die E-Mail-Adressen aus diesen Daten auslesen? Wie können solche wertvollen Informationen für Marketingzwecke, Supportanfragen oder Analysen nutzbar gemacht werden?

In meinem Rezept #25 zeige ich, wie in Power Query E-Mail-Adressen aus Bemerkungsfeldern extrahiert werden, indem ich alle Texte mit einem @-Zeichen verarbeite.

1) Text in Freitextfeldern aufteilen mit Text.SplitAny

Zunächst überführe ich den Text der Spalte Bemerkung in eine Liste aus Worten. Als Trennzeichen nutze ich hierbei Leerzeichen, Komma und Semikolon. Anschließend selektiere ich aus dieser Wortliste alle Zeichenketten, die ein @ enthalten. So gehe ich vor:

• In Excel klicke ich in die Tabelle und wähle auf der Registerkarte Daten > Daten abrufen > Aus Tabelle/Bereich.
• Bevor ich mit Textfunktionen arbeite, ändere ich den Datentyp der Spalte Bemerkung zu Text.
• Über Spalte hinzufügen > Benutzerdefinierte Spalte erstelle ich eine neue Spalte mit dem Namen E-Mail-Adressen [1].
• Mit der Funktion Text.SplitAny und unter Angabe der gewünschten Trennzeichen zerlege ich den gesamten Text in eine Liste von Worten [2].

Als Ergebnis erhalte ich in jeder Zelle der neuen Spalte eine Liste mit Wörtern.

Tipp: Klicke ich in eine dieser Zellen (nicht auf das Wort List), kann ich mir unten links das Ergebnis einer einzelnen Zelle ansehen.

2) Aus der Wortliste die Zeichenketten mit @ filtern mit List.Select

Um nur Zeichenketten mit @ zu extrahieren, bearbeite ich die benutzerdefinierte Spalte wie folgt weiter:

• Ich klicke rechts im Bereich Angewendete Schritte auf das Zahnrädchen beim Schritt Hinzugefügte benutzerdefinierte Spalte und wende die Funktion List.Select an.
• Der Funktion List.Select gebe ich als zweites Argument die Bedingung each Text.Contains(_,“@“) mit.
  Damit werden alle Listenelemente mit einem @-Zeichen selektiert.

Als Ergebnis erhalte ich in jeder Zelle wieder eine Liste, diesmal aber nur noch mit den Texten, die ein @-Zeichen enthalten.

3) E-Mail-Adressen zu einem Text zusammenfügen mit Text.Combine

Um nun die Wörter zu einem Text zusammenzufügen, bearbeite ich die benutzerdefinierte Spalte erneut und erweitere den Formeltext:

• Ich klicke im Bereich Angewendete Schritte auf das Zahnrädchen beim Schritt Hinzugefügte benutzerdefinierte Spalte und erweitere die vorhandene Formel am Anfang um die Funktion Combine.
• Dieser gebe ich als zweites Argument das Trennzeichen “ | “ mit.

Das Ergebnis kann sich sehen lassen: Alle vorhandenen E-Mail-Adressen stehen nun |-getrennt in einer neuen Spalte.

Profi-Tipp 1: Weitere Bedingungen für das Selektieren der E-Mail-Adressen einbauen – mindestens ein Punkt

Das hier beschriebene Verfahren extrahiert alle Texte mit @-Zeichen. Allerdings garantiert das nicht die Richtigkeit der E-Mail-Adressen.
Um sicherzustellen, dass nicht nur ein @-Zeichen, sondern beispielsweise auch mindestens ein Punkt im Text vorkommt, erweitere ich die Bedingung von List.Select wie folgt.

Profi-Tipp 2: Punkt am Ende von E-Mail-Adressen bei Bedarf entfernen

Das Leben ist bunt. Daher kann es auch vorkommen, dass im Bemerkungstext ein Punkt NACH einer E-Mail-Adresse steht – als Satzzeichen. Auch für diese Herausforderung gibt es eine Lösung.

• Dazu bearbeite ich die durch List.Select entstandene Liste von Wörtern mit der Funktion List.Transform [1].
• Ich prüfe mit einer if-Anweisung auf einen Punkt am Ende [2] und reduziere gegebenenfalls die E-Mail-Adresse mit Text.Start [3] um ein Zeichen von rechts – berechnet durch Gesamtlänge -1 [4].

Und so sieht das Ergebnis dann aus:

Fazit

• Mit einer Kombination aus Text.SplitAny, List.Select, Text.Combine und Text.Contains lassen sich E-Mail-Adressen aus Zeichenketten herauslösen.
• Allerdings kann diese Technik nicht dafür sorgen, dass aus fehlerhaft eingegebenen anschließend korrekte E-Mail-Adressen werden.