Verwenden Sie die Excel-TEXT-Funktion, um beliebige Daten (z. B. Zahlen, Datumsangaben usw.) in Text in einem benutzerdefinierten Format umzuwandeln.
Die TEXT-Funktion wird als Zeichenfolgen-/Textfunktion kategorisiert, die einen Zahlenwert in eine Textzeichenfolge in einem benutzerdefinierten Format umwandelt. Wenn Sie beispielsweise ein Datum im Format '15.03.2020′ in das Format 15.03.2020 umwandeln möchten, verwenden Sie dazu die TEXT-Funktion.
In dieser Anleitung wird die Verwendung der TEXT-Funktion in Excel anhand einiger Formeln und Beispiele erläutert.
Syntax
Die allgemeine Syntax der TEXT-Funktion:
=TEXT(wert,format_text)Die TEXT-Funktion erfordert zwei Argumente/Parameter:
Wert– Der Zahlenwert, den Sie in eine Textzeichenfolge umwandeln möchten. Dieser Wert kann ein numerischer Wert, ein Datum oder ein Zellbezug eines Zahlenwerts sein.format_text– Der Formatcode, den Sie auf den spezifischen Wert anwenden möchten. Es sollte immer in doppelte Anführungszeichen eingeschlossen werden.
Formatcodes der TEXT-Funktion
Die Textfunktion hat nur zwei Argumente. Das erste Argument erfordert nur den Wert, den Sie konvertieren möchten, es ist einfach. Sie sollten jedoch den richtigen Formatcode einfügen, der Ihnen die Ausgabenummer im gewünschten Format gibt. Die folgende Tabelle enthält die gängigsten und am häufigsten verwendeten Formate.
| Code formatieren | Beschreibung | Beispiel |
|---|---|---|
0 | Null ist ein Ziffernplatzhalter, der nur Ziffern ohne Nachkommastellen anzeigt. | #.0 – zeigt immer 1 Dezimalstelle an. Wenn Sie 5,50 in die referenzierte Zelle eingeben, wird sie als 5,5 angezeigt. |
# | Zeigt Ziffer ohne zusätzliche Nullen an. | #.## – zeigt bis zu zwei Dezimalstellen an. Wenn Sie 3,777 eingeben, wird 3,78 zurückgegeben. |
? | Zeigt nur eine Ziffer ohne Dezimalstellen an. Es wird normalerweise verwendet, um numerische Werte in einer Spalte an einer Dezimalstelle auszurichten. | #.? – es zeigt eine Dezimalstelle an und richtet den Dezimalpunkt aus. |
. | Komma | |
, | Tausendertrennzeichen. | ###,### – Es wird ein Tausendertrennzeichen angezeigt. Wenn Sie 195200 eingeben, werden 195.200 . zurückgegeben |
0% | Zeigt Zahlen als Prozentsatz an. | Wenn Sie 0,285 eingeben, werden 28,5 zurückgegeben |
Zusätzlich zum obigen Formatcode können Sie auch eines der folgenden Symbole in den Formatcode der Formel einfügen, und diese werden genau wie eingegeben angezeigt.
| Symboll | Beschreibung |
|---|---|
+ und - | Plus- und Minuszeichen |
() | Linke und rechte Klammer |
: | Doppelpunkt |
^ | Caret |
' | Apostroph |
{} | Geschweifte Klammern |
<> | Kleiner-als- und Größer-als-Zeichen |
= | Gleichheitszeichen |
/ | Schrägstrich |
! | Ausrufezeichen |
& | Et-Zeichen |
~ | Tilde |
| Leerzeichen |
Formatcodes für Textfunktionen für Datum und Uhrzeit
Wenn Sie Datums- und Uhrzeitangaben mithilfe der TEXT-Funktion konvertieren möchten, verwenden Sie einen der folgenden Formatcodes.
| Code formatieren | Beschreibung und Beispiele |
|---|---|
D | Gibt den Tag des Monats in einer oder zweistelligen Zahl ohne führende Null an (z. B. 2 bis 25) |
dd | Gibt den Tag des Monats in einer zweistelligen Darstellung mit führender Null an (z. B. 02 bis 25) |
ddd | Gibt den Wochentag in einer dreibuchstabigen Abkürzung an (z. B. Mo bis So) |
dddd | Gibt den vollständigen Namen des Wochentags an. (z.B. Montag, Mittwoch) |
m | Gibt den Monat des Jahres in einer oder zweistelligen Zahl ohne führende Null an (z. B. 02 bis 12) |
mm | Gibt den Monat in einer zweistelligen Darstellung mit einer führenden Null an. (z. B. 01, 12) |
mmm | Gibt den Monat in einer dreibuchstabigen Abkürzung an (z. B. Jan, Nov) |
mmmm | Gibt den vollständigen Namen des Monats an. (z. B. Januar, November) |
yy | Gibt das Jahr in einer zweistelligen Zahl an (z. B. 08 bedeutet 2008, 19 bedeutet 2019) |
yyyy | Gibt das Jahr in einer vierstelligen Zahl an (z. B. 2008, 2019) |
h | Gibt die Stunde in ein- oder zweistelliger Darstellung ohne führende Null an (z. B. 6, 12) |
hh | Gibt die Stunde in zweistelliger Darstellung mit führender Null an (06 bis 12) |
m | Gibt Minuten in einer oder zweistelligen Zahl ohne führende Null an (z. B. 5, 45) |
mm | Gibt Minuten in einer oder zweistelligen Zahl eine führende Null an (z. B. 05, 45) |
S | Gibt Sekunden in einer oder zweistelligen Zahl ohne führende Null an (z. B. 5, 45) |
ss | Gibt Sekunden in einer oder zweistelligen Zahl eine führende Null an (z. B. 05, 45) |
BIN / PN | Gibt an, dass die Uhrzeit im 12-Stunden-Format angezeigt werden soll, gefolgt von „AM“ oder „PM“ |
So verwenden Sie die TEXT-Funktion in Excel
Sie haben die Syntax und Formatcodes der TEXT-Funktion kennengelernt. Lassen Sie uns nun anhand einiger Beispiele untersuchen, wie Sie die Funktion in Excel verwenden.
Verwenden Sie die Textformel, um die ganze Zahl bis zur Zahl in Zelle A1 anzuzeigen.
Dazu können wir die Textformel verwenden als:
=TEXT(A1,"0")
Verwenden Sie diese Formel, um eine einzelne Dezimalstelle anzuzeigen:
=TEXT(A1,"0.0")
Die folgende Tabelle zeigt die Textformeln mit unterschiedlichen Formatcodes, um unterschiedliche Formatierungstypen auf unterschiedliche numerische Werte anzuwenden. Sie können diese Formeln direkt in Ihre Tabelle kopieren, um sie selbst auszuprobieren.
| Wert | Formel | Formatierter Wert |
|---|---|---|
| 4963.34 | =TEXT(A2,"0.000") | 4963.340 |
| 5300.52 | =TEXT(A3,"#,##0") | 5,301 |
| 5.12 | =TEXT(A4,"# ?/?") | 5 1/8 |
| 0.4963 | =TEXT(A5,"#%") 50% | 50% |
| 9600.60 | =TEXT(A6,"$#,##0.0") | $9,600.6 |
| 20 | =TEXT(A7,"~#!") ~20! | ~20! |
| 5656 | =TEXT(A8,"00000000") | 00005656 |
Die obigen Formeln werden in Spalte C einer Tabelle angewendet, wie im folgenden Screenshot gezeigt:
TEXT-Funktion mit Formeln
Sie können auch andere Formeln und Funktionen sowohl innerhalb als auch außerhalb der TEXT-Funktion verwenden.
Nehmen wir an, Sie haben Brutto- und Ausgabenbetrag und möchten den Nettogewinn berechnen und den Gewinn in Zelle A9 mit der Zeichenfolge "Ihr Nettogewinn ist" anzeigen. Sie können dafür die folgende Formel verwenden:
="Ihr Nettogewinn ist "&TEXT(C6-C7,"$#,###.00")Die Formel berechnet zuerst den Gewinn durch eine Formel (C6-C7) innerhalb der TEXT-Funktion und verbindet den formatierten Wert mit der Zeichenfolge "Ihr Nettogewinn ist" unter Verwendung einer verketteten Formel (&) und zeigt schließlich das Ergebnis in Zelle A9 an.
Formatieren einer Handynummer mit der TEXT-Funktion
Wenn Sie eine Zahl mit mehr als 11 Stellen in die Tabelle eingeben, z. B. Handynummern, wandelt Excel normalerweise automatisch in die wissenschaftliche Schreibweise um. Und Sie können diese wissenschaftlichen Notationen richtig verwenden, also möchten Sie sie vielleicht in normale numerische Werte umwandeln. Mit der TEXT-Funktion können Sie diese lästigen wissenschaftlichen Notationen in Handynummern umwandeln.
Wie im folgenden Screenshot gezeigt, werden bei der Eingabe von Mobiltelefonnummern (12-stellig einschließlich ihres Ländercodes) in Excel diese Mobiltelefonnummern automatisch in das wissenschaftliche Notationsformat konvertiert.
Mit der TEXT-Funktion können Sie dieses wissenschaftliche Notationsformat in lesbare Handynummern formatieren.
Normalerweise ist eine Handynummer 12 Ziffern lang (dies kann in einigen Ländern variieren). Die ersten beiden Ziffern sind die Landesvorwahl und die restlichen 10 Ziffern sind die Handynummern.
Um die obigen wissenschaftlichen Notationen in Handynummern umzuwandeln, verwenden Sie diese Formel:
=TEXT(A1,"+############")Wir verwenden "############" als Formatcode für dieses Beispiel, um die Notation in eine Handynummer umzuwandeln:
Jetzt machen wir es besser lesbar, indem wir die Ländervorwahl von der Handynummer trennen. Fügen Sie dazu nach zwei Hashes einen Bindestrich (-) ein.
=TEXT(A1,"+##-##########")
Formatieren Sie das Datum mit der TEXT-Funktion
Standardmäßig speichert Excel Datum als Seriennummern. Die Seriennummer für den 1. Januar 1900 ist 1, und der 1. Januar 2001 ist 36892, da seit dem 1. Januar 1900 36891 Tage vergangen sind.
Da die meisten Funktionen Datumswerte automatisch in Seriennummern umwandeln, ist es schwierig, sie in einem lesbaren Format anzuzeigen. Aber mit der Excel-TEXT-Funktion können Sie diese ganz einfach in Textwerte umwandeln und in Ihrem gewünschten Format anzeigen.
Wenn Sie beispielsweise das Datum aus Zelle A1 (05-03-2015) nehmen und es in einem Standard-Datumsformat wie "5. März 2015" in Zelle B1 anzeigen möchten, verwenden Sie die folgende Formel:
=TEXT(A1,"mmm d,yyyy")mmmspezifiziert mit 3 Buchstaben den abgekürzten MonatDgibt den Tag des Monats ein- oder zweistellig anyyyygibt an, vierstellige Jahreszahlen anzuzeigen.
Der folgende Screenshot zeigt, wie Sie mithilfe der Textformel verschiedene Formatierungstypen auf dasselbe Datum anwenden können:
Verkettungsdatum und Text
Nehmen wir an, wir möchten den Namen (Spalte A) und das Geburtsdatum (Spalte B) zusammenfügen und in Spalte C anzeigen. Sie erhalten ein Ergebnis wie folgt:
Wenn wir Text in Zelle A1 und Datum in Zelle B1 direkt verketten, verbindet Excel den Text und die Seriennummer für das Datum, nicht das tatsächliche Datum.
Um Text und Datum zu verketten und das Datum richtig im gewünschten Format anzuzeigen, verwenden Sie die TEXT-Funktion mit der CONCAT-Funktion.
Die Formel:
=CONCAT(A2,"-",TEXT(B2,"dd/m/yy"))Das Ergebnis:
Jetzt fügen wir eine Textzeichenfolge „was geboren am“ zur Ausgabe hinzu und formatieren das Datum in einem anderen Format.
Anschließend wird die Formel mithilfe des Ausfüllkästchens in die Zelle A2:A5 kopiert.
In einem anderen Beispiel verwenden wir die HEUTE() Funktion, um das aktuelle Datum abzurufen und das Datum mit einem zugehörigen Text zu verbinden.
Jetzt können Sie jeden Wert (z. B. Zahlen, Datum usw.) ganz einfach in Text in Ihrem gewünschten Format umwandeln.