Erstes und letztes Zeichen bedingt entfernen

Das erste und letzte Zeichen entfernen, wenn …

Mitunter ist es so, dass der eigentliche Zell-Inhalt außen (an erster und an letzter Position) in ein spezielles Zeichen eingefasst wurde. In einem Forum-Beitrag wurde genau diese Problematik geschildert, wo bei manchen Zellen zu Beginn und an letzter Stelle ein % eingefügt worden ist. Die Forderung war nun, dass bei exakt dieser Konstellation diese beiden %-Zeichen (und nur diese) entfernt werden sollten. Eine entsprechende Muster-Datei können Sie hier in unserem Blog herunterladen.

Auf die Besonderheiten dieser Tabelle werde ich eingehen, wenn das Ergebnis der Power Query-Abfrage vorliegt. Zuerst einmal werden sie Schritt für Schritt den Weg zum Ziel kennenlernen. Stand der Dinge: Sie haben entweder eigene Daten nach dem obigen Muster vorliegen bzw. erstellt oder sie nutzen vorzugsweise meine heruntergeladene Vorlage.

Zu Beginn können Sie die Daten in Spalte A in eine Intelligente Tabelle umwandeln oder sie lassen diesen Schritt durch Power Query automatisch vollziehen. In beiden Fällen es wegen der Vergleichbarkeit zu den hier gezeigten Abbildungen wichtig, dass die Daten keine Überschrift haben und die aktive Zelle innerhalb der Daten ist. In den Versionen Excel 2010/2013 wählen Sie den Menüpunkt Power Query, ab 2016 ist es der Menüpunkt Daten. Anschließend im Menüband Aus bzw. Von Tabelle anklicken. Umgehend wird der Power Query-Editor geöffnet und die Daten der Tabelle dort hinein importiert:

Der Power Query Editor dirkt nach dem Import

Der Power Query Editor dirkt nach dem Import

Im Gegensatz zu sehr vielen anderen mit Power Query zu lösenden Aufgaben genügen hier nicht einige Mausklicks, um zum Ziel zu gelangen. Dennoch ist das ganze überschaubar und für User nach der Einsteiger-Phase gewiss auch nachvollziehbar. Im ersten Schritt soll nun festgestellt werden, ob das erste als auch das letzte Zeichen jeder einzelnen Zelle ein Prozent-Zeichen ist oder nicht. Dazu auf den Menüpunkt Spalte hinzufügen und anschließend Benutzerdefinierte Spalte klicken. Es öffnet sich ein Dialogfenster:

Das Dialog-Fenster für eine benutzerdefiniete Spalte

Das Dialog-Fenster für eine benutzerdefiniete Spalte

In das Textfeld Neuer Spaltenname trage ich eine prägnante, aussagekräftige Bezeichnung ein: %*%, was einem Suchbegriff in Excel entsprechen könnte. Sie können natürlich einen Ihnen genehmen Begriff verwenden, in der endgültigen Phase wird diese Spalte nicht mehr sichtbar sein. In das große Textfeld Benutzerdefinierte Spaltenformel ist das Gleichheitszeichen fest eingefügt. Ich räume ein, dass ich auch die Vokabeln der nachfolgenden Formel großenteils selber hier nachschlagen musste. Der Text der Formel stellt sich nun so dar:
Text.Start([Spalte1],1)="%" and Text.End([Spalte1],1)="%"
Was sie dabei unbedingt beachten müssen: Praktisch alles in Power Query ist Case Sensitiv, also sie müssen exakt die hier gezeigte bzw. in der MS-Website dargestellte Groß- Kleinschreibung beachten. Den Spaltennamen können Sie durch Doppelklick auf den Namen der verfügbaren Spalte im kleineren der beiden Fenster (rechts) übernehmen bzw. markieren und dann auf << Einfügen klicken. Dabei werden dann die eckigen Klammern automatisch mit eingefügt. Prinzipiell ist diese Funktion eine Behauptung, ein Wahrheitswert dass das erste und das letzte Zeichen des Feldes in der Spalte mit der Überschrift Spalte1 das gesuchte % ist. Das Ergebnis wird nach einem OK in die neu erstellte Spalte in englischer Schreibweise FALSE bzw. TRUE eingetragen:

Die neue, ausgefüllte Spalte mit den TRUE/FALSE-Werten

Die neue Spalte mit den Logik-Werten

