Mit der TEXT-Funktion können Sie durch das Anwenden einer Formatierung mithilfe von Formatcodes die Anzeige von Zahlen ändern. Diese Funktion ist in solchen Fällen nützlich, in denen Sie Zahlen in einem besser lesbaren Format anzeigen oder diese mit Text oder Symbolen kombinieren möchten.
Hinweis: Die TEXT-Funktion konvertiert Zahlen in Text, wodurch der Bezug auf diese Zahlen bei späteren Berechnungen schwierig werden kann. Am besten behalten Sie den ursprünglichen Wert in einer Zelle bei und verwenden die TEXT-Funktion in einer anderen Zelle. Wenn Sie dann andere Formeln bilden müssen, können Sie immer Bezug auf den ursprünglichen Wert und nicht das Ergebnis der TEXT-Funktion nehmen.
Syntax
TEXT(Wert;Textformat)
Die Syntax der TEXT-Funktion weist die folgenden Argumente auf:
|
Argumentname |
Beschreibung |
|
Wert |
Ein numerischer Wert, der in Text umgewandelt werden soll |
|
Textformat |
Eine Textzeichenfolge, in der die Formatierung definiert ist, die auf den angegebenen Wert angewendet werden soll |
Übersicht
In ihrer einfachsten Form besagt die TEXT-Funktion Folgendes:
-
TEXT(Wert, den Sie formatieren möchten;"Formatcode, der angewendet werden soll")
Nachstehend finden Sie einige gängige Beispiele, die Sie direkt in Excel kopieren und dann selbst damit experimentieren können. Achten Sie darauf, dass die Formatcodes in Anführungszeichen stehen müssen.
|
Formel |
Beschreibung |
|
=TEXT(1234,567;"#.##0,00 €") |
Währung mit Tausendertrennzeichen und 2 Dezimalstellen, z. B. 1.234,57 €. Beachten Sie, dass Excel den Wert auf 2 Dezimalstellen rundet. |
|
=TEXT(HEUTE();"TT.MM.JJ") |
Aktuelles Datum im Format TT.MM.JJ, z. B. 14.03.12 |
|
=TEXT(HEUTE();"TTTT") |
Aktueller Wochentag, z. B. Montag |
|
=TEXT(JETZT();"H:MM AM/PM") |
Aktuelle Uhrzeit, z. B. 1:29 PM |
|
=TEXT(0,285;"0,0 %") |
Prozentsatz, z. B. 28,5 % |
|
=TEXT(4,34;"# ?/?") |
Bruch, z. B. 4 1/3 |
|
=GLÄTTEN(TEXT(0,34;"# ?/?")) |
Bruch, z. B. 1/3 Mit der GLÄTTEN-Funktion wird in diesem Fall das führende Leerzeichen entfernt, das durch den Dezimalwert entsteht. |
|
=TEXT(12200000;"0,00E+00") |
Wissenschaftliche Schreibweise, z. B. 1,22E+07 |
|
=TEXT(1234567898;"[<=9999999]###-####;(###) ###-####") |
Sonderformat (Telefonnummer), z. B. (123) 456-7898 |
|
=TEXT(1234;"0000000") |
Hinzufügen von führenden Nullen (0), z. B. 0001234 |
|
=TEXT(123456;"##0° 00' 00''") |
Benutzerdefiniert – Breiten-/Längengrad |
Hinweis: Auch wenn Sie mit der TEXT-Funktion die Formatierung ändern können, ist dies nicht die einzige Möglichkeit. Sie können das Format auch ohne eine Formel ändern. Drücken Sie dazu STRG+1 (oder 
+1 auf einem Mac), und wählen Sie dann im Dialogfeld Zellen formatieren die Registerkarte Zahlen aus.
Herunterladen unserer Beispiele
Sie können eine Beispielarbeitsmappe mit sämtlichen Beispielen zur TEXT-Funktion, die Sie in diesem Artikel finden, sowie weiteren Beispielen herunterladen. Sie können sich nach diesen Beispielen richten oder eigene Formatcodes für die TEXT-Funktion erstellen.
Herunterladen von Beispielen für die TEXT-Funktion in Excel
Weitere verfügbare Formatcodes
Im Dialogfeld Zellen formatieren können Sie weitere verfügbare Formatcodes finden:
-
Drücken Sie STRG+1 (
+1 auf einem Mac), um das Dialogfeld Zellen formatieren aufzurufen. -
Wählen Sie auf der Registerkarte Zahlen das gewünschte Format aus.
-
Wählen Sie die Option Benutzerdefiniert aus.
-
Der gewünschte Formatcode wird jetzt im Feld Typ angezeigt. Wählen Sie in diesem Fall im Feld Typ alles außer Komma (,) und @-Symbol aus. Im folgenden Beispiel wurde nur TT.MM.JJJJ markiert und kopiert.
-
Drücken Sie STRG+C, um den Formatcode zu kopieren, und drücken Sie dann Abbrechen, um das Dialogfeld Zellen formatieren zu schließen.
-
Jetzt brauchen Sie nur noch STRG+V zu drücken, um den Formatcode in Ihre TEXT-Formel einzufügen, z. B.: =TEXT(B2;"TT.MM.JJJJ"). Denken Sie daran, den Formatcode in Anführungszeichen ("Formatcode") zu setzen, da Excel sonst eine Fehlermeldung ausgibt.
Formatcodes nach Kategorie
Nachstehend finden Sie einige Beispiele für die Anwendung unterschiedlicher Zahlenformate auf Ihre Werte. Verwenden Sie dazu das Dialogfeld Zellen formatieren und dann die Option Benutzerdefiniert, um diese Formatcodes in Ihre TEXT-Funktion zu kopieren.
Warum löscht Excel führende Nullen?
Excel ist darauf ausgelegt, in Zellen nach Zahlen und nicht nach Zahlen, die wie Text aussehen (z. B. Teile- oder Artikelnummern), zu suchen. Um führende Nullen beizubehalten, formatieren Sie vor dem Einfügen oder Eingeben von Werten den Eingabebereich als Text. Markieren Sie die Spalte oder den Bereich, in die bzw. den Sie die Werte eingeben möchten, drücken Sie STRG+1, um das Dialogfeld Zellen formatieren aufzurufen, und wählen Sie auf der Registerkarte Zahlen die Option Text aus. Jetzt behält Excel die führenden Nullen bei.
Wenn Excel nach der Eingabe von Daten führende Nullen entfernt hat, können Sie diese mithilfe der TEXT-Funktion wieder hinzufügen. Sie können auf die oberste Zelle mit den Werten verweisen und =TEXT(Wert;"00000") verwenden, wobei die Anzahl der Nullen in der Formel der Anzahl der gewünschten Zeichen entsprechen muss. Kopieren Sie dann den übrigen Bereich, und fügen Sie ihn ein.
Wenn die Textwerte aus irgendeinem Grund wieder in Zahlen umgewandelt werden müssen, können Sie mit 1 multiplizieren, z. B. =D4*1 oder den doppelt-unären Operator (--) verwenden, z. B. =--D4.
In Excel werden Tausender durch Punkte getrennt, wenn das Format einen Punkt (.) enthält, der von Nummernzeichen (#) oder Nullen eingeschlossen ist. Mit der Formatzeichenfolge "#.###" beispielsweise zeigt Excel die Zahl 12200000 als 12.200.000 an.
Ein Punkt, der einem Platzhalter für Ziffern folgt, gibt ein Vielfaches von Tausend an. Mit der Formatzeichenfolge "#.###,0." beispielsweise zeigt Excel die Zahl 12200000 als 12.200,0 an.
Hinweise:
-
Das Tausendertrennzeichen hängt von den Ländereinstellungen ab. In den USA ist es ein Komma, in anderen Gebietsschemas ein Punkt (.).
-
Das Tausendertrennzeichen ist für Zahlen-, Währungs- und Buchhaltungsformate verfügbar.
Im Folgenden finden Sie Beispiele für Standardzahlenformate (nur Tausendendertrennzeichen und Dezimalzahlen) sowie Währungs- und Buchhaltungsformate. Währungsformate können Sie um das gewünschte Währungssymbol ergänzen und dieses neben dem Wert ausrichten, während bei Buchhaltungsformaten das Währungssymbol und die Dezimalstellen in der Spalte ausgerichtet werden. Achten Sie unten auf die Unterschiede zwischen Währungs- und Buchhaltungsformat. Beim Buchhaltungsformat wird ein nachgestelltes Leerzeichen für die Trennung zwischen Symbol und Wert verwendet.
Wenn Sie nach dem Formatcode für ein Währungssymbol suchen, drücken Sie zuerst STRG+1 (oder +1 auf einem Mac), wählen Sie dann das gewünschte Format und abschließend in der Dropdownliste Symbol das Symbol aus:
Klicken Sie dann links neben dem AbschnittKategorie auf Benutzerdefiniert, und kopieren Sie den Formatcode einschließlich des Währungssymbols.
Hinweis: Die TEXT-Funktion unterstützt keine Farbformatierung. Wenn Sie also einen Zahlenformatcode aus dem Dialogfeld "Zellen formatieren" kopieren, der eine Farbe enthält, z. B. "#.##0,00 €;[Rot]-#.##0,00 €", akzeptiert die TEXT-Funktion zwar den Formatcode, zeigt die Farbe aber nicht an.
Sie können die Anzeige eines Datums ändern und eine Kombination aus "T" für Tag, "M" für Monat und "J" für Jahr verwenden.
Bei den Formatcodes der TEXT-Funktion wird die Groß- und Kleinschreibung nicht beachtet. Sie können also "T" oder "t", "M" oder "m", "J" oder "j" verwenden.
|
|
Mynda empfiehlt... Wenn Sie Excel-Dateien und -Berichte mit Benutzern aus verschiedenen Ländern teilen, möchten Sie diese möglicherweise in der jeweiligen Sprache zur Verfügung stellen. Excel MVP, Mynda Treacy zeigt im Artikel Anzeigen von Excel-Datumsangaben in unterschiedlichen Sprachen hierfür eine großartige Lösung auf. Er umfasst auch eine Beispielarbeitsmappe, die Sie herunterladen können. |
Sie können die Anzeige der Uhrzeit ändern und eine Kombination aus "H" für Stunden, "M" für Minuten oder "S" für Sekunden und "AM/PM" für die Anzeige im 12-Stunden-Format verwenden.
Wenn Sie "AM/PM" bzw. "A/P" weglassen, wird die Uhrzeit im 24-Stunden-Format angezeigt.
Bei den Formatcodes der TEXT-Funktion wird die Groß- und Kleinschreibung nicht beachtet. Sie können also "H" oder "h", "M" oder "m", "S" oder "s" benutzen.
Sie können die Anzeige von Dezimalwerten in Prozentsätze (%) ändern.
Sie können die Anzeige von Dezimalwerten in Brüche (?/?) ändern.
Mit der wissenschaftlichen Schreibweise können Zahlen als Dezimalzahl zwischen 1 und 10 mit einer Zehnerpotenz angegeben werden. Diese Schreibweise wird häufig zum Verkürzen der Anzeige großer Zahlen verwendet.
Excel enthält bereits einige Sonderformate:
-
Postleitzahl – "00000"
-
Postleitzahl D – "D-00000"
-
Telefonnummer – "[<=9999999]###-####;(###) ###-####"
-
Sozialversicherungsnummer – "0000-00 00 00"
Die Sonderformate hängen vom Gebietsschema ab. Wenn es für Ihr Gebietsschema keine Sonderformate gibt oder die Sonderformate Ihren Anforderungen nicht entsprechen, können Sie im Dialogfeld Zellen formatieren unter Benutzerdefiniert eigene Sonderformate erstellen.
Allgemeines Szenario
Die TEXT-Funktion wird selten allein, sondern meist in Verbindung mit anderen Angaben verwendet. Angenommen, Sie möchten Text und einen Zahlenwert miteinander kombinieren, wie beispielsweise "Bericht gedruckt am 14.03.12" oder "Wochenumsatz: $66.348,72". Sie könnten dies manuell in Excel eingeben, was jedoch wenig Sinn macht, da Excel diese Aufgabe für Sie erledigen kann. Leider weiß Excel beim Kombinieren von Text und formatierten Zahlen wie Datumsangaben, Uhrzeiten, Währung usw. nicht, wie diese angezeigt werden sollen, und verwirft daher das Zahlenformat. An diesem Punkt ist die TEXT-Funktion von unschätzbarem Wert, da Sie mit dieser Funktion Excel zwingen können, die Werte nach Ihren Wünschen zu formatieren, indem Sie beispielsweise einen Formatcode wie "TT.MM.JJ" als Datumsformat verwenden.
Im folgenden Beispiel wird gezeigt, was passiert, wenn Sie Text und Zahl ohne die TEXT-Funktion zu kombinieren versuchen. In diesem Fall wird das kaufmännische Und-Zeichen (&) verwendet, um eine Textzeichenfolge, ein Leerzeichen und einen Wert mit =A2&" "&B2 zu verketten.
Wie Sie sehen, hat Excel die Formatierung des Datums in Zelle B2 entfernt. Aus dem nächsten Beispiel geht hervor, wie Sie mit der TEXT-Funktion das gewünschte Format anwenden.
Die aktualisierte Formel lautet:
-
Zelle C2:=A2&" "&TEXT(B2;"TT.MM.JJ") – Datumsformat
Häufig gestellte Fragen
Leider ist das mit der TEXT-Funktion nicht möglich. Dazu müssen Sie VBA-Code (Visual Basic for Applications) verwenden. Der folgende Link verweist auf eine Methode: Konvertieren eines numerischen Werts in englische Wörter in Excel
Ja, aber dafür sind ein paar Schritte erforderlich. Wählen Sie zunächst die entsprechende Zelle oder die Zellen aus, und verwenden Sie STRG+1,um das Dialogfeld Zellen formatieren zu öffnen, und aktivieren Sie dann unter Ausrichtung > Textsteuerung die Option Zeilenumbruch. Passen Sie als Nächstes die fertige TEXT-Funktion an. Fügen Sie an der Stelle, an welcher der Zeilenumbruch erfolgen soll, die ASCII-Funktion ZEICHEN(10) ein. Möglicherweise müssen Sie die Spaltenbreite an die Ausrichtung des Endergebnisses anpassen.
In diesem Fall wurde Folgendes verwendet: ="Heutiges Datum: "&ZEICHEN(10)&TEXT(HEUTE();"TT.MM.JJ")
Dies ist die sogenannte wissenschaftliche Schreibweise. Excel konvertiert automatisch Zahlen mit mehr als 12 Ziffern, wenn eine Zelle als Allgemein formatiert ist, und mit mehr als 15 Ziffern, wenn eine Zelle als Zahl formatiert ist. Wenn Sie lange numerische Zeichenfolgen eingeben müssen, aber nicht möchten, dass diese konvertiert werden, formatieren Sie die fraglichen Zellen als Text, bevor Sie Werte in Excel eingeben oder einfügen.
|
|
Mynda empfiehlt... Wenn Sie Excel-Dateien und -Berichte mit Benutzern aus verschiedenen Ländern teilen, möchten Sie diese möglicherweise in der jeweiligen Sprache zur Verfügung stellen. Excel MVP, Mynda Treacy zeigt im Artikel Anzeigen von Excel-Datumsangaben in unterschiedlichen Sprachen hierfür eine großartige Lösung auf. Er umfasst auch eine Beispielarbeitsmappe, die Sie herunterladen können. |
Siehe auch
Erstellen oder Löschen eines benutzerdefinierten Zahlenformats
