Wissensstand: Level 1 ⇒ Einsteiger in PQ, keine/kaum Erfahrung
PQQ: Führende oder angefügte Nullen, Asterisk (**) oder fast beliebige Sonderzeichen
In manchen Fällen ist es erforderlich, eingegebene Zahlenwerte so zu verändern, dass beispielsweise eine vorgegebene Anzahl von Zeichen vor oder nach einem Dezimaltrenner erreicht wird. In den meisten Fällen werden dieses führende (vorangestellte) Nullen oder Sterne (*) sein, gleiches gilt natürlich auch für andere angehängte Zeichen jeglicher Art.
Wie so oft im Excel-Leben führen mehrere Wege zum Ziel. Für jeden der Fälle gilt, dass das Ergebnis im Format Text sein wird und auch die entsprechenden Füllzeichen tatsächlich erzeugt werden müssen und diese nicht durch ein entsprechendes Zahlenformat nur für die Darstellung generiert werden. Anhand des Zahlenbeispiels 123,45 zeige ich Ihnen unterschiedliche Wege auf, das Ziel zu erreichen. Die Vorgabe ist, dass der Teil vor dem Komma stets 5 Zeichen umfasst und immer 3 Stellen/Positionen nach dem Komma sein müssen. Und ausnahmsweise in diesem Power Query-Beitrag zu beginnen eine (mögliche) Lösung in Plain Excel. 😉
Plain Excel
Ich könnte Ihnen jetzt eine der vielen möglichen Formeln hier hinschreiben und sie dann ihrem Schicksal überlassen. Aber das kann nicht der Sinn meines Forums sein. Darum werde ich in mehreren kleinen Schritten aufzeigen, wie auch Sie zu solch einer Lösung, zu solch einer Formel kommen können. Wie gesagt, Schritt für Schritt. Und nur die endgültige Formel ist genau jenes Ergebnis, welches sie für sich verwenden sollten.
In A1 steht der bereits oben vorgegebene Wert 123,45. Diese Zahlenfolge soll nun nach Vorgabe mit Sternen aufgefüllt werden. Schritt 1: Schreiben Sie in B1 diese Formel:
="****" & GANZZAHL(A1)
und als Ergebnis sehen Sie ****123. Die Vorgabe war ja ein wenig anders, nämlich dass vor dem Komma genau 5 Stellen angezeigt werden sollen. Verändern Sie die Formeln nun so, dass von rechts aus gesehen nur 5 Stellen übrig bleiben:
=RECHTS("****" & GANZZAHL(A1); 5)
Schritt 2: Damit ist der Vorkomma-Anteil korrekt. Ergänzen Sie diese Formel nun so, dass dem Ergebnis noch ein Komma eingefügt wird:
=RECHTS("****" & GANZZAHL(A1); 5) & ","
Als Einsteiger sollten Sie jetzt ein kleines Intermezzo einlegen und in einer beliebigen freien Zelle per Formel berechnen, an welcher Position das Komma in der Zelle A1 steht. Dazu bietet sich diese Formel an: =FINDEN(","; A1) was natürlich zum korrekten Ergebnis 4 führt.
Im nächsten Zwischenschritt (Schritt 3) werden Sie die Formel in B1 dahingehend verändern, dass bis zu 3 Ziffern nach dem Komma aus dem Wert in A1 übernommen werden. Im allerersten Schritt können Sie dazu das eben erworbene Wissen nutzen, dass das Komma ja an der 4. Position steht, die auszuwertenden Ziffern also an der 5. Stelle beginnen. Ergänzen Sie die Formel in B1 nun folgendermaßen:
=RECHTS("****" & GANZZAHL(A1); 5) & "," & TEIL(A1; 4+1; 3)
und das Ergebnis wird korrekt sein. Das ist aber nur der Fall, weil das Komma wirklich an der 4. Stelle steht. Läge die Ausgangszahl im Zehner- oder Tausender Bereich, dann wäre die Position des Komas naturgemäß eine andere. Darum werden sie jetzt in Schritt 4 die per Hand eingetragene 4 in der Formel durch jene Funktion ersetzen, die sie vorher zur Berechnung der Position verwendet haben. Die fast fertige Formel sieht nun folgendermaßen aus:
RECHTS("****" & GANZZAHL(A1); 5) & "," & TEIL(A1; FINDEN(","; A1)+1; 3)
Bleibt nur noch der Schritt 5, wo an den Nachkomma-Teil ein oder mehrere Sterne angefügt werden müssen. Dazu beantworten Sie erst einmal die Frage, aus exakt wie viel Zeichen der endgültige String bestehen wird: Es sind natürlich 9 Zeichen. Also werden sie nun noch die Zeichenfolge *** anhängen und von diesem String die linken 9 Zeichen „abschneiden”:
=LINKS(RECHTS("****" & GANZZAHL(A1); 5) & "," & TEIL(A1; FINDEN(","; A1)+1; 3) & "***";9)
Ich muss gestehen, dass ich mir so etwas einfach nicht mehr antun mag. Aber für Formel-Liebhaber ist das vielfach der einfacher zu handhabende Weg. Und zugegeben, Formel-affine User werden das Ganze auch noch in „eleganter” lösen können und über dieses Konstrukt nur müde lächeln. Mir ging es hier darum, dass Sie auf der einen Seite die Vielfalt des Excel sehen und sich andererseits auch entscheiden können, welchen Weg Sie gehen wollen. Und natürlich funktioniert das alles nur, wenn die Ausgangs-Zahl auch wirklich ein Komma enthält…
Hierüber habe ich die Vorgehensweise beschrieben, die Zahlenfolge mit Sternchen (oder andere Zeichen) aufzufüllen. Obwohl es sich bei führenden oder angehängten Nullen nicht direkt um Zeichen sondern Zahlen handelt, werden Sie die drei Nullen auch in Anführungsstriche setzen, um dadurch eine Wandlung zu einem String zu erreichen.
Power Query (1)
Ein sehr stark an die eben gezeigten Lösung angelehnt der Weg ist auch mit Power Query begehbar. Auch hier ist es natürlich wieder die Zahl 123,45, welche mit Nullen oder Sternen aufgefüllt werden soll. Ich werde hier so weit wie möglich auf Formeln verzichten, dafür lieber den einen oder anderen Schritt mehr durchführen lassen.
Sie beginnen wie üblich damit, die Zelle A1 in den Power Query-Editor zu importieren. Zu Beginn gehen Sie über Start | Spalte teilen | Nach Trennzeichen und akzeptieren den vorgeschlagenen Wert Komma. Bestätigen Sie mit OK und die Zahl ist in 2 Spalten aufgeteilt: Spalte1.1 enthält den Vorkomma-Teil 123 und in Spalte1.2 steht der Nachkomma-Teil, also die 45.
Was jetzt noch fehlt sind die entsprechenden Füllzeichen. Ich verwende hier wiederum die Sterne. Dazu wechseln Sie zum Register Spalte hinzufügen und Klicken dann auf Benutzerdefinierte Spalte. Im neu erschienenen Dialog tragen Sie bei Neuer Spaltenname den Text Sternchen ein und im großen Textfeld darunter bei Benutzerdefinierte Spaltenformel nach dem vorhandenen = den Text „***”:
Die Formel für die Benutzerdefinierte Spalte
PQ wird eine neue Spalte mit der Überschrift Sternchen und dem Inhalt *** erzeugen. Bleiben Sie im Menü-Register Spalte hinzufügen und markieren Sie die Überschrift Sternchen durch einen Mausklick. Anschließend Strg und ein Klick in Spalte1.1. Die Reihenfolge ist wichtig, denn die wird sich Power Query merken, wenn Sie nun auf Spalten zusammenführen Klicken. Belassen Sie es dabei, dass kein Trennzeichen verwendet wird und bei Neuer Spaltenname (optional) geben Sie Ganzzahl ein. Ihr Editor wird sich anschließend so darstellen:
Die Abfrage mit den beiden neuen Spalten
Wechseln Sie zum Register Transformieren und markieren Sie die Überschrift Zusammengeführt. In der Gruppe Textspalte erweitern Sie den Punkt Extrahieren | Letzte Zeichen und geben Sie im Dialog bei Anzahl eine 5 ein, danach per OK schließen. Und voila, das stimmt schon einmal. 😎
Die praktisch gleiche Vorgehensweise werden sie nun für die Zahlen nach dem Komma anwenden. Klicken Sie dieses Mal der anderen Reihenfolge wegen zuerst auf Spalte1.2, anschließend Strg oder Shift und Sternchen. Spalte hinzufügen | Spalten zusammenführen und der neue Spaltenname soll Nachkomma sein. Nachkomma markieren, Transformieren | Extrahieren | Erste Zeichen | 3 und auch hier ist die Wunsch-Zeichenfolge gegeben.
Immer noch im Register Transformieren markieren Sie Ganzzahl und Nachkomma, Spalten zusammenführen, bei Trennzeichen wählen Sie Komma und als neuen Spaltennamen könnten Sie beispielsweise Aufgefüllt verwenden. Damit ist die Aufgabe mit nur einer einzigen kleinen und durchaus transparenten Formel gelöst. Falls Sie statt der Sterne beispielsweise eine führende/anhängende Null verwenden wollen, gilt folgendes: Der Datentyp muss natürlich Text bleiben, denn sonst würden ja die führenden bzw. angehängten Nullen automatisch eliminiert werden. Und die statt der Sternchen in der Formel verwendeten Nullen müssen natürlich auch in „Gänsefüßchen” stehen. Jetzt noch alle Spalten außer der letzten löschen, Schließen & laden in…, Bestehendes Arbeitsblatt auswählen, die Ziel-Zelle festlegen und mit OK bestätigen.
Power Query (2)
Das Beste kommt ja bekanntlich immer zum Schluss. So ist auch die jetzt von mir vorgestellte PQ-Lösung aus meiner Sicht die professionellere. In einer neuen Abfrage ebenfalls die Zelle A1 oder den Bereich Tabelle1 einlesen, falls das gleiche Arbeitsblatt verwendet wird und dort naturgemäß die umzuwandeln der Zahl bereits in einer Intelligenten Tabelle steht.
Zuerst werden Sie die Zelle wiederum teilen und als Kriterium das Komma verwenden. Wie gehabt existieren nun die beiden Spalten Spalte1.1 und Spalte1.2. Power Query hat aber den Typ der beiden nun als einzeln stehende Zahlen auch als Zahl erkannt und umgewandelt. Sie erkennen das ganz klar im rechten Seitenfenster, hier zeige ich mit der Maus darauf:
Automatisch geänderter Typ der Daten
Für die nun folgenden Schritte ist genau das aber gar nicht vorteilhaft, es wird jeweils der Datentyp Text für die Operationen erwartet. Darum zeigen Sie auf das kleine Kreuz links des Text-Eintrages im rechten Seitenfenster und löschen Sie diesen Schritt durch einen Klick auf das rote Kreuzchen. Markieren Sie nun die erste Spalte mit der Ganzzahl, Spalte hinzufügen | Benutzerdefinierte Spalte und geben Sie bei Neuer Spaltenname Ganzzahl ein. Bei Benutzerdefinierte Spaltenformel tragen Sie nach dem vorgegebenen Gleichheitszeichen diese Formel ein:
= Text.PadStart([Spalte1.1], 5, "*")
Wie in der Sprache M üblich ist es wichtig, dass sie exakt diese Groß- Kleinschreibung verwenden. Den Namen Spalte1.1 in den eckigen Klammern können Sie einfach übernehmen, indem sie im rechten Seitenfenster Verfügbare Spalten einen Doppelklick auf den entsprechenden Spaltennamen durchführen.
Der gleichen Logik folgend werden sie nun eine weitere Spalte für den Nachkomma-Teil durchführen. Die Formel in der Benutzerdefinierten Spalte zitiert folgendermaßen aus:
= Text.PadEnd([Spalte1.2], 3, "*")
Wenn Sie nun die beiden eben erzeugten Spalten über Transformieren | Spalten zusammenführen | Trennzeichen: Komma mit dem Neuen Spaltennamen Aufgefüllt generieren, haben Sie mit wenigen Schritten und auch durchaus überschaubaren Formeln das Ergebnis erzielt. Die ersten beiden Spalten können natürlich gelöscht und das Ergebnis an ihre Wunschposition per Schließen & laden in… gespeichert werden.