Diese Werte können nun als Argument für eine Behandlung der Zelle genutzt werden. Ist der Wert TRUE dann muss das erste und das letzte Zeichen gelöscht werden. Aber auch hier gilt, dass die bekannten Excel Formeln aus plain Excel oder VBA nicht verwendet werden können. Es ist wiederum die Sprache M, welche uns die Vokabeln für die Formel zur Verfügung stellt. Wie bereits im vorherigen Schritt erstellen Sie eine neue Spalte. Ich verwende der besseren Aussagekraft wegen den: Titel ohne %*% und gebe dann folgende Formel ein:
if [#"%*%"]=true then Text.Middle([Spalte1],1,Text.Length([Spalte1])-2) else [Spalte1]
wobei es hier besonders einfach ist, wenn sie die Spaltennamen aus dem Bereich Verfügbare Spalten übernehmen; dadurch wird beispielsweise auch das #-Zeichen automatisch an passender Stelle eingefügt. Die einzige Besonderheit in dieser Formel ist, dass der Begriff true klein geschrieben werden muss, obwohl es in der Spalte des Editors GROSS geschrieben ist. Es handelt sich dabei um einen definierten Schlüsselbegriff.

Ich räume ein, dass auch ich auch diese Vokabeln etwas zusammensuchen musste. Falls es Sie wundert, dass bei der Funktion Middle der Wert 1 als 1. Zeichen des zu generieren denn String und von der berechneten Länge 2 Zeichen abgezogen werden, dann bedenken Sie, dass die Rückgabewerte dieser Funktion 0-basiert sind. Nach wiederum einem Klick auf die Schaltfläche OK erkennen sie, dass in der neuen Spalte die Forderung zu 100 % erfüllt ist:

Das Ergebnis steht für einen Vergleich bereit

Das Ergebnis steht für einen Vergleich bereit

Nutzen Sie die Gelegenheit, die berechneten Ergebnisse zu verifizieren und bei Zweifeln zu erkennen, warum das Ergebnis dennoch richtig ist. Hier einige Hinweise, die sie in ihre Überlegungen mit einbeziehen sollten:

  • Zeile 7: Eigentlich ein Selbstgänger, der Form halber aber erwähnt: Jutta ist zwar in &-Zeichen eingefasst aber es zählt immer die komplette Zelle. Und diese beginnt nicht mit dem &.
  • Zeile 8: Dass das Ergebnis mit einem Leerzeichen beginnt ist „natürlich“ korrekt, denn es wurde ja nur das & entfernt und das folgende, nun erste Zeichen ist ein Leerzeichen.
  • Zeile 9: Vielleicht auf den ersten Blick etwas verwunderlich, denn zu Anfang und am Ende steht ja das Ampersand. Aber diese Aussage ist nicht ganz richtig, denn das 1. Zeichen in Spalte1 ist ein Leerzeichen, darum ist die Berechnung durch Power Query korrekt.
  • Zeile 10: Auch ein Selbstgänger. Denn es soll ja nur ein einziges & jeweils vorne und hinten entfernt werden und dieser Vorgang wird auch nur ein Mal durchgeführt.

Was jetzt noch bleibt: Markieren Sie die ersten beiden Spalten und dann beispielsweise Entf oder zeigen Sie in eine der beiden markierten Überschriften, Rechtsklick und Spalten entfernen. Anschließend Register Start und dort den Ribbon Schließen & laden klicken. Die komplette Tabelle (einschließlich der Überschrift) können Sie nun aus dem getrennt angelegten Arbeitsblatt ausschneiden und an beliebiger Stelle beispielsweise im Blatt Tabelle1 einfügen. – Das endgültige Ergebnis können Sie hier herunterladen.

Gemäß der Logik von Power Query gilt: Wenn sich die Quelldaten verändern, sei es durch löschen editieren oder hinzufügen genügt es, wenn sie eine Zelle des Abfrage-Ergebnisses markieren, den dann sichtbaren Menüpunkt Abfrage aktivieren und auf die Schaltfläche Aktualisieren klicken. Alle Änderungen werden umgehend übernommen. Hinweis: Für die Aktualisierung gibt es noch diverse andere Möglichkeiten.

Und last but not least: Auch innerhalb Power Query ist es über die Standard-Menüfunktionalität Transformieren | Formatieren | Kürzen möglich, führende und nachfolgende Leerstellen zu entfernen, was der Excel-Funktion GLÄTTEN() entspricht.

▲ nach oben …

Dieser Beitrag wurde unter Allgemein, Verschiedenes, Ohne Makro/VBA, Power Query, PQ-Formeln (Sprache M), Text-Behandlung abgelegt und mit , , , , , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.

Schreibe einen Kommentar