Foren-FAQ: Ziffern aus Texten entfernen
Im Herber-Excel-Forum wurde die Frage gestellt, wie bestimmte Ziffern/Zahlen aus einem Text in diversen Zellen entfernt werden können. Zurzeit, wo ich diesen Beitrag verfasse 1) war eine sinnvolle Antwort gegeben worden. Es ist aber nicht auszuschließen, dass alle Ziffern entfernt werden sollen und nicht nur die im Muster-Text vorgestellten (Stand: 1. Antwort). Und insbesondere wenn es große Datenmengen sind kann eine Lösung per Power Query aus Gründen der Geschwindigkeit sinnvoll sein.
1) Die Veröffentlichung dieses Beitrages ist aus organisatorischen Gründen nicht zum Zeitpunkt des Erscheinens der Frage im Forum erfolgt.
Die PQ-Lösung
Zuvor angemerkt: Diese Thematik ist unter den gegensätzlichen Bedingungen (Zahlen sollen extrahiert werden) hier im Blog sehr ausführlich beschrieben, darum ist das Ganze hier eher stichwortartig dargelegt.
Da im Beitrag auch keine Muster-xlsx angeboten wurde sondern nur 3 Zeilen Text, habe ich die hier einfach einmal übernommen. Sie sind ja leicht per copy/paste in ein Arbeitsblatt eingefügt und dann als Tabelle formatiert:
30Hannes70Schmidt
80Klaus20Franz
10Tim30Hannes60Markus
Sie beginnen natürlich damit, die Muster-Daten (die Tabelle) in Power Query zu importieren. Und Natürlich dürfen Sie noch nach Herzenslust eigene Kreationen hinzufügen. 😎
Im ersten Schritt nach dem Import in den Power Query-Editor soll als endgültiges Ziel eine neuen Spalte erstellt werden, wo die „verschlankten” Zeichenfolgen aus der ersten Spalte geschrieben sind. – Auch wenn es Ihnen vielleicht als erstes in den Sinn kommt, 10 Ersetzungsvorgänge nacheinander anzustoßen, das muss es nicht sein. Auch wenn damit garantiert das Ziel erreicht werden würde.
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 alle Zeichen außer den Zahlen/Ziffern übrig bleiben. Das sind naturgemäß die Ziffern 0 bis 9. Alles andere soll ja erhalten bleiben, einschließlich eventueller Sonderzeichen (wenn auch nicht explizit erwähnt).
Natürlich könnten Sie von Hand eine Excel-Tabelle mit den 10 Ziffern anlegen, diese importieren und dann (als Liste) zur weiteren Auswertung nutzen. Aber es geht um einiges einfacher (manch einer würde wahrscheinlich auch sagen „eleganter”). 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({48..57}, 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 Ziffern (idealerweise kurz und prägnant) bekommt. Das stellt sich nun so dar:
Die durch Power Query erzeugte Liste aller Ziffern
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 Zahlen/Ziffern. Im Internet können Sie in diversen Tabellen die Zuordnung der numerischen Werte zu den Zeichen recherchieren. Wenn Sie in der Windows-Zeichentabelle nachschlagen, werden Sie wahrscheinlich nur die Hex-Werte bekommen, die hier nicht zielführend sind (die Sie aber mit dem integrierten Rechner auch in Dezimale Werte umwandeln können).
Idealerweise speichern Sie nun diese Liste nicht als Tabelle sondern so wie sie ist 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 werden Sie direkt dabei gehen und die Werte der einzelnen Spalten so konvertieren, dass nur noch die Nicht-Zahlen übrig bleiben, im Normalfall also die Buchstaben. Und das geht beispielsweise so:
- Wechseln Sie zum Register Spalte hinzufügen und wählen Sie das Symbol Benutzerdefinierte Spalte.
- Geben Sie hier bei Neuer Spaltenname beispielsweise den Text Nur Texte ein.
- Bei Benutzerdefinierte Spaltenformel tragen Sie diese Formel ein:
= Text.Remove([Spalte1], Ziffern)
und bestätigen mit OK. Sollten Sie die erste Spalte mit den ursprünglichen Werten (einschließlich der Ziffern) umbenannt haben, werden Sie selbstverständlich den Namen anstatt [Spalte1] verwenden. - 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 ausschließlich die „bereinigte” Spalte benötigen, löschen Sie in der Abfrage (also im Editor) einfach Spalte1.
Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?
Dann würde ich mich über einen Beitrag (z.B. 1,00€ bis 2,00€) Ihrerseits freuen …