Individuelles Zahlenformat für Texte und Werte in Excel-Tabellen festlegen
Neben den zahlreichen Funktionen in Excel, mit denen sich effizient professionelle Kalkulationstabellen aufbauen lassen, sind auch die Zahlenformate wichtig, damit Betrachter einer Tabelle auf einen Blick ihren Mehrwert und die Kernaussagen verstehen.
Um dies zu erreichen, ist es zielführend, mit benutzerdefinierten Zahlenformaten in Microsoft Excel zu arbeiten. Individuelle Zahlenformate sind unter anderem sinnvoll, wenn Sie beispielsweise eine bestimmte Anzahl von Dezimalstellen anzeigen oder Zahlen in verschiedenen Einheiten formatieren möchten. Individuelle Zahlenformate können ebenso hilfreich sein, wenn einzelne Kriterien hervorgehoben werden sollen, z. B. negative Zahlen oder Werte, die außerhalb eines bestimmten Bereichs liegen.
In diesem Artikel erfahren Sie anhand von zwei nachvollziehbaren Praxisbeispielen, wie individuelle Zahlenformate in Microsoft Excel im Zellbereich angelegt werden und wo der Mehrwert von individuellen Zahlenformaten liegt.
Wie kann man individuelle Zahlenformate in Excel einstellen?
Am ersten Praxisbeispiel ist im Detail ablesbar, warum individuelle Zahlenformate in Excel nützlich und zielführend sein können:
Ein Unternehmen verwaltet in einer Excel-Tabelle eine umfangreiche Liste mit Artikeldaten. In der ersten Spalte wird eine Artikelnummer als sechs-stellige Zahl angegeben. Aufgrund einer softwarespezifischen Umstellung im Warenwirtschaftsprogramm muss ab sofort jeder Artikelnummer ein identischer Buchstabencode vorangestellt werden. Das neue Format ist so aufgebaut, dass grundsätzlich die Buchstaben von den Zahlen durch einen Punkt zu trennen sind. Außerdem werden immer drei Ziffern durch einen Punkt getrennt.
Aus der alten Artikelnummer 730452 wird nach Anwendung des individuellen Zahlenformats die Artikelnummer: ABC.730.452.
Um die neue Artikelnummer in wenigen Schritten in Excel individuell zu formatieren, nutzen Sie ein benutzerdefiniertes Zahlenformat und gehen wie folgt vor:
Markieren Sie im Beispiel die Zellen B2 bis B8. Diese enthalten in unserer Beispieltabelle die umzuwandelnden Artikelnummern. Im nächsten Schritt rufen Sie mit der Tastenkombination STRG 1 das Dialogfenster ZELLEN FORMATIEREN auf. Das Kommando funktioniert in allen Excel-Versionen.
Aktivieren Sie das Register ZAHLEN.
Unter KATEGORIE klicken Sie in der Dialogbox auf BENUTZERDEFINIERT.
In das Feld TYP tragen Sie dann die folgende Formatierungsanweisung ein: "ABC."###"."###
Die benutzerdefinierte Formatierung bestätigen Sie zum Schluss mit der Schaltfläche OK. Die Zahlen erscheinen dann in der gewünschten Form in Ihrer Tabelle.
Wichtig: Beachten Sie, dass der Text und die Punkte bei der benutzerdefinierten Formatierung den Zahlen nicht tatsächlich hinzugefügt werden. Dies erkennen Sie, wenn Sie in die Eingabezeile klicken.
Dort wird die Artikelnummer in ihrer ursprünglichen Formatierung dargestellt. Diese Vorgehensweise, die Sie durch die Anführungsstriche im benutzerdefinierten Zahlenformat implementieren, hat den Vorteil, dass Sie für Kalkulation in Excel weiterhin auf Zahlen zugreifen können. Dies würde bei einer Umwandlung in Text nicht mehr funktionieren.
Wie kann man die Inhalte über eine Tabellenfunktion umwandeln?
Für den Fall, dass die Buchstaben und Punkte tatsächlich in der Zelle erscheinen sollen, ist eine Umwandlung des Zahlenformats in Text alternativlos. Um Zahlen in Text zu formatieren, gehen Sie wie folgt vor:
Fügen Sie neben der Spalte mit den Artikelnummern eine Hilfsspalte ein. In dieser werden die Artikelnummern mittels einer Formel mit Text und Punkten versehen.
Wenn die erste Nummer in der Zelle A2 steht und die Spalte C als Hilfsspalte fungieren soll, tragen Sie in die Zelle C2 die folgende Formel ein:
="ABC."&LINKS(A2;3)&"."&RECHTS(A2;3)
Die Formel stellt die Artikelnummern in der gewünschten Form in der Tabelle dar. Gleichzeitig gehen durch Anwendung der Formel die Zahlenwerte verloren, sodass Sie in der Spalte C keine weiteren Berechnungen durchgeführt oder die Zahlen weiterverarbeitet werden können.
Die Formel kopieren Sie in der Hilfsspalte ausreichend weit nach unten. Anschließend kopieren Sie die Hilfsspalte in die Zwischenablage und fügen sie über das Kommando INHALTE EINFÜGEN mit der Option WERTE wieder ein. Die Formeln werden dabei durch die tatsächlichen Artikelnummern ersetzt. Die Spalte mit den alten Artikelnummern können Sie bei Bedarf löschen, wenn Sie sie nicht mehr benötigen.
2. Beispiel: Wie kann man benutzerdefinierte Zahlenformate in Excel hinterlegen?
Im zweiten Beispiel sehen Sie wie Sie das benutzerdefinierte Zahlenformat in Excel nutzen können, um Kreditkartennummer in Excel professionell zu formatieren. Kreditkarten werden immer beliebter und sind aus dem Zahlungsverkehr von Firmen und Privatleuten nicht mehr wegzudenken. Für die korrekte Darstellung von Kreditkartennummern, deren sechzehn Stellen üblicherweise in Viererblöcken dargestellt werden, stellt Excel kein integriertes Format zur Verfügung. Spontan betrachtet sollte sich die gewünschte Darstellung mithilfe des folgenden benutzerdefinierten Zahlenformats einfach realisieren lassen.
#### #### #### ####
Dieses Format führt im Zellbereich der Tabelle dazu, dass die Blöcke angezeigt werden. Die letzte Ziffer wird dabei allerdings grundsätzlich durch eine Null ersetzt. Grund für Darstellung der Null ist die Tatsache, dass Excel maximal 15 Stellen verarbeiten kann. Jede 16-stellig Kreditkartennummer wird aus diesem Grund immer mit einer Null am Ende dargestellt werden.
Um die Kreditkartennummer korrekt darzustellen, müssen Kreditkartennummern als Text erfasst werden. Das erreichen Sie durch ein vorangestelltes Hochkomma oder die vorherige Zuweisung des Textformats.
Trotz der Textdarstellung wird das eigentliche Ziel, die Kreditkarten-Nummern als Viererblöcke darzustellen, durch die Formatänderung nicht erreicht. Ähnlich wie im ersten Beispiel besteht die Lösung wiederum darin, eine zusätzliche Hilfsspalte einzufügen, in der Sie per Formel die gewünschte Darstellung ausgeben lassen.
Wenn Sie die Kreditkarten-Nummer zum Beispiel in der Zelle B1 eingegeben haben, können Sie in Zelle C1 mit folgender Formel eine Darstellung in Viererblöcken erreichen:
=LINKS(B1;4)&" "&TEIL(B1;5;4)&" "&TEIL(B1;9;4)&" "&RECHTS(B1;4)
Durch die Verwendung der Hilfsspalte kann die Kreditkartennummer korrekt dargestellt und in der Folge weiterverarbeitet werden.
Egal ob Sie ein benutzerdefiniertes Zahlenformat nutzen, um Artikelnummern individuell darzustellen, Kreditkartennummern in Viererblocks aufzubauen – entscheidend ist, dass das Zahlenformat so aufgebaut ist, dass Nutzer schnell die wichtigsten Informationen aufnehmen können.
