Office-Blog

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.

Alle Texte mit einem @ Zeichen extrahieren und in einer neue Spalte auflisten, ggf. getrennt mit |

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].

Der Funktion Text.SplitAny die Trennzeichen Semikolon, Komma und Leerzeichen mitgeben

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.

Die Funktion Text.SplitAny erzeugt eine Liste mit allen Worten der Spalte Bemerkung

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.

Die Funktion List.Select wählt in Kombination mit der Funktion Text.Contains alle Texte mit @-Zeichen aus

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

In der Liste jeder Zelle befinden sich jetzt nur noch E-Mail-Adressen

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.

Die Formel erweitern und die Liste der E-Mail-Adressen mit Text.Combine zusammenfügen

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

Fertig: Die neue Spalte mit den E-Mail-Adressen

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.

Die Prüfung auf eine korrekte E-Mail-Adresse beispielsweise erweitern auf das Vorhandensein eines Punktes

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].

Da der Punkt absichtlich nicht als Trennzeichen vorgegeben wurde, kann es zu einem Punkt am Ende einer E-Mail-Adresse kommen

Einen möglichen Punkt am Ende einer E-Mail-Adresse eliminieren.

Und so sieht das Ergebnis dann aus:

Die fertige Lösung, die den Punkt als Satzende im Anschluss an eine E-Mail-Adresse meistert

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.