So fügen Sie führende Nullen in Excel hinzu (5 einfache Möglichkeiten)

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 verwalten, z. B. Mitarbeiter-IDs oder Transaktions-IDs.,

Zum Beispiel möchten Sie einen konsistenten Blick in Ihren Datensatz erhalten, wie unten gezeigt:

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

• Konvertieren des Formats in Text
• Mit benutzerdefinierter Zahlenformatierung
• Mit Textfunktion
• Mit REPT/LEN Funktionen
• /li >
• Mit VBA

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

Mal sehen, wie jeder von ihnen funktioniert.,

Fügen Sie führende Nullen hinzu, indem Sie das Format in Text konvertieren

Wenn Sie es verwenden: Wenn Sie einen kleinen numerischen Datensatz haben und diese Bearbeitung manuell durchführen möchten.

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

Sie versuchen also, die ID durch Eingabe führender Nullen (00001 anstelle von 1) zu ändern.

Aber zu Ihrem Erstaunen konvertiert Excel es wieder in 1.,

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

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

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

Also hier ist, was Sie tun müssen:

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

Das ist es!

Nun, wenn Sie führende Nullen manuell eingeben, würde Excel leicht entsprechen.

Vorsicht: Wenn Sie das Format in Text konvertieren, funktionieren einige Excel-Funktionen nicht ordnungsgemäß. Zum Beispiel würde die SUM/COUNT Funktion die Zelle ignorieren, da sie im Textformat ist.

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

Wenn Sie verwenden: Wenn Sie einen numerischen Datensatz haben und das Ergebnis numerisch sein soll (kein Text).

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

In all den verschiedenen Möglichkeiten, die Zahl anzuzeigen, ändert sich der Wert der Zahl nie. Nur die Art und Weise, wie es angezeigt wird, wird geändert.

Um führende Nullen hinzuzufügen, können wir sie so formatieren, dass sie auf diese Weise angezeigt werden, während der zugrunde liegende Wert unverändert bleibt.

Hier sind die Schritte, um mit dieser Technik 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. Gehe zu Home → Nummerngruppe und klicke auf den Dialog Launcher (ein kleiner geneigter Pfeil unten rechts). Dadurch wird das Dialogfeld Zellen formatieren geöffnet. Alternativ können Sie auch die Tastenkombination verwenden: Control + 1.
3. Wählen Sie im Dialogfeld Zellen formatieren auf der Registerkarte Nummer in der Liste Kategorie die Option Benutzerdefiniert aus.
4. Geben Sie im Feld Typ 00000
5. ein und klicken Sie auf OK.,

Wenn Sie dies tun, werden immer alle Zahlen als fünf Ziffern angezeigt, wobei führende Nullen automatisch hinzugefügt werden, wenn die Zahl kleiner als 5 Ziffern ist. 10 würde also zu 00010 und 100 zu 00100.

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

Hinweis: Diese Technik würde nur für einen numerischen Datensatz funktionieren. Wenn Sie Mitarbeiter-IDs wie A1, A2, A3 usw. haben, handelt es sich um Text und würde sich nicht ändern, wenn Sie das benutzerdefinierte Format wie oben gezeigt anwenden.,

Fügen Sie führende Nullen hinzu, indem Sie die Textfunktion

Verwenden: Wenn das Ergebnis Text sein soll.

Mit der Textfunktion 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 Textfunktion verwenden.

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

So fügen Sie mit der Textfunktion führende Nullen hinzu:

1. Wenn Sie die Zahlen in Spalte A haben(z. B. von A2:A100), 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ünf Ziffern angezeigt, wobei führende Nullen automatisch hinzugefügt werden, wenn die Zahl kleiner als 5 Ziffern ist.,

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

Hinweis: Diese Technik würde nur für einen numerischen Datensatz funktionieren. Wenn Sie Mitarbeiter-IDs wie A1, A2, A3 usw. haben, handelt es sich um Text und würde sich nicht ändern, wenn Sie das benutzerdefinierte Format wie oben gezeigt anwenden.

Fügen Sie führende Nullen hinzu, indem Sie die Funktionen REPT und LEN verwenden

Wenn Sie verwenden: Wenn Sie einen Datensatz haben, der numerisch/alphanumerisch ist und das Ergebnis Text sein soll.,

Der Nachteil der Verwendung der Textfunktion war, dass sie nur mit numerischen Daten funktionieren würde. Wenn Sie jedoch einen alphanumerischen Datensatz haben (z. B. A1, A2, A3 usw.), schlägt die Textfunktion fehl.

In solchen Fällen macht eine Kombination aus REPT-und LEN-Funktion den Trick.

Hier ist, wie es geht:

Dies würde alle Zahlen / Zeichenfolgen 5 Zeichen lang mit führenden Nullen machen, wo immer nötig.

So funktioniert diese Formel:

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

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

Wenn das Hinzufügen führender Nullen in Excel häufig erforderlich ist, 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 from http://trumpexcel.comFunction AddLeadingZeroes(ref As Range, Length As Integer)Dim i As IntegerDim Result As StringDim StrLen As IntegerStrLen = Len(ref)For i = 1 To LengthIf i <= StrLen ThenResult = Result & Mid(ref, i, 1)ElseResult = "0" & ResultEnd IfNext iAddLeadingZeroes = ResultEnd Function

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

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

Sie können auch die folgenden Excel-Tutorials mögen:

• So entfernen Sie führende Nullen in Excel
• Leerzeichen in Excel entfernen – Führende, nachgestellte und doppelte
• Sechs großartige Dinge, die Excel Custom Number Format für Sie tun kann.,
• 10 Möglichkeiten, Daten in Excel-Tabellen zu bereinigen.
• So sperren Sie Zellen in Excel.
• Hinzufügen von Dezimalstellen in Excel (automatisch)