So fügen Sie in Excel eine Null vor einer Zahl hinzu - Excel-Tipps

Inhaltsverzeichnis:

Anonim


In diesem Artikel lernen wir verschiedene Möglichkeiten zum Hinzufügen von Nullen (0s) vor in Excel kennen.

Szenario:

Hinzufügen von Null vor der Zahl in Excel. Standardmäßig verwendet Excel keine Nullen vor der Zahl in der Zelle. Haben Sie schon einmal versucht, Daten wie 000123 in Excel einzugeben? Sie werden wahrscheinlich schnell feststellen, dass Excel die führenden Nullen automatisch aus der Eingabenummer entfernt. Dies kann sehr ärgerlich sein, wenn Sie diese führenden Nullen in den Daten haben möchten und nicht wissen, wie Sie Excel dazu bringen können, sie beizubehalten. Glücklicherweise gibt es einige Möglichkeiten, Ihre Zahlen am Anfang mit Nullen aufzufüllen. In diesem Artikel erkläre ich verschiedene Möglichkeiten, diese führenden Nullen zu Ihren Zahlen hinzuzufügen oder beizubehalten.

Verschiedene Funktionen zum Hinzufügen von Null (0) vorne

Wie wir wissen, ist die TEXT-Funktion die am häufigsten verwendete Funktion, um 0 voranzustellen, aber hier erkläre ich weitere Möglichkeiten, Nullen (0s) zu Zahlen hinzuzufügen.

  1. TEXT-Funktion
  2. RECHTE Funktion
  3. BASE-Funktion
  4. 0 in Pivot-Tabellenwerten hinzufügen

Als Text formatieren

