Xtract: Per Power Query Zahlen (Ziffernfolgen) aus einem Text in jeder Zelle der Spalte extrahieren, auch vor Excel 2019⁄365.
Wissensstand: Level 3 ⇒ Excel GUT!, mindestens Basis-Kenntnisse in Power Query
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 (nicht alle sind im Screenshot sichtbar)
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 (wahrscheinlich 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 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.
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… 😎
Ergänzung von Anfang November 2019
Beginnend in Excel 2019 und natürlich in Excel 365 gibt es eine weitere, aus meiner Sicht durchaus elegantere Möglichkeit, das gewünschte Ziel zu erlangen.
Über das Menü Transformieren erweitern Sie das Symbol Textspalte. Dort wählen Sie Spalte teilen und im DropDown Klicken Sie dann in die Auswahl Nach Wechsel von Nicht-Ziffer zu Ziffer. Dadurch werden eine oder mehrere Spalten, jeweils beginnend mit den Ziffern neu erstellt. Diese Zahlen-Spalten können Sie nun noch einmal auf dem gleichen Wege so teilen, dass sie im letzten Schritt Nach Wechsel von Ziffer zu Nicht-Ziffer auswählen. Anschließend werden sie all jene betroffenen Spalten löschen, die nur Text enthalten. Diese Lösungsmöglichkeit hat auch den ausgesprochen großen Vorteil, dass eine Zelle, die mehrere Zahlenfolgen (durch Text getrennt) enthält, „sauber” aufgeteilt wird. 😎
Rückmeldungen / Feedback gerne per Mail an mich (G.Mumme@Excel-ist-sexy.de)
Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?
Dann würde ich mich über einen Beitrag Ihrerseits z.B. 2,50 € freuen … (← Klick mich!)