Artikelnummern aus einer Formel
Artikelnummern aus einer Formel

Individuelles Zahlenformat für Texte und Werte in Excel-Tabellen festlegen

So erzeugen Sie eine individuelle Darstellung für Zahlen-Buchstaben-Kombinationen in Ihren Excel-Zellen

Individuelle Zahlenformate in Excel einstellen - Beispiel: Artikelnummer

In einer Tabelle befindet sich eine umfangreiche Liste mit Artikeldaten. In der ersten Spalte wird eine Artikelnummer als sechsstellige Zahl angegeben.

Wegen einer Umstellung in der Warenwirtschaft muss jeder Artikelnummer ein identischer Buchstabencode vorangestellt werden. Die Buchstaben und immer drei Ziffern sind zudem durch Punkte zu trennen. Aus 123456 wird also ABC.123.456.

Blitzschnell lösen Sie diese Aufgabenstellung mit einem benutzerdefinierten Zahlenformat. Dazu gehen Sie folgendermaßen vor:

  1. Markieren Sie die Zellen, die die umzuwandelnden Artikelnummern enthalten. Dann rufen Sie mit der Tastenkombination STRG 1 das Dialogfenster ZELLEN FORMATIEREN auf. Das funktioniert in allen Excel-Versionen.
  2. Aktivieren Sie das Register ZAHLEN.
  3. Unter KATEGORIE klicken Sie in der Dialogbox auf BENUTZERDEFINIERT.
  4. In das Feld TYP tragen Sie dann die folgende Formatierungsanweisung ein:  "ABC."###"."###
  5. Diese benutzerdefinierte Formatierung bestätigen Sie mit der Schaltfläche OK.

Die Zahlen erscheinen dann in der gewünschten Form in Ihrer Tabelle. Die Abbildung rechts zeigt die Artikelnummern im Standardformat und mit der beschriebenen benutzerdefinierten Formatierung.

Beachten Sie, dass der Text und die Punkte bei der benutzerdefinierten Formatierung den Zahlen nicht tatsächlich hinzugefügt werden. Diese werden nur angezeigt, während in der Zelle die ursprüngliche Zahl erhalten bleibt.

Das hat den Vorteil, dass Sie für Berechnungen auf Zahlen zugreifen können, was bei einer Umwandlung in Text nicht mehr funktionieren würde.

So wandeln Sie die Inhalte über Tabellenfunktionen um

Für den Fall, dass die Buchstaben und Punkte tatsächlich in der Zelle erscheinen sollen, kommen Sie an einer Umwandlung in Text nicht vorbei. Dazu gehen Sie folgendermaßen 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 C die Hilfsspalte ist, tragen Sie in die Zelle C2 die folgende Formel ein:

="ABC."&LINKS(A2;3)&"."&RECHTS(A2;3)

Diese Formel stellt die Artikelnummern in der gewünschten Form in der Tabelle dar. Das hat allerdings den Nachteil, dass die Zahlenwerte verloren gehen, sodass Sie keine Berechnungen mehr damit durchführen können.

Die Abbildung rechts zeigt den Einsatz der Formel zur Generierung der Artikelnummern in der Beispieltabelle.

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 löschen, wenn Sie sie nicht mehr benötigen.

Zweites Beispiel: Erfolgsreiche Formatierung von Kreditkartennummern in Excel

Für die korrekte Darstellung von Kreditkartennummern stellt Excel leider auch kein integriertes Format zur Verfügung. Auch die benutzerdefinierte Formatierung führt nicht zum gewünschten Ergebnis. Mit einer Formel in einer Hilfszelle erreichen Sie das gewünschte Ergebnis.

Kreditkartennummern sind sechzehnstellig und werden gerne in Viererblöcken in der folgenden Form dargestellt. 1234 1234 1234 1234

Spontan betrachtet sollte sich die gewünschte Darstellung mithilfe des folgenden benutzerdefinierten Zahlenformats einfach realisieren lassen.

Dieses Format führt in der Tabelle dazu, dass die Blöcke angezeigt werden. Die letzte Ziffer wird dabei allerdings durch eine Null ersetzt. Die Abbildung rechts  zeigt, wie das aussieht.

Grund für die Null ist, dass Excel nur maximal 15 signifikante Stellen verarbeitet. Excel wird also immer die letzte Ziffer auf Null setzen, solange Sie die Kreditkarten-Nummer als Zahl eingeben.

Sie müssen Kreditkartennummern daher als Text erfassen. Das erreichen Sie durch ein vorangestelltes Hochkomma oder die vorherige Zuweisung des Textformats

Damit lösen Sie natürlich nicht das Problem, die Kreditkarten-Nummern als Viererblöcke darzustellen. Leider können Sie Textwerten aber wiederum kein Zahlenformat zuweisen.

Die Lösung ist auch hier, eine zusätzliche Spalte einzufügen, in der Sie per Formel die gewünschte Darstellung ausgeben lassen.

Wenn Sie die Kreditkarten-Nummer zum Beispiel in 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)

Die Abbildung rechts zeigt den Einsatz dieser Formel in einer Beispieltabelle.