In diesem Tutorial werden die verschiedenen Methoden zum Hinzufügen oder Beibehalten führender Nullen sowie zum Entfernen führender Nullen in Excel behandelt.
Immer wenn Sie Zahlen mit einer oder mehreren führenden Nullen eingeben oder importieren, z. B. 000652, entfernt Excel diese Nullen automatisch, und nur die Zahl selbst wird in den Zellen angezeigt (652). Dies liegt daran, dass die führenden Nullen für Berechnungen nicht erforderlich sind und nicht zählen.
Es gibt jedoch Situationen, in denen diese führenden Nullen erforderlich sind, z. B. wenn Sie ID-Nummern, Telefonnummern, Kreditkartennummern, Produktcodes oder Postleitzahlen usw. eingeben. Glücklicherweise bietet Excel uns mehrere Möglichkeiten, führende Nullen hinzuzufügen oder beizubehalten in Zellen. In diesem Artikel zeigen wir Ihnen die verschiedenen Möglichkeiten, führende Nullen hinzuzufügen oder beizubehalten und führende Nullen zu entfernen.
Hinzufügen führender Nullen in Excel
Im Wesentlichen gibt es zwei Methoden, mit denen Sie führende Nullen hinzufügen können: Erstens, formatieren Sie Ihre Zahl als "Text"; zweitens verwenden Sie benutzerdefinierte Formatierungen, um führende Nullen hinzuzufügen. Welche Methode Sie verwenden möchten, hängt möglicherweise davon ab, was Sie mit der Nummer tun möchten.
Möglicherweise möchten Sie eine führende Null hinzufügen, wenn Sie eindeutige ID-Nummern, Kontonummern, Sozialversicherungsnummern oder Postleitzahlen usw. eingeben. Sie werden diese Zahlen jedoch nicht für Berechnungen oder in Funktionen verwenden, daher ist es am besten, diese umzuwandeln Zahlen zum Text. Sie würden niemals Telefonnummern oder Kontonummern summieren oder mitteln.
Es gibt mehrere Möglichkeiten, Nullen vor den Zahlen hinzuzufügen oder aufzufüllen, indem Sie sie als Text formatieren:
- Ändern des Zellenformats in Text
- Apostroph (‘) hinzufügen
- Verwenden der TEXT-Funktion
- Verwendung der REPT/LEN-Funktion
- Verwenden Sie die CONCATENATE-Funktion/den kaufmännischen Und-Operator (&)
- Verwenden der RECHTEN Funktion
Ändern des Zellenformats in Text
Dies ist eine der einfachsten Möglichkeiten, Ihren Zahlen führende Nullen hinzuzufügen. Wenn Sie nur Zahlen eingeben und während der Eingabe führende Nullen beibehalten möchten, ist dies die richtige Methode für Sie. Indem Sie das Zellenformat von Allgemein oder Zahl in Text ändern, können Sie Excel zwingen, Ihre Zahlen als Textwerte zu behandeln, und alles, was Sie in die Zelle eingeben, bleibt genau gleich. So machen Sie es:
Wählen Sie die Zelle(n) aus, in der Sie führende Nullen hinzufügen möchten. Gehen Sie zur Registerkarte „Start“, klicken Sie auf das Dropdown-Feld „Format“ in der Gruppe Zahlen und wählen Sie „Text“ aus den Formatoptionen.
Wenn Sie nun Ihre Zahlen eingeben, löscht Excel keine führenden Nullen daraus.
Möglicherweise sehen Sie ein kleines grünes Dreieck (Fehleranzeige) in der oberen linken Ecke der Zelle und wenn Sie diese Zelle auswählen, wird ein Warnzeichen angezeigt, das darauf hinweist, dass Sie die Nummer als Text gespeichert haben.
Um die Fehlermeldung zu entfernen, wählen Sie die Zelle(n), klicken Sie auf das Warnzeichen und wählen Sie dann „Fehler ignorieren“ aus der Liste.
Sie können Telefonnummern auch mit Leerzeichen oder Bindestrich zwischen den Nummern eingeben, Excel behandelt diese Nummern automatisch als Text.
Führend verwenden Apostroph (‘)
Eine andere Möglichkeit, führende Nullen in Excel hinzuzufügen, besteht darin, ein Apostroph (‘) am Anfang der Zahl hinzuzufügen. Dadurch wird Excel gezwungen, eine Zahl als Text einzugeben.
Geben Sie einfach ein Apostroph vor einer beliebigen Zahl ein und drücken Sie die Eingabetaste. Excel lässt die führenden Nullen intakt, aber das (‘) wird im Arbeitsblatt nicht sichtbar, es sei denn, Sie wählen die Zelle aus.
Verwenden der Textfunktion
Die obige Methode fügt Zahlen während der Eingabe Nullen hinzu. Wenn Sie jedoch bereits eine Liste mit Zahlen haben und führende Nullen davor auffüllen möchten, ist die TEXT-Funktion die richtige Methode für Sie. Mit der TEXT-Funktion können Sie Zahlen in Textzeichenfolgen konvertieren, während Sie eine benutzerdefinierte Formatierung anwenden.
Die Syntax der TEXT-Funktion:
= TEXT(wert, format_text)Wo,
- Wert - Es ist der numerische Wert, den Sie in Text konvertieren und formatieren müssen.
- format_text – ist das Format, das Sie anwenden möchten.
Mit der TEXT-Funktion können Sie festlegen, wie viele Stellen Ihre Nummer haben soll. Wenn Ihre Zahlen beispielsweise 8 Stellen lang sein sollen, geben Sie 8 Nullen in das zweite Argument der Funktion ein: „00000000“. Wenn Sie eine 6-stellige Zahl in einer Zelle haben, fügt die Funktion manuell 2 führende Nullen hinzu, und wenn Sie 2-stellige Zahlen wie 56 haben, sind der Rest Nullen (00000056).
Um beispielsweise führende Nullen hinzuzufügen und die Zahlen 6-stellig zu machen, verwenden Sie diese Formel:
=TEXT(A2,"000000")Da das zweite Argument der Formel 6 Nullen enthält, wandelt die Funktion die Zahlenzeichenfolge in eine Textzeichenfolge um und fügt 5 führende Nullen hinzu, um die Zeichenfolge 6-stellig zu machen.
Notiz: Bitte denken Sie daran, die Formatcodes in der Funktion in doppelte Anführungszeichen zu setzen.
Jetzt können Sie dieselbe Formel auf den Rest der Zellen anwenden, indem Sie den Füllpunkt ziehen. Wie Sie sehen, wandelt die Funktion die Zahlen in Texte um und fügt den Zahlen führende Nullen hinzu, sodass die Gesamtzahl der Ziffern 6 beträgt.
Die TEXT-Funktion gibt den Wert immer als Textzeichenfolge und nicht als Zahl zurück, sodass Sie sie nicht in arithmetischen Berechnungen verwenden können, aber Sie können sie dennoch in Nachschlageformeln wie SVERWEIS oder INDEX/VERGLEICH verwenden, um die Details von a abzurufen Produkt mit Produkt-IDs.
Verwenden der CONCATENATE-Funktion/des kaufmännischen Und-Operators (&)
Wenn Sie vor allen Zahlen in einer Spalte eine feste Anzahl führender Nullen hinzufügen möchten, können Sie die Funktion VERBINDEN oder den kaufmännischen Und-Operator (&) verwenden.
Syntax der CONCATENATE-Funktion:
=VERKETTEN(text1, [text2], ...)Wo,
Text 1 - Die Anzahl der Nullen, die vor der Zahl eingefügt werden sollen.
Text2 – Die ursprüngliche Nummer oder Zellreferenz
Syntax des kaufmännischen Und-Operators:
=Wert_1 & Wert_2 Wo,
Wert_1 ist die führende Null, die vor der Zahl einzufügen ist, und Wert_2 ist die Zahl.
Um beispielsweise nur zwei Nullen vor einer Zahl hinzuzufügen, verwenden Sie eine der folgenden Formeln:
=VERKETTEN("00",A2)Das erste Argument sind zwei Nullen („00“), weil wir zwei Nullen vor der Zahl in A2 (das zweite Argument) auffüllen wollen.
Oder,
="00"&A2Hier besteht das erste Argument aus 2 Nullen, gefolgt vom '&'-Operator, und das zweite Argument ist die Zahl.
Wie Sie sehen, fügt die Formel allen Zahlen in einer Spalte nur zwei führende Nullen hinzu, unabhängig davon, wie viele Ziffern die Zahl enthält.
Beide Formeln verbinden eine bestimmte Anzahl von Nullen vor den ursprünglichen Zahlen und speichern sie als Textzeichenfolgen.
Verwendung der REPT/LEN-Funktion
Wenn Sie numerischen oder alphanumerischen Daten führende Nullen hinzufügen und die Zeichenfolge in Text umwandeln möchten, verwenden Sie die REPT-Funktion. Die REPT-Funktion wird verwendet, um ein oder mehrere Zeichen eine bestimmte Anzahl von Malen zu wiederholen. Diese Funktion kann auch verwendet werden, um feste Zahlen mit führenden Nullen vor der Zahl einzufügen.
=REPT(Text, Anzahl_Zeiten)Wobei „text“ das Zeichen ist, das wir wiederholen möchten (in unserem Fall „0“) und das Argument „number_times“ die Anzahl der Wiederholungen dieses Zeichens ist.
Um beispielsweise fünf Nullen vor Zahlen zu generieren, würde die Formel wie folgt aussehen:
=REPT(0,5)&A2Die Formel wiederholt 5 Nullen und verbindet die Zahlenfolge in A2 und gibt das Ergebnis zurück. Anschließend wird die Formel mithilfe des Ausfüllkästchens auf die Zelle B2:B6 angewendet.
Die obige Formel fügt vor der Zahl eine feste Anzahl von Nullen hinzu, aber die Gesamtlänge der Zahl variiert je nach Zahl.
Wenn Sie führende Nullen hinzufügen möchten, wo immer dies erforderlich ist, um Zeichenfolgen mit einer bestimmten Länge (fester Länge) zu erstellen, können Sie die Funktionen REPT und LEN zusammen verwenden.
Syntax:
=REPT(text, number_times-LEN(text))&cellUm beispielsweise dem Wert in A2 vorangestellte Nullen hinzuzufügen und eine 5 Zeichen lange Zeichenfolge zu erstellen, versuchen Sie es mit dieser Formel:
=REPT(0,5-LEN(A2))&A2Hier erhält 'LEN(A2)' die Gesamtlänge der Zeichenfolge/Zahlen in Zelle A2. „5“ ist die maximale Länge von Zeichenfolgen/Zahlen, die die Zelle haben sollte. Und der Teil „REPT(0,5-LEN(A2))“ fügt die Anzahl der Nullen hinzu, indem die Länge der Zeichenfolge in A2 von der maximalen Anzahl der Nullen abgezogen wird (5). Dann wird eine Anzahl von Nullen vor dem Wert von A2 verbunden, um eine Zeichenfolge mit fester Länge zu erstellen.
Verwenden der RIGHT-Funktion
Eine andere Möglichkeit, führende Nullen vor einer Zeichenfolge in Excel aufzufüllen, besteht darin, die RECHTE Funktion zu verwenden.
Die Funktion RECHTS kann am Anfang einer Zahl eine Reihe von Nullen hinzufügen und die N Zeichen ganz rechts aus dem Wert extrahieren.
Syntax:
= RECHTS (Text, Anzahl_Zeichen)- Text ist die Zelle oder der Wert, aus dem Sie Zeichen extrahieren möchten.
- num_chars ist die Anzahl der Zeichen, die aus dem Text extrahiert werden sollen. Wird dieses Argument nicht angegeben, wird nur das erste Zeichen extrahiert.
Für diese Methode verketten wir die maximale Anzahl von Nullen mit dem Zellbezug, der die Zeichenfolge im Argument "text" enthält.
Um eine 6-stellige Zahl basierend auf der Zahlenfolge in A mit führenden Nullen zu erstellen, versuchen Sie diese Formel:
=RECHTS("0000000"&A2,6)Das erste Argument (Text) der Formel fügt dem Wert in A2 („0000000“&A2) 7 Nullen hinzu und gibt dann die ganz rechten 7 Zeichen zurück, was zu einigen führenden Nullen führt.
Hinzufügen führender Nullen mit benutzerdefinierter Zahlenformatierung
Wenn Sie eine der oben genannten Methoden verwenden, um führende Nullen vor den Zahlen zu setzen, erhalten Sie immer eine Textzeichenfolge, keine Zahl. Und sie werden in Berechnungen oder in numerischen Formeln nicht viel nützlich sein.
Die beste Möglichkeit, führende Nullen in Excel hinzuzufügen, besteht darin, eine benutzerdefinierte Zahlenformatierung anzuwenden. Wenn Sie führende Nullen hinzufügen, indem Sie der Zelle ein benutzerdefiniertes Zahlenformat hinzufügen, ändert dies nicht den Wert der Zelle, sondern nur die Art und Weise, wie sie angezeigt wird. Der Wert bleibt weiterhin eine Zahl, kein Text.
Gehen Sie folgendermaßen vor, um die Zahlenformatierung von Zellen zu ändern:
Wählen Sie die Zelle oder den Zellbereich aus, in der führende Nullen angezeigt werden sollen. Klicken Sie dann mit der rechten Maustaste auf eine beliebige Stelle innerhalb des ausgewählten Bereichs und wählen Sie die Option "Zellen formatieren" aus dem Kontextmenü. Oder drücken Sie die Tastenkombination Strg + 1.
Gehen Sie im Fenster „Zellen formatieren“ zur Registerkarte „Zahl“ und wählen Sie „Benutzerdefiniert“ unter den Kategorieoptionen.
Geben Sie die Anzahl der Nullen in das Feld „Typ:“ ein, um die Gesamtzahl der Ziffern anzugeben, die Sie in einer Zelle anzeigen möchten. Wenn die Nummer beispielsweise 6-stellig sein soll, geben Sie „000000“ als benutzerdefinierten Formatcode ein. Klicken Sie dann auf "OK", um sich zu bewerben.
Dadurch werden führende Nullen vor den Zahlen angezeigt und wenn die Zahl weniger als 6 Stellen hat, wird eine Null davor aufgefüllt.
Zahlen scheinen nur führende Nullen zu haben, während der zugrunde liegende Wert unverändert bleibt. Wenn Sie eine Zelle mit benutzerdefinierter Formatierung auswählen, wird die ursprüngliche Zahl in der Bearbeitungsleiste angezeigt
Es gibt viele digitale Platzhalter, die Sie in Ihrem benutzerdefinierten Zahlenformat verwenden können. Es gibt jedoch nur zwei primäre Platzhalter, die Sie verwenden können, um führende Nullen in Zahlen hinzuzufügen.
- 0 – Es ist der Ziffernplatzhalter, der zusätzliche Nullen anzeigt. Es werden die erzwungenen Ziffern 0-9 angezeigt, unabhängig davon, ob die Ziffer für den Wert relevant ist oder nicht. Wenn Sie beispielsweise 2.5 mit dem Formatcode 000.00 eingeben, wird 002.50 angezeigt.
- # – Es ist der Ziffernplatzhalter, der optionale Ziffern anzeigt und keine zusätzlichen Nullen enthält. Wenn Sie beispielsweise 123 mit dem Formatierungscode 000# eingeben, wird 0123 angezeigt.
Außerdem werden alle Satzzeichen oder andere Zeichen, die Sie in den Formatcode einfügen, unverändert angezeigt. Sie können Zeichen wie Bindestrich (-), Komma (,), Schrägstrich (/) usw. verwenden.
Sie können beispielsweise auch Zahlen als Telefonnummern formatieren, indem Sie das benutzerdefinierte Format verwenden.
Das Dialogfeld Code formatieren im Dialogfeld Zellen formatieren:
Das Ergebnis:
Wenden wir diesen Formatierungscode im folgenden Beispiel an:
##0000
Wie Sie sehen können, fügt ‚0‘ zusätzliche Nullen hinzu, während ‚#‘ keine unbedeutenden Nullen hinzufügt:
Sie können auch vordefinierte Formatcodes im Abschnitt „Sonderformate“ des Dialogfelds Zellen formatieren für Postleitzahlen, Telefonnummern und Sozialversicherungsnummern verwenden.
Die folgende Tabelle zeigt Zahlen mit führenden Nullen, bei denen unterschiedliche „Spezial“-Formatcodes auf verschiedene Spalten angewendet werden:
Entfernen führender Nullen in Excel
Jetzt haben Sie gelernt, wie Sie in Excel führende Nullen hinzufügen. Sehen wir uns an, wie Sie die führenden Nullen aus der Anzahl der Zeichenfolgen entfernen. Wenn Sie Daten aus einer externen Quelle importieren, können Zahlen manchmal Nullen haben und als Text formatiert werden. In solchen Fällen müssen Sie die führenden Nullen entfernen und wieder in Zahlen umwandeln, damit Sie sie in Formeln verwenden können.
Es gibt verschiedene Möglichkeiten, führende Nullen in Excel zu entfernen, und wir werden sie nacheinander sehen.
Entfernen Sie führende Nullen, indem Sie die Zellenformatierung ändern
Wenn führende Nullen durch benutzerdefinierte Zahlenformatierung hinzugefügt wurden, können Sie sie einfach entfernen, indem Sie das Format der Zellen ändern. Sie können anhand der Adressleiste erkennen, ob Ihre Zellen benutzerdefiniert formatiert sind (Nullen sind in der Zelle sichtbar, nicht in der Adressleiste).
Um die vorangestellten Nullen zu entfernen, wählen Sie die Zellen mit den führenden Nullen aus, klicken Sie auf das Feld „Zahlenformat“ und wählen Sie die Formatierungsoption „Allgemein“ oder „Zahl“ aus.
Jetzt sind die führenden Nullen weg:
Löschen Sie führende Nullen, indem Sie den Text in Zahlen umwandeln
Wenn Ihre führenden Nullen durch Ändern des Zellenformats oder durch Hinzufügen von Apostrophen vor den Zahlen oder automatisch beim Importieren von Daten hinzugefügt wurden, können Sie sie am einfachsten in Zahlen umwandeln, indem Sie die Option zur Fehlerprüfung verwenden. So machen Sie es:
Sie können diese Methode verwenden, wenn Ihre Zahlen linksbündig ausgerichtet sind und Ihre Zellen ein kleines grünes Dreieck (einen Fehlerindikator) in der oberen linken Ecke der Zellen haben. Das bedeutet, dass die Zahlen als Text formatiert sind.
Wählen Sie diese Zellen aus und Sie sehen eine gelbe Warnung oben rechts in der Auswahl. Klicken Sie dann im Dropdown-Menü auf die Option "In Zahl umwandeln".
Ihre Nullen werden entfernt und die Zahlen werden wieder in das Zahlenformat (rechtsbündig) umgewandelt.
Entfernen führender Nullen um Multiplizieren/Dividieren mit 1
Eine andere einfache und beste Möglichkeit, führende Zahlen zu entfernen, besteht darin, die Zahlen mit 1 zu multiplizieren oder zu dividieren. Das Teilen oder Multiplizieren des Werts ändert den Wert nicht, sondern wandelt den Wert einfach zurück in eine Zahl und entfernt die führenden Nullen.
Geben Sie dazu die Formel aus dem folgenden Beispiel in eine Zelle ein und drücken Sie die EINGABETASTE. Die führenden Nullen werden entfernt und der String wieder in eine Zahl umgewandelt.
Wenden Sie diese Formel dann mithilfe des Ausfüllkästchens auf andere Zellen an.
Sie können die gleichen Ergebnisse erzielen, indem Sie den Befehl "Spezial einfügen" verwenden. Hier ist wie:
Geben Sie den numerischen Wert „1“ in eine Zelle ein (sagen wir in B2) und kopieren Sie diesen Wert.
Wählen Sie als Nächstes die Zellen aus, in denen Sie die führenden Nullen entfernen möchten. Klicken Sie dann mit der rechten Maustaste auf die Auswahl und wählen Sie dann die Option "Spezial einfügen".
Wählen Sie im Dialogfeld Inhalte einfügen unter Vorgang entweder die Option „Multiplizieren“ oder „Teilen“ und klicken Sie auf „OK“.
Das war's, Ihre führenden Nullen werden entfernt und die Strings bleiben als Zahlen übrig.
Entfernen Sie führende Nullen mithilfe einer Formel
Eine andere einfache Möglichkeit zum Löschen von Nullen mit Präfix ist die Verwendung der VALUE-Funktion. Diese Methode kann nützlich sein, unabhängig davon, ob Ihre führenden Nullen mithilfe einer anderen Formel oder eines Apostrophs oder durch eine benutzerdefinierte Formatierung hinzugefügt wurden.
=WERT(A1)Das Argument der Formel kann ein Wert oder der Zellbezug sein, der den Wert enthält. Die Formel entfernt die führenden Nullen und wandelt den Wert von Text in Zahl um. Wenden Sie dann die Formel auf die restlichen Zellen an.
Manchmal möchten Sie möglicherweise die führenden Nullen entfernen, die Zahlen jedoch im Textformat beibehalten. In solchen Fällen müssen Sie die Funktionen TEXT() und VALUE() wie folgt zusammen verwenden:
=TEXT(WERT(A1),"#")Die Funktion WERT wandelt den Wert in A1 in eine Zahl um. Aber das zweite Argument, '#', wandelt den Wert ohne zusätzliche Nullen zurück in das Textformat. Als Ergebnis erhalten Sie die Zahlen ohne führende Nullen, aber immer noch im Textformat (linksbündig).
Entfernen Sie führende Nullen mit der Text-to-Spalten-Funktion von Excel
Eine weitere Möglichkeit, führende Nullen zu entfernen, besteht darin, die Text-in-Spalten-Funktion von Excel zu verwenden.
Wählen Sie den Zellbereich aus, der die Zahlen mit führenden Nullen enthält.
Wechseln Sie als Nächstes zur Registerkarte „Daten“ und klicken Sie in der Gruppe „Datentools“ auf die Schaltfläche „Text in Spalten“.
Der Assistent „Text in Spalten konvertieren“ wird angezeigt. Wählen Sie in Schritt 1 von 3 „Getrennt“ und klicken Sie auf „Weiter“.
Deaktivieren Sie in Schritt 2 von 3 alle Trennzeichen und klicken Sie auf "Weiter".
Belassen Sie im letzten Schritt die Option Spaltendatenformat auf „Allgemein“ und wählen Sie das Ziel (die erste Zelle des Bereichs) aus, in dem Sie Ihre Zahlen ohne führende Nullen haben möchten. Klicken Sie dann auf „Fertig stellen“
Und Sie erhalten die Zahlen mit entferntem führenden in einer separaten Spalte, wie unten gezeigt.
Das ist alles.