So fügen Sie führende Nullen in Excel hinzu - Alles, was Sie wissen müssen

Es gibt Situationen, in denen Sie einem Datensatz in Excel führende Nullen hinzufügen müssen. Dies kann der Fall sein, wenn Sie Datensätze in Excel pflegen, z. B. Mitarbeiter-IDs oder Transaktions-IDs.

Beispielsweise möchten Sie möglicherweise ein einheitliches Erscheinungsbild in Ihrem Datensatz erhalten, wie unten gezeigt:

In diesem Tutorial lernen Sie verschiedene Möglichkeiten kennen, um führende Nullen in Excel hinzuzufügen:

• Konvertieren des Formats in Text
• Verwenden der benutzerdefinierten Zahlenformatierung
• Verwenden der Textfunktion
• Verwenden der REPT/LEN-Funktionen
• Verwenden von VBA

Jede dieser Methoden hat einige Vor- und Nachteile (die in jedem Abschnitt behandelt werden).

Sehen wir uns an, wie jede dieser Funktionen funktioniert.

Füge führende Nullen hinzu, indem du das Format in Text umwandelst

Wann zu verwenden: Wenn Sie über einen kleinen numerischen Datensatz verfügen und diese Bearbeitung manuell durchführen möchten.

Angenommen, Sie haben Mitarbeiter-IDs der Marketingabteilung, wie unten gezeigt, und möchten, dass diese IDs konsistent aussehen, indem Sie führende Nullen hinzufügen.

Sie versuchen also, die ID zu ändern, indem Sie führende Nullen eingeben (00001 statt 1).

Aber zu Ihrem Erstaunen konvertiert Excel es zurück in 1.

Dies geschieht, da Excel versteht, dass 00001 und 1 dieselben Zahlen sind und denselben Anzeigeregeln folgen sollten.

So frustrierend es für Sie auch sein mag, Excel hat seine Gründe.

Um also die Arbeit zu erledigen, ohne Excel-Regeln zu verbiegen, müssen Sie die Tatsache nutzen, dass diese Regel nicht für die Textformatierung gilt.

Hier ist also, was Sie tun müssen:

1. Wählen Sie die Zellen aus, in denen Sie führende Nullen manuell hinzufügen möchten.
2. Gehen Sie zu Home → Nummerngruppe und wählen Sie Text aus der Dropdown-Liste.

Das ist es!

Wenn Sie nun führende Nullen manuell eingeben, würde Excel dies problemlos tun.

Achtung: Wenn Sie das Format in Text konvertieren, funktionieren einige Excel-Funktionen nicht richtig. Beispielsweise würde die Funktion SUM/COUNT die Zelle ignorieren, da sie im Textformat vorliegt.

Fügen Sie führende Nullen hinzu, indem Sie die benutzerdefinierte Zahlenformatierung verwenden

Wann zu verwenden: Wenn Sie über einen numerischen Datensatz verfügen und das Ergebnis numerisch (kein Text) sein soll.

Wenn Sie eine Zahl in einem bestimmten Format anzeigen, ändert sich der zugrunde liegende Wert der Zahl nicht. Zum Beispiel kann ich die Zahl 1000 als 1000 oder 1.000 oder 1000,00 oder 001000 oder 26-09-1902 anzeigen (gerade Daten sind Zahlen im Backend in Excel).

Bei all den verschiedenen Arten, die Zahl anzuzeigen, ändert sich der Wert der Zahl nie. Es ändert sich nur die Art der Darstellung.

Um führende Nullen hinzuzufügen, können wir es so formatieren, dass es so angezeigt wird, während der zugrunde liegende Wert unverändert bleibt.

Hier sind die Schritte, um diese Technik zu verwenden, um führende Nullen in Excel hinzuzufügen:

1. Wählen Sie die Zellen aus, in denen Sie führende Nullen hinzufügen möchten.
2. Gehen Sie zu Start → Nummerngruppe und klicken Sie auf den Dialogstarter (ein kleiner geneigter Pfeil unten rechts). Dadurch wird das Dialogfeld Zellen formatieren geöffnet. Alternativ können Sie auch die Tastenkombination verwenden: Strg + 1.
3. Wählen Sie im Dialogfeld Zellen formatieren auf der Registerkarte Zahl in der Liste Kategorie die Option Benutzerdefiniert aus.
4. Geben Sie im Feld Typ 00000 . ein
5. OK klicken.

Dadurch werden alle Zahlen immer fünfstellig angezeigt, wobei führende Nullen automatisch hinzugefügt werden, wenn die Zahl weniger als fünfstellig ist. Aus 10 wird 00010 und aus 100 wird 00100.

In diesem Fall haben wir sechs Nullen verwendet, aber wenn Ihre Daten Zahlen mit mehr Ziffern enthalten, müssen Sie das Format entsprechend verwenden.

