Laden Sie die Beispielarbeitsmappe herunter
In diesem Tutorial wird gezeigt, wie Sie Zahlen aus dem Text in einer Zelle in Excel und Google Sheets entfernen.
Wir werden zwei verschiedene Formeln zum Entfernen von Zahlen aus Text in Excel diskutieren.
ERSATZ-Funktionsformel
Wir können eine Formel verwenden, die auf der SUBSTITUTE-Funktion basiert. Es ist eine lange Formel, aber es ist eine der einfachsten Möglichkeiten, Zahlen aus einer alphanumerischen Zeichenfolge zu entfernen.
In dieser Formel haben wir SUBSTITUTE-Funktionen zehnmal verschachtelt, wie folgt:
| 1 | =ERSETZEN(ERSETZEN(ERSETZEN(ERSETZEN(ERSETZEN(ERSETZEN(ERSETZEN(ERSETZEN(ERSETZEN(B3,1,""),2,""),3,""),4,""),5," "),6,""),7,""),8,""),9,""),0,"") |
Array TEXTJOIN-Formel
Um Zahlen aus alphanumerischen Zeichenfolgen zu entfernen, können wir auch eine komplexe Matrixformel verwenden, die aus den Funktionen TEXTJOIN, MID, ROW und INDIRECT besteht.
| 1 | {=TEXTJOIN("",TRUE,IF(ISERR(MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1)+0),MID(B3,ROW(INDIRECT("1:" &LEN(B3))),1),""))} |
Hinweis: TEXTJOIN ist eine neue Excel-Funktion, die in Excel 2022+ und Office 365 verfügbar ist.
Dies ist eine komplexe Formel, daher werden wir sie in Schritte unterteilen, um sie besser zu verstehen.
Schritt 1
Die MID-Funktion wird verwendet, um die alphanumerische Zeichenfolge basierend auf den Argumenten start_num und num_chars zu extrahieren.
Für das Argument start_num in der MID-Funktion verwenden wir die resultierende Array-Liste aus den Funktionen ROW und INDIRECT.
| 1 | =REIHE(INDIREKT("1:"&LEN(B3))) |
Und für das num-chars-Argument setzen wir 1. Nachdem die Argumente in die MID-Funktion eingefügt wurden, wird ein Array zurückgegeben.
| 1 | {=MITTE(B3,REIHE(INDIREKT("1:"&LEN(B3))),1)} |
Schritt 2
Wir fügen jedem Wert im resultierenden Array (das wir von der obigen MID-Funktion erhalten) Null hinzu. Wenn in Excel Zahlen zu nicht numerischen Zeichen hinzugefügt werden, erhalten wir einen #VALUE! Fehler. Nachdem wir also 0 in das obige Array hinzugefügt haben, erhalten wir ein Array von Zahlen und #Value! Fehler.
| 1 | {=MITTE(B3,REIHE(INDIREKT("1:"&LEN(B3))),1)+0} |
Schritt 3
Nach dem Hinzufügen von 0 wird das resultierende Array in die ISERR-Funktion eingefügt. Wie wir wissen, gibt die ISERR-Funktion TRUE für Fehler und FALSE für Nicht-Fehlerwerte zurück.
Es wird also ein Array von TRUE und FALSE, TRUE für nicht numerische Zeichen und FALSE für Zahlen zurückgegeben.
| 1 | =ISERR(MITTE(B3,REIHE(INDIREKT("1:"&LEN(B3))),1)+0) |
Schritt 4
Jetzt fügen wir die IF-Funktion hinzu.
Die IF-Funktion prüft das Ergebnis der ISERR-Funktion (Schritt 3). Wenn der Wert TRUE ist, wird ein Array aller Zeichen einer alphanumerischen Zeichenfolge zurückgegeben. Dafür haben wir eine weitere MID-Funktion hinzugefügt, ohne am Ende eine Null hinzuzufügen. Wenn der Wert der IF-Funktion FALSE ist, wird ein Leerzeichen ("") zurückgegeben.
Auf diese Weise haben wir ein Array, das nur die nicht numerischen Zeichen des Strings enthält.
| 1 | =IF(ISERR(MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1)+0),MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1 ),"") |
Schritt 5
Schließlich wird das obige Array in die TEXTJOIN-Funktion eingefügt. Die Funktion TEXTJOIN verbindet alle Zeichen des obigen Arrays und ignoriert die leere Zeichenfolge.
Als Trennzeichen für diese Funktion wird ein leerer String ("") gesetzt und der Wert des ignore_empty-Arguments wird auf TRUE gesetzt.
Dadurch erhalten wir das gewünschte Ergebnis, d. h. nur die nicht numerischen Zeichen der alphanumerischen Zeichenfolge.
| 1 | {=TEXTJOIN("",TRUE,IF(ISERR(MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1)+0),MID(B3,ROW(INDIRECT("1:" &LEN(B3))),1),""))} |
Hinweis: Dies ist eine Array-Formel. Bei der Eingabe von Arrayformeln in Excel 2022 oder früher müssen Sie STRG + UMSCHALT + EINGABETASTE die Formel statt der regulären eingeben EINTRETEN.
An den erscheinenden geschweiften Klammern erkennen Sie, dass Sie die Formel richtig eingegeben haben. Geben Sie die geschweiften Klammern NICHT manuell ein, die Formel funktioniert nicht.
Bei Office 365 (und vermutlich Excel-Versionen nach 2022) können Sie die Formel einfach wie gewohnt eingeben.
TRIM-Funktion
Wenn die Zahlen aus der Zeichenfolge entfernt werden, bleiben möglicherweise zusätzliche Leerzeichen übrig. Um alle nachgestellten und führenden Leerzeichen sowie die zusätzlichen Leerzeichen zwischen den Wörtern zu entfernen, können wir die TRIM-Funktion vor der Hauptformel verwenden, wie folgt:
| 1 | =TRIM (C3) |
Entfernen Sie Zahlen aus dem Text in Google Tabellen
Die Formel zum Entfernen von Zahlen aus dem Text funktioniert in Google Sheets genauso wie in Excel:
