PQ: Zahl aus Text extrahieren / separieren

Foren-FAQ: Zahlen aus Strings extrahieren und summieren

Dieser Beitrag in einem Forum hat mich einfach einmal gereizt. Es sind verschiedene Antworten gegeben worden, ausnahmsweise ist mein Beitrag zum Thema nur an dieser Stelle, also hier im Blog. Der Grund dafür liegt in einer „Macke“ von mir begründet und soll hier nicht diskutiert werden.

Mir war klar, dass solch eine Aufgabe auf verschiedene Weisen gelöst werden kann. Mit VBA hatte ich den Ablauf klar vor Augen, wenn meine Idee auch eher in einen anderen Vergleich (als im Beitrag im Forum) zugrunde gelegt hätte. Ich wollte es aber mit Power Query lösen und versuchte dieses oder jenes, was auf einigen Umwegen dann auch zum Ziel führte. Und bei meiner Recherche „stolperte“ ich dann über diesen Beitrag von Ken Puls und ich erkannte wieder einmal, dass Ken ein wahrer Meister in Sachen PQ ist. Die dort verlinkte Lösung habe ich leicht angepasst für meine Lösung verwendet. Die hauptsächlichen Änderungen sind die unterschiedlichen Namen der Spalten und bei den auszutauschenden Zeichen habe ich statt des Dezimalpunktes das Dezimalkomma aus der Liste ausgelassen und großzügig die ganzen europäischen Sonderzeichen als zu löschende Zeichen mit aufgenommen.

Die angebotene Muster-XLSX aus dem Forum habe ich so verschlankt, dass die fehlerhaften Ergebnisse  sowie die Spalten ab AP entfernt sind; laden Sie das File hier herunter, um die hier diskutierten einzelnen Schritte nachvollziehen zu können.

Sie beginnen natürlich damit, die Muster-Datei in Power Query zu importieren. Wie Sie das erledigen, ist prinzipiell egal, Hauptsache die Daten aus dem Bereich B3:AO13 befinden sich komplett im Power Query-Editor:

Die Daten direkt nach dem Import

Die Daten direkt nach dem Import (nicht alle sind im Screenshot sichtbar)


▲ nach oben …

Liste der zu löschenden Zeichen erstellen

Im nächsten Schritt geht es darum, eine Liste jener Zeichen zu erstellen, die gelöscht werden sollen, damit nur die Zahlen übrig bleiben. Das sind in diesem Fall prinzipiell alle alphanumerischen Zeichen, ausgenommen die Ziffern, das Komma (als Dezimaltrenner) und natürlich das Minus-Zeichen. Leerzeichen belasse ich, falls einmal mehrere Zahlen in einem String enthalten sind und diese dann noch getrennt werden können.

Der einfachste Weg wäre gewiss, eine Tabelle in einem Arbeitsblatt anzulegen und diese beispielsweise über die ZEICHEN() – Funktion auszufüllen. „Einfach“ ja, aber gewiss nicht elegant. 🙄 Da hat Ken Puls mir den wesentlich besseren Weg gewiesen. – Beginnen Sie damit, im Register Start | Neue Quelle | Andere Quellen | Leere Abfrage auszuwählen. In die Eingabezeile geben Sie nun folgende Formel mit dem führenden Gleichheitszeichen (mit oder ohne Zeilenumbruch) ein:
= List.Transform({33..43,46,47,58..126,127..221}, each Character.FromNumber(_))
und bestätigen mit der Eingabe-Taste. Benennen Sie die erstellte Liste mit dem automatisch erstellten Namen Abfrage1 um, damit sie den deutlich sinnvolleren Namen 
ZuLoeschendeZeichen
(idealerweise ohne Leerzeichen) bekommt. Das stellt sich nun so dar:

Die hier aufgeführten Zeichen werden gelöscht

Die hier aufgeführten Zeichen werden im weiteren Verlauf gelöscht

Eine kleine Erklärung bin ich Ihnen vielleicht schuldig: Die Zahlen in der Formel stehen für exakt den Wert, welchen Sie in einem Arbeitsblatt als ZEICHEN() – Funktion verwendet hätten; es sind die ASCII-Nummern der Zeichen. Im Internet können Sie in diversen Tabellen die Zuordnung der numerischen Werte zu den Zeichen nachschlagen.

Idealerweise speichern Sie nun diese Liste nicht als Tabelle sondern nur als Typ Nur Verbindung erstellen. Dazu entweder im Register Start ein Klick auf den Text Schließen &  laden und dann Schließen & laden in… oder Sie gehen über das Register Datei und wählen dort den entsprechenden Menüpunkt direkt aus.

▲ nach oben …

Die Konvertierung

Jetzt könnten Sie direkt dabei gehen und die Werte der einzelnen Spalten so konvertieren, dass nur noch die Zahlen übrig bleiben. Der Weg wäre aber gewiss zu umständlich und aus meiner Sicht auch fehleranfälliger als der nun aufgezeigte Vorgehens-Vorschlag:

  • Wechseln Sie zum Register Spalte hinzufügen.
  • Wählen Sie im Menüband den Punkt Indexspalte.
  • Rechtsklick in die Überschrift Index und im Kontextmenü wählen Sie dann Andere Spalten entpivotieren.
  • Weisen Sie der Spalte Wert den Datentyp Text zu. Das geht entweder via Rechtsklick in die Überschrift oder das Register Start | Datentyp.
  • Wechseln Sie zum Register Spalte hinzufügen und wählen Sie das Symbol Benutzerdefinierte Spalte.
  • Geben Sie hier bei Neuer Spaltenname den Text Werte ein.
  • Bei Benutzerdefinierte Spaltenformel tragen Sie diese Formel ein: 
    = Text.Remove([Wert], ZuLoeschendeZeichen)
    und bestätigen mit OK.
  • Löschen Sie nun die Spalte Wert (die, wo noch die Buchstaben enthalten sind).
  • Markieren Sie die Spalte Attribut.
  • Weisen Sie der Spalte Werte den Datentyp Dezimalzahl zu, da es ja immer noch Texte sind (was an der linksbündigen Ausrichtung auch erkennbar ist).
  • Wechseln zu zum Register Transformieren und wählen Sie Pivotieren  aus.
  • Als Wertespalte wählen Sie Werte.
  • Klicken Sie auf Erweiterte Optionen und wählen im Kombinationsfeld die unterste Möglichkeit Nicht aggregieren. Mit OK schließen Sie den Dialog.
  • Löschen Sie nun die erste Spalte Index.
  • Schließen & laden, und die Daten werden in ein neues Tabellenblatt in eine Liste / Tabelle geschrieben.

Damit ist die Aufgabe prinzipiell erfüllt. Sollten Sie die (wirklich) leeren Zellen irritieren oder stören steht es Ihnen natürlich frei, diese beispielsweise direkt nach dem Entpivotieren durch eine 0 zu ersetzen. – In der Excel-Tabelle können Sie nun mit der Tabellenfunktionalität die Summen bilden, oder was Sie sonst noch mit den Zahlen berechnen oder anfangen wollen. – Und ja, Sie könnten auch diesen Schritt in Power Query erledigen lassen…  😎 

▲ nach oben …

Dieser Beitrag wurde unter Allgemein, Verschiedenes, Datentyp anpassen, Foren-Q&A, Power Query, PQ-Formeln (Sprache M), Text-Behandlung abgelegt und mit , , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.