Eine der Lösungen besteht darin, das Format der Zahl in Text zu konvertieren, denn wenn wir 00xyz eingeben, betrachtet Excel diesen Wert als Text und behält seine 0 voran. Es gibt verschiedene Möglichkeiten, eine Zahlenzelle in das Textformat zu konvertieren.

  • Verwenden des benutzerdefinierten Formats
  • Verwenden der TEXT-Funktion
  • Verwenden von Apostroph ( ' ) am Anfang des Werts während der Eingabe

Benutzerdefiniertes Format

Lassen Sie uns diese Methoden nacheinander verstehen. Die Verwendung eines benutzerdefinierten Formats zum Ändern des Formats ist der einfachste Weg, um die bereits vorhandenen Zahlen im Bereich zu ändern. Wählen Sie einfach den Bereich und verwenden Sie die Tastenkombination Strg + 1 oder wählen Sie Mehr Zahlenformat aus der Dropdown-Liste auf der Registerkarte Start, die Allgemein anzeigt.

Ändern Sie das Format von Allgemein in Text.

  1. Wählen Sie den Zellbereich aus, in den Sie führende Nullen eingeben möchten.
  2. Wechseln Sie zur Registerkarte Startseite.
  3. Klicken Sie im Abschnitt Zahlen auf die Auswahl Dropdown-Format formatieren.
  4. Wählen Sie Text aus den Formatoptionen.

Wenn Sie nun versuchen, Zahlen mit führenden Nullen einzugeben, verschwinden diese nicht, da sie als Textwerte anstelle von Zahlen eingegeben werden. Excel-Benutzer können eine benutzerdefinierte Formatierung hinzufügen, um Zahlen mit führenden Nullen zu formatieren.

Sie scheinen jedoch nur führende Nullen zu haben. Die zugrunde liegenden Daten werden mit den hinzugefügten Nullen nicht in Text umgewandelt.

Fügen Sie ein benutzerdefiniertes Format hinzu, um führende Nullen anzuzeigen.

  1. Wählen Sie den Zellbereich aus, dem Sie führende Nullen hinzufügen möchten, und öffnen Sie das Dialogfeld Zellen formatieren.
    • Klicken Sie mit der rechten Maustaste und wählen Sie Zellen formatieren.
    • Verwenden Sie die Tastenkombination Strg + 1.
  2. Wechseln Sie zur Registerkarte Nummer.
  3. Wählen Sie Benutzerdefiniert aus den Kategorieoptionen.
  4. Fügen Sie in der Typeingabe ein neues benutzerdefiniertes Format hinzu. Wenn Sie möchten, dass die Gesamtzahl der Ziffern einschließlich aller führenden Nullen 6 beträgt, fügen Sie 000000 als benutzerdefiniertes Format hinzu.
  5. Drücken Sie die OK-Taste.

TEXT-Funktion

Mit der TEXT-Funktion können Sie eine benutzerdefinierte Formatierung auf alle Zahlendaten anwenden, die sich bereits in Ihrer Tabelle befinden.

= TEXT ( Wert, Format)

  • Wert – Dies ist der Wert, den Sie in Text konvertieren und formatieren möchten.
  • Format - Dies ist die anzuwendende Formatierung.

= TEXT ( B3, "000000" )

Wenn Sie einer Zahl in Zelle B3 Nullen hinzufügen möchten, sodass die Gesamtzahl der Ziffern 6 beträgt, können Sie die obige Formel verwenden.

Verwenden von Apostroph ( ' ) in start

Sie können Excel zwingen, eine Zahl als Text einzugeben, indem Sie einen führenden Apostroph verwenden.

Dies bedeutet, dass Sie diese Nullen bei der Eingabe Ihrer Daten im Vordergrund behalten können.

Diese Methode ist schnell und einfach bei der Dateneingabe. Geben Sie einfach ein '-Zeichen vor einer beliebigen Zahl ein. Dadurch wird Excel mitgeteilt, dass die Daten Text und keine Zahl sein sollen.

Wenn Sie die Eingabetaste drücken, bleiben die führenden Nullen im Arbeitsblatt sichtbar. Das ' wird im Arbeitsblatt nicht sichtbar, ist aber immer noch vorhanden und kann in der Bearbeitungsleiste angezeigt werden, wenn sich der aktive Zellencursor auf der Zelle befindet.

RECHTE Funktion

Eine andere Möglichkeit, Ihre Nullen mit einer Formel voranzubringen, besteht darin, die RECHTE Funktion zu verwenden. Sie können eine Reihe von Nullen mit der Zahl verketten und dann die Extras mit der RECHTEN Funktion abschneiden.

Die Funktion RECHTS extrahiert die N Zeichen ganz rechts aus einem Textwert.

= RECHTS ( Text, [Zahl])

  • Text - Dies ist der Text, aus dem Sie Zeichen extrahieren möchten.
  • Nummer (Optional)- Dies ist die Anzahl der Zeichen, die aus dem Text extrahiert werden sollen. Wird dieses Argument nicht eingegeben, wird nur das erste Zeichen extrahiert.

= RECHTS ( "000000" & B3, 6 )

Die obige Formel verkettet mehrere Nullen an den Anfang einer Zahl in Zelle B3 und gibt dann die ganz rechten 6 Zeichen zurück, was zu einigen führenden Nullen führt.

BASE-Funktion

Sie werden feststellen, dass dieser Artikel 9 Möglichkeiten zum Hinzufügen führender Nullen beschreibt, aber mein YouTube-Video zeigt nur 8 Möglichkeiten.

Das liegt daran, dass ich nicht wusste, dass Sie die BASE-Funktion verwenden können, um führende Nullen hinzuzufügen, bis es jemand in den Videokommentaren erwähnt hat.

Mit der Funktion BASIS können Sie eine Zahl mit einer gegebenen Basis in eine Textdarstellung umwandeln.

Die Zahlen, die Sie normalerweise verwenden, sind die Basis 10, aber Sie können diese Funktion verwenden, um eine Zahl zur Basis 10 in eine (binäre) Darstellung zur Basis 2 umzuwandeln.

= BASIS (Zahl, Basis, [MinLength])

  • Zahl - Dies ist die Zahl, die Sie in einen Textwert in einer anderen Basis umwandeln möchten.
  • Basis - Dies ist die Basis, in die Sie den Wert umwandeln möchten.
  • Minimale Länge (Optional) - Dies ist die Mindestlänge der Zeichen des konvertierten Textwerts.

Verwenden Sie die Formel

=BASIS(B3,10,6)

Die obige Formel wandelt eine Zahl in Zelle B3 in die Basis 10 (es ist bereits eine Zahl zur Basis 10, aber dies behält sie als Basis 10) und konvertiert sie in einen Textwert mit mindestens 6 Zeichen. Wenn der konvertierte Wert weniger als 6 Zeichen umfasst, wird der Wert mit Nullen aufgefüllt, um mindestens 6 Zeichen zu erhalten.

Definitiv eine coole neue Verwendung für eine Funktion, die ich sonst nie nutze.

Fügen Sie Null (0) nur vor Zahlen hinzu, nicht bei Textwerten

Hier betrachten wir das Szenario, das sowohl Text- als auch Zahlenwerte in derselben Liste abdeckt. Excel betrachtet jetzt jede Zelle als unterschiedlich in Bezug auf das Format. Nun, wie kann man Excel wissen lassen, dass, wenn die Zelle einen Zahlenwert hat, 0 vorangestellt wird oder wenn die Zelle Text oder einen anderen Wert hat, dann belassen Sie es so, wie es ist.

Dazu verwenden wir eine Kombination aus zwei Funktionen IF und ISNUMBER Funktion. Hier haben wir einige Werte zum Anprobieren.

Die generische Formel geht weiter wie

=IF(ISNUMBER(cell_ref),"0"&cell_ref, cell_ref)

Hier

cell_ref : Zellbezug der entsprechenden Zelle

Verwenden Sie die Formel in Zelle

=WENN(ISNUMMER(F3),"0"&F3, F3)

Erläuterung: Die Funktion ISNUMBER überprüft die Zelle auf Nummer. Wenn die Funktion überprüft, ob die Zelle eine Zahl hat, fügt sie Null vor der Zahl hinzu, sonst gibt sie nur den Zellenwert zurück.

Wie Sie im obigen Bild sehen können, wird die Null (0) vor der Zahl hinzugefügt. Lassen Sie uns überprüfen, ob diese Funktion bei anderen Werten funktioniert. Kopieren Sie die Formel in andere Zellen, wählen Sie die Zellen aus, die die erste Zelle enthalten, in der die Formel bereits angewendet wurde, verwenden Sie die Tastenkombination Strg+D

es wird den Wert mit Nullen auffüllen, um mindestens eine Null vor den Zeichen zu erhalten. Definitiv eine coole neue Verwendung für eine Funktion, die ich sonst nie nutze.

Berechnete Spalte von Power Pivot

Es gibt eine weitere Möglichkeit, Nullen in die Pivot-Tabelle einzufügen. Sie können sie einer berechneten Spalte in Power Pivot hinzufügen. Auf diese Weise können Sie die neue Spalte im Bereich Filter, Zeilen oder Spalte einer Pivot-Tabelle verwenden.

= FORMAT ( Zahlen[Zahl], "000000" )

Im Power Pivot-Add-In können Sie eine neue Spalte hinzufügen und die Funktion FORMAT verwenden, um führende Nullen in der Spalte mit einer Formel wie oben zu erstellen. Eine berechnete Spalte berechnet einen Wert für jede Zeile, sodass die Funktion nicht in eine Zelle eingeschlossen werden muss. Jetzt können Sie diese neue Spalte im Zeilenbereich einer Pivot-Tabelle wie jede andere Datenspalte verwenden.

Zusammenfassung

Es kann frustrierend sein, all Ihre Nullen verschwinden zu sehen, wenn wir nicht wissen, warum es passiert oder wie wir es verhindern können. Wie Sie sehen, gibt es viele Möglichkeiten, um sicherzustellen, dass all diese Nullen in Ihren Daten verbleiben. Was auch immer Ihre Anforderung ist, es gibt sicherlich eine gute Lösung für Sie.

Hier sind alle Beobachtungsnotizen mit der Formel in Excel
Anmerkungen :

  1. Die Formel kann sowohl für Texte als auch für Zahlen verwendet werden.
  2. Der Suchen- und Ersetzen-Text sollte in einer Tabelle sortiert werden. Da wir Indizes zum Suchen und Ersetzen verwenden, stellen Sie sicher, dass 'find'-Text denselben Index hat wie 'replace'-Text. Es ist besser, beide Listen nebeneinander zu haben, wie im zweiten Beispiel.
  3. Die generische Formel hat zwei SUBSTITUTE-Funktionen. Sie benötigen so viele Ersatzfunktionen, wie Sie ersetzen müssen.
  4. Platzhalter helfen beim Extrahieren von Werten aus den Teilzeichenfolgen.
  5. Die Funktion unterstützt logische Operatoren wie , , = aber diese werden mit doppelten Anführungszeichen verwendet ( " ) mit Argumenten. Wenn Sie jedoch einen Zellbezug für die Kriterien verwenden, können Anführungszeichen ignoriert werden.
  6. Kriterienargumente sollten Anführungszeichen (") für Texte (wie "Gillen" ) oder Zahlen (wie ">50" ) verwenden.

Ich hoffe, dieser Artikel über verschiedene Möglichkeiten zum Hinzufügen von Nullen (0s) in Excel ist erklärend. Weitere Artikel zum Berechnen von Werten und zugehörigen Excel-Formeln finden Sie hier. Wenn Ihnen unsere Blogs gefallen haben, teilen Sie sie mit Ihren Freunden auf Facebook. Und Sie können uns auch auf Twitter und Facebook folgen. Wir würden uns freuen, von Ihnen zu hören, lassen Sie uns wissen, wie wir unsere Arbeit verbessern, ergänzen oder erneuern und für Sie verbessern können. Schreiben Sie uns auf der E-Mail-Site.

Excel ERSETZEN vs. ERSATZ-Funktion: Die Funktionen REPLACE und SUBSTITUTE sind die am meisten missverstandenen Funktionen. Um einen bestimmten Text zu finden und zu ersetzen, verwenden wir die Funktion SUBSTITUTE. Wobei REPLACE verwendet wird, um eine Anzahl von Zeichen in einer Zeichenfolge zu ersetzen.

Text am Ende einer Zeichenfolge ersetzen, beginnend mit variabler Position: Um Text am Ende des Strings zu ersetzen, verwenden wir die REPLACE-Funktion. Die REPLACE-Funktion verwendet die Position des zu ersetzenden Texts in der Zeichenfolge.

So überprüfen Sie, ob eine Zeichenfolge einen von vielen Texten in Excel enthält: Um zu überprüfen, ob ein String einen von mehreren Texten enthält, verwenden wir diese Formel. Wir verwenden die SUM-Funktion, um alle Übereinstimmungen zu summieren, und führen dann eine Logik durch, um zu überprüfen, ob die Zeichenfolge eine der mehreren Zeichenfolgen enthält.

Zellen zählen, die bestimmten Text enthalten: Eine einfache ZÄHLENWENN-Funktion wird die Magie bewirken. Um die Anzahl mehrerer Zellen zu zählen, die eine bestimmte Zeichenfolge enthalten, verwenden wir den Platzhalteroperator mit der Funktion ZÄHLENWENN.

Populäre Artikel :

50 Excel-Kurzbefehle zur Steigerung Ihrer Produktivität : Erledigen Sie Ihre Aufgaben in Excel schneller. Diese Verknüpfungen helfen Ihnen, Ihre Arbeitseffizienz in Excel zu steigern.

So verwenden Sie die SVERWEIS-Funktion in Excel : Dies ist eine der am häufigsten verwendeten und beliebtesten Funktionen von Excel, die verwendet wird, um Werte aus verschiedenen Bereichen und Blättern zu suchen.

So verwenden Sie die IF-Funktion in Excel : Die IF-Anweisung in Excel überprüft die Bedingung und gibt einen bestimmten Wert zurück, wenn die Bedingung WAHR ist, oder einen anderen bestimmten Wert, wenn FALSE.

So verwenden Sie die SUMIF-Funktion in Excel : Dies ist eine weitere wesentliche Funktion des Dashboards. Dies hilft Ihnen, Werte unter bestimmten Bedingungen zusammenzufassen.

So verwenden Sie die ZÄHLENWENN-Funktion in Excel : Zählen Sie Werte mit Bedingungen mit dieser erstaunlichen Funktion. Sie müssen Ihre Daten nicht filtern, um bestimmte Werte zu zählen. Die Countif-Funktion ist unerlässlich, um Ihr Dashboard vorzubereiten.