Hinweis: Diese Technik funktioniert nur für ein numerisches Dataset. Falls Sie Mitarbeiter-IDs wie A1, A2, A3 usw. haben, handelt es sich um Text, der sich nicht ändert, wenn Sie das benutzerdefinierte Format wie oben gezeigt anwenden.

Fügen Sie führende Nullen mithilfe der TEXT-Funktion hinzu

Wann zu verwenden: Wenn das Ergebnis Text sein soll.

Mit der TEXT-Funktion können Sie den Wert in das gewünschte Format ändern.

Wenn Sie beispielsweise möchten, dass 1 als 001 angezeigt wird, können Sie dafür die TEXT-Funktion verwenden.

Denken Sie jedoch daran, dass die TEXT-Funktion das Format ändern und es zu TEXT machen würde. Dies bedeutet, dass Excel das neue Ergebnis als Text mit drei Zeichen behandelt, wenn Sie 1 als 001 festlegen (genau wie abc oder xyz).

So fügen Sie mit der TEXT-Funktion führende Nullen hinzu:

1. Wenn Sie die Zahlen in Spalte A haben (sagen wir von A2:A100), dann wählen Sie B2:B100 und geben Sie die folgende Formel ein:
   =TEXT(A2,00000″)
2. Drücken Sie Strg + Eingabetaste, um die Formel auf alle ausgewählten Zellen anzuwenden.

Dadurch werden alle Zahlen als fünfstellig angezeigt, wobei führende Nullen automatisch hinzugefügt werden, wenn die Zahl weniger als fünfstellig ist.

Ein Vorteil der Konvertierung von Daten in Text besteht darin, dass Sie sie jetzt in Nachschlageformeln wie SVERWEIS oder INDEX/MATCH verwenden können, um die Details eines Mitarbeiters mit seiner Mitarbeiter-ID abzurufen.

Hinweis: Diese Technik funktioniert nur für ein numerisches Dataset. Falls Sie Mitarbeiter-IDs wie A1, A2, A3 usw. haben, handelt es sich um Text und würden sich nicht ändern, wenn Sie das benutzerdefinierte Format wie oben gezeigt anwenden.

Füge führende Nullen hinzu, indem du die Funktionen REPT und LEN verwendest

Wann zu verwenden: Wenn Sie einen numerischen/alphanumerischen Datensatz haben und das Ergebnis Text sein soll.

Der Nachteil der TEXT-Funktion bestand darin, dass sie nur mit numerischen Daten funktionierte. Wenn Sie jedoch einen alphanumerischen Datensatz haben (z. B. A1, A2, A3 usw.), schlägt die TEXT-Funktion fehl.

In solchen Fällen hilft eine Kombination aus REPT- und LEN-Funktion.

So geht's:

1. Wenn Sie die Zahlen in Spalte A haben (sagen wir von A2:A100), dann wählen Sie B2:B100 und geben Sie die folgende Formel ein:
   =REPT(0,5-LEN(A2))&A2
2. Drücken Sie Strg + Eingabetaste, um die Formel auf alle ausgewählten Zellen anzuwenden.

Dies würde alle Zahlen / Zeichenfolgen 5 Zeichen lang mit führenden Nullen machen, wo immer sie benötigt werden.

So funktioniert diese Formel:

• LEN(A2) gibt die Länge des Strings/der Zahlen in der Zelle an.
• =REPT(0,5-LEN(A2)) würde die Zahl 0 ergeben, die hinzugefügt werden soll. Hier habe ich 5 in der Formel verwendet, da dies die maximale Länge von Zeichenfolgen / Zahlen in meinem Datensatz war. Sie können dies entsprechend Ihren Daten ändern.
• =REPT(0,5-LEN(A2))&A2 würde einfach die Anzahl der Nullen zum Wert der Zelle hinzufügen. Wenn der Wert in der Zelle beispielsweise 123 ist, würde dies 00123 zurückgeben.

Fügen Sie führende Nullen mithilfe der benutzerdefinierten Funktion (VBA) hinzu

Wenn Sie häufig führende Nullen in Excel hinzufügen müssen, ist die Verwendung einer benutzerdefinierten Funktion eine gute Idee.

Hier ist der VBA-Code, der eine einfache Funktion zum Hinzufügen führender Nullen erstellt:

'Code by Sumit Bansal von http://trumpexcel.com Funktion AddLeadingZeroes(ref As Range, Length As Integer) Dim i As Integer Dim Result As String Dim StrLen As Integer StrLen = Len(ref) For i = 1 To Length If i <= StrLen Then Result = Result & Mid(ref, i, 1) Else Result = "0" & ​​Result End If Next i AddLeadingZeroes = Result End Function

Fügen Sie diesen Code einfach zum Modulcodefenster hinzu, und Sie können ihn wie jede andere Arbeitsblattfunktion verwenden.

Oder erstellen Sie ein Add-In dafür und teilen Sie es mit Ihren Kollegen.