Nicht-numerische Zeichen aus Textzeichenfolgen entfernen oder herausfiltern

Gelegentlich müssen Sie möglicherweise alle nicht-numerischen Zeichen aus Textzeichenfolgen entfernen und nur die Zahlen behalten – wie im folgenden Screenshot gezeigt. In diesem Artikel stellen wir Ihnen einige Formeln vor, mit denen Sie diese Aufgabe in Excel mühelos erledigen können.

• Alle nicht-numerischen Zeichen aus Textzeichenfolgen mithilfe von Formeln entfernen oder herausfiltern
• Alle nicht-numerischen Zeichen aus Textzeichenfolgen mithilfe einer einfachen Funktion entfernen oder herausfiltern

Alle nicht-numerischen Zeichen aus Textzeichenfolgen mithilfe von Formeln entfernen oder herausfiltern

In Excel 2019 und Office 365 hilft die neue TEXTVERKETTEN-Funktion in Kombination mit den Funktionen WENNFEHLER, MITTELTEXT, ZEILE und INDIREKT dabei, ausschließlich Zahlen aus einer Textzeichenfolge zu extrahieren. Die allgemeine Syntax lautet:

• text: die Textzeichenfolge oder der Zellwert, aus der bzw. dem Sie alle nicht-numerischen Zeichen entfernen möchten.

1. Kopieren Sie die folgende Formel in eine leere Zelle, in der das Ergebnis angezeigt werden soll, oder geben Sie sie manuell ein:

2. Drücken Sie anschließend gleichzeitig die Tasten Strg + Umschalt + Enter, um das erste Ergebnis zu erhalten. Siehe Screenshot:

3. Wählen Sie anschließend die Formelzelle aus und ziehen Sie den Ausfüllknauf nach unten auf die Zellen, auf die Sie die Formel anwenden möchten. Es wurden ausschließlich Zahlen extrahiert – alle nicht-numerischen Zeichen wurden entfernt. Siehe Screenshot:

Erklärung der Formel:

ZEILE(INDIREKT("1:100")): Die Angabe „1:100“ in der INDIREKT-Formel legt fest, dass die MITTELTEXT-Funktion die ersten 100 Zeichen der Textzeichenfolge auswertet. Das resultierende Array enthält 100 fortlaufende Zahlen wie folgt: {1;2;3;4;5;6;7;8;…;98;99;100}.
Hinweis: Wenn Ihre Textzeichenfolge deutlich länger ist, erhöhen Sie den Wert „100“ einfach entsprechend.

MITTELTEXT(A2;ZEILE(INDIREKT("1:100"));1): Diese MITTELTEXT-Funktion wird verwendet, um Text extrahieren in Zelle A2 jeweils ein Zeichen zu extrahieren, wodurch ein Array wie folgt entsteht:
{"5";"0";"0";" ";"K";"u";"t";"o";"o";"l";"s";" ";"f";"o";"r";" ";"E";"x";"c";"e";"l";"";"";"";"";"";„"...}

MITTELTEXT(A2;ZEILE(INDIREKT("1:100"));1)+0: Durch das Anhängen von +0 an dieses Array werden Textwerte in Zahlen umgewandelt. Numerische Texte werden dabei in echte Zahlen konvertiert, während nicht-numerische Werte als #WERT!-Fehler erscheinen – wie hier gezeigt:
{"5";"0";"0";#WERT!;#WERT!;#WERT!;#WERT!;#WERT!;#WERT!;#WERT!;#WERT!...}

WENNFEHLER(MITTELTEXT(A2;ZEILE(INDIREKT("1:100"));1)+0): Diese WENNFEHLER-Funktion ersetzt sämtliche Fehlerwerte durch eine leere Zeichenfolge, wie folgt:
{"5"; "0";"0";"";""; "";"";"";"";"";„"; … }

TEXTVERKETTEN(„";WAHR;WENNFEHLER(MITTELTEXT(A2;ZEILE(INDIREKT("1:100"));1)+0;„")): Schließlich verknüpft diese TEXTVERKETTEN-Funktion alle nicht leeren Werte des Arrays, das von der WENNFEHLER-Funktion zurückgegeben wurde, und liefert das Endergebnis.

Hinweise:

1. Mit der obigen Formel werden die Zahlen im Textformat zurückgegeben. Falls Sie einen echten numerischen Wert benötigen, verwenden Sie bitte stattdessen diese Formel und drücken Sie gleichzeitig die Tasten Strg + Umschalt + Enter, um das korrekte Ergebnis zu erhalten.

2. In älteren Excel-Versionen funktioniert diese Formel nicht. Dann hilft Ihnen die folgende Formel weiter. Kopieren Sie sie einfach in eine leere Zelle oder geben Sie sie manuell ein:

Alle nicht-numerischen Zeichen aus Textzeichenfolgen mithilfe einer einfachen Funktion entfernen oder herausfiltern

Verwendete verwandte Funktionen:

Weitere Artikel: