Wie extrahiert man Zahlen ausschließlich aus einer Textzeichenfolge in Excel?
Beim Arbeiten mit Excel enthalten Zellen häufig eine Mischung aus Zahlen und Text – etwa „Bestellung 1234“ oder „Punktzahl beträgt 95,6“. Oft müssen für Analysen, Berechnungen oder Berichte ausschließlich die numerischen Werte aus diesen Textzeichenfolgen extrahiert werden. Je nachdem, ob sich die Zahl am Anfang, am Ende oder irgendwo innerhalb des Textes befindet – oder ob nur Dezimalzahlen benötigt werden – bietet Excel mehrere praktische Lösungsansätze.
Dieser Leitfaden stellt verschiedene Methoden zur Extraktion von Zahlen aus Textzeichenfolgen in Excel vor und berücksichtigt dabei unterschiedliche Szenarien – wie etwa das Extrahieren von Zahlen an beliebiger Position, der Umgang mit Dezimalzahlen sowie das gezielte Abrufen von Zahlen vom Anfang oder Ende einer Zeichenfolge. Jede Methode erklärt klar ihre Funktionsweise, ihren Anwendungsbereich und liefert praktische Tipps für eine effektive Nutzung.
Zahl aus beliebiger Position einer Textzeichenfolge extrahieren
Nur Dezimalzahl aus einer Textzeichenfolge extrahieren
Zahl vom Ende der Textzeichenfolge extrahieren
Zahl vom Anfang der Textzeichenfolge extrahieren
Zahl aus beliebiger Position einer Textzeichenfolge extrahieren
Das Extrahieren von Zahlen, die irgendwo innerhalb einer Textzeichenfolge eingebettet sind, ist ein häufiges Anliegen – besonders beim Bereinigen von Daten aus verschiedenen Quellen oder beim Zusammenführen von Informationen aus mehreren Tabellenblättern. Für dieses Szenario gibt es mehrere praktische und effiziente Lösungen, wobei jede je nach Ihrer Excel-Version und Ihrer Vertrautheit mit Formeln oder Add-Ins ihren idealen Einsatzbereich findet.
♦ Zahl aus beliebiger Position einer Textzeichenfolge mit Flash Fill extrahieren
Flash Fill ist eine schnelle und praktische Funktion, die ab Excel 2013 zur Verfügung steht und es Ihnen ermöglicht, Zahlen aus einer Textzeichenfolge anhand eines von Ihnen vorgegebenen Musters zu extrahieren. Diese Lösung kommt ganz ohne Formeln oder Makros aus und eignet sich daher ideal für einfache Szenarien:
1. Geben Sie in einer Zelle neben Ihrer Textzeichenfolge die Zahl manuell genau so ein, wie Sie sie extrahiert haben möchten (geben Sie beispielsweise in B2 „123“ ein, wenn A2 „Bestellung123A“ enthält).
2. Drücken Sie bei einer aktiven Zelle unterhalb Ihrer manuellen Eingabe Strg + E(oder verwenden Sie die Registerkarte)Daten > Flash Fill). Excel füllt automatisch nach unten und extrahiert Zahlen aus ähnlichen Zellen basierend auf Ihrem ersten Beispiel.
Diese Methode eignet sich hervorragend für einmalige, kleine bis mittelgroße Datensätze mit konsistenter Struktur. Beachten Sie jedoch, dass Flash Fill auf der Erkennung von Mustern basiert – bei stark inkonsistenten Daten oder einer gemischten Reihenfolge aus Zahlen und Text funktioniert die Extraktion möglicherweise nicht wie gewünscht. Falls Flash Fill nicht automatisch greift, prüfen Sie, ob es aktiviert ist unter Datei>Optionen>Erweitert>Automatisches Flash Fill.
♦ Zahl aus beliebiger Position einer Textzeichenfolge mit Formeln extrahieren
Die Verwendung von Formeln bietet eine vielseitige Möglichkeit, Zahlen aus Textzeichenfolgen zu extrahieren. Diese Methode greift auf integrierte oder dynamische Array-Funktionen zurück und eignet sich besonders gut für Lösungen, die sich automatisch aktualisieren, sobald sich die Quelldaten ändern.
Wählen Sie eine leere Zelle aus, in der die extrahierte Zahl erscheinen soll, und geben Sie anschließend – je nach Ihrer Excel-Version – eine der folgenden Formeln ein. Ziehen Sie nach der Eingabe den Ausfüllknauf nach unten, um die Formel bei Bedarf auf weitere Zellen zu übertragen. Als Referenz dient die folgende Abbildung:
● Alle Excel-Versionen:
=IF(SUM(LEN(A2)-LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"}, "")))>0, SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("$1:$"&LEN(A2))),1))* ROW(INDIRECT("$1:$"&LEN(A2))),0), ROW(INDIRECT("$1:$"&LEN(A2))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN(A2)))/10),"")Diese Formel ist mit allen modernen Excel-Versionen kompatibel und bietet eine zuverlässige Alternative, wenn Sie nicht Excel 365 oder Excel 2021 verwenden. Beachten Sie jedoch, dass Formeln mit mehreren Funktionen die Leistung bei großen Datensätzen beeinträchtigen können. Stellen Sie außerdem sicher, dass die Textzeichenfolgen mindestens ein numerisches Zeichen enthalten – andernfalls gibt die Formel möglicherweise ein Leerzeichen oder einen Fehler zurück.
● Excel 365 oder Excel 2021 und neuere Versionen:
=TEXTJOIN("", TRUE, IFERROR(MID(A2, SEQUENCE(LEN(A2)), 1) *1, ""))Diese Formel nutzt neue dynamische Array-Funktionen und vereinfacht den Extraktionsprozess für Benutzer von Office 365 oder Excel 2021 und neuer erheblich. Sie verarbeitet sowohl einfache als auch komplexere alphanumerische Mischungen effizient. Stellen Sie sicher, dass Sie eine unterstützte Version verwenden – andernfalls funktioniert die Formel möglicherweise nicht wie erwartet.
Tipp: Prüfen Sie bei Formeln mit gemischten oder negativen Zahlen sowie Zahlen mit Sonderzeichen stets sorgfältig die Genauigkeit der extrahierten Ergebnisse. Standardmäßig erkennen Formeln Dezimalpunkte möglicherweise nicht – wenn Sie Dezimalzahlen extrahieren möchten, lesen Sie bitte den entsprechenden Abschnitt weiter unten. Sollten unerwartete Ergebnisse oder Fehler auftreten, überprüfen Sie Ihre Zellbezüge und die Formelsyntax gründlich.
♦ Zahl aus beliebiger Position einer Textzeichenfolge mit Kutools für Excel extrahieren
Kutools für Excel bietet eine unkomplizierte Methode, um Zahlen aus beliebiger Position innerhalb einer Textzeichenfolge zu extrahieren – ideal für Anwender, die eine intuitive Oberfläche Formeln vorziehen oder große Datenbereiche ohne manuellen Aufwand verarbeiten möchten. So sparen Sie Zeit und vereinfachen Ihre Arbeit, ohne sich Formeln merken oder mühsam Fehler suchen zu müssen.
- Klicken Sie auf Kutools > Text > Text extrahieren, wie unten gezeigt.
- Im Text extrahieren-Dialogfeld:
(1.) Wählen Sie die Zellenliste mit Ihren Textzeichenfolgen aus;
(2.) Aktivieren Sie die Option Zahlen extrahieren, um sicherzustellen, dass nur numerische Bestandteile erfasst werden;
(3.) Wenn sich die extrahierten Zahlen automatisch bei Änderungen in den Quelldaten aktualisieren sollen, aktivieren Sie das Kontrollkästchen Als Formel einfügen (andernfalls sind die Ergebnisse statischer Text);
(4.) Klicken Sie auf OK.
- Wählen Sie bei der Aufforderung eine Zelle für die Ausgabe – achten Sie dabei darauf, einen leeren Bereich mit ausreichend Platz für die Ergebnisse zu wählen, um ein Überschreiben vorhandener Daten zu vermeiden.
- Klicken Sie auf OK. Die Zahlen aus jeder ausgewählten Zelle werden sofort extrahiert.
Dieser Ansatz ist benutzerfreundlich und minimiert das Risiko von Formelfehlern oder falsch angewendeten Zellbezügen. Beachten Sie jedoch, dass Kutools ein Add-In ist, das nicht im Standardumfang von Excel enthalten ist. Es eignet sich ideal für Batch-Vorgänge und für Anwender, die komplexe Funktionssyntax oder VBA umgehen möchten.
Falls die Zahlen nicht wie erwartet extrahiert werden, prüfen Sie, ob in Ihren Originaltext gemischte Formatierungen (wie versteckte Zeichen oder nicht standardmäßige Ziffern) vorliegen. Erstellen Sie außerdem vor massenhaften Änderungen stets eine Sicherungskopie Ihres Arbeitsblatts.
♦ Zahl aus beliebiger Position einer Textzeichenfolge mit VBA-Code extrahieren
Für fortgeschrittene Benutzer oder spezielle Situationen, in denen Formeln und Add-Ins nicht ausreichen, bietet VBA einen flexiblen Ansatz zum Extrahieren von Zahlen aus Textzeichenfolgen in Excel. Diese Methode ist hochgradig anpassbar und besonders nützlich zur Automatisierung sich wiederholender Aufgaben oder zur Verarbeitung nicht standardmäßiger Datensätze.
- Öffnen Sie den VBA-Editor, indem Sie Alt + F11 drücken. Klicken Sie im Fenster Microsoft Visual Basic for Applications auf Einfügen > Modul. Fügen Sie im neuen Modulfenster den folgenden Code ein:
- VBA-Code: Nur Zahlen aus einer Textzeichenfolge extrahieren:
Sub ExtrNumbersFromRange() Dim xRg As Range Dim xDRg As Range Dim xRRg As Range Dim nCellLength As Integer Dim xNumber As Integer Dim strNumber As String Dim xTitleId As String Dim xI As Integer xTitleId = "KutoolsforExcel" Set xDRg = Application.InputBox("Please select text strings:", xTitleId, "", Type:=8) If TypeName(xDRg) = "Nothing" Then Exit Sub Set xRRg = Application.InputBox("Please select output cell:", xTitleId, "", Type:=8) If TypeName(xRRg) = "Nothing" Then Exit Sub xI = 0 strNumber = "" For Each xRg In xDRg xI = xI + 1 nCellLength = Len(xRg) For xNumber = 1 To nCellLength If IsNumeric(Mid(xRg, xNumber, 1)) Then strNumber = strNumber & Mid(xRg, xNumber, 1) End If Next xNumber xRRg.Item(xI) = strNumber strNumber = "" Next xRg End Sub - Nachdem Sie den Code eingefügt haben, drücken Sie F5 oder klicken Sie auf die Schaltfläche Ausführen. Daraufhin erscheint ein Eingabefeld, das Sie auffordert, den Bereich der zu verarbeitenden Textzeichenfolgen auszuwählen.
- Sobald Sie Ihren Bereich ausgewählt haben, klicken Sie auf OK. Anschließend erscheint ein zweites Eingabefeld, in dem Sie die Startzelle für die Ausgabe angeben müssen.
- Klicken Sie erneut auf OK. Alle Zahlen aus der ursprünglichen Auswahl werden extrahiert und in dem von Ihnen angegebenen Ausgabebereich platziert.
Die VBA-Methode überzeugt durch effiziente Verarbeitung großer Datensätze und flexible Anpassungsmöglichkeiten – etwa um nur die erste Zahl zu extrahieren, Dezimalzahlen korrekt zu handhaben oder bestimmte Symbole auszuschließen – sofern grundlegende VBA-Kenntnisse vorhanden sind. Hinweis: VBA-Makros lassen sich nicht rückgängig machen und könnten Daten überschreiben, wenn der gewählte Zielbereich mit Ihren Originaldaten überlappt. Testen Sie daher stets zunächst an einer Sicherungskopie und stellen Sie sicher, dass Makros in Excel aktiviert sind.
Nur die Dezimalzahl aus einer Textzeichenfolge extrahieren
Manchmal enthalten Ihre Textzeichenfolgen Zahlen mit Dezimaltrennzeichen (z. B. „Gewicht: 15,25 kg“). Standardformeln erfassen solche Dezimalzahlen – inklusive des Dezimaltrennzeichens – unter Umständen nicht korrekt. Die folgenden Lösungen sind speziell für genau diese Fälle konzipiert:
Die folgenden Formeln helfen Ihnen, Dezimalzahlen effizient aus Textzeichenfolgen zu extrahieren. Geben Sie die passende Formel in eine leere Zelle neben Ihren Daten ein und ziehen Sie den Ausfüllknauf, um sie bei Bedarf auf weitere Zeilen zu übertragen.
● Alle Excel-Versionen:
=LOOKUP(9.9E+307,--LEFT(MID(A2,MIN(FIND({1,2,3,4,5,6,7,8,9,0}, $A2&"1023456789")),999),ROW(INDIRECT("1:999"))))Diese Formel unterstützt die meisten Excel-Versionen und ist so gestaltet, dass Dezimaltrennzeichen als Bestandteil einer Zahl während der Extraktion erkannt werden. Überprüfen Sie Ihre Daten sorgfältig, um sicherzustellen, dass keine überflüssigen Punkte im Text enthalten sind. Falls Dezimalzahlen nicht wie erwartet extrahiert werden, prüfen Sie die ursprüngliche Zeichenfolge auf Sonderzeichen oder Formatierungsprobleme.
● Excel 365:
=REGEXEXTRACT(A2,"[\d.]+")Mit Excel 365 verarbeiten dynamische Arrays extrahierte Dezimalzahlen aus komplexen Textzeichenfolgen intelligenter und geben sie präzise zurück. Geben Sie die Formel in die Zielzelle ein, drücken Sie Enter, und ziehen Sie die Formel bei Bedarf nach unten.
Tipp: Sollten Sie wiederholt Nullen oder Fehler statt Zahlen erhalten, vergewissern Sie sich, dass Ihre Daten gültige Dezimalzahlen enthalten und die Zellbezüge korrekt sind. Falls Ihre Zahlen sowohl negative Werte als auch Dezimalstellen umfassen können, müssen die Formeln gegebenenfalls angepasst werden.
Zahl vom Ende der Textzeichenfolge extrahieren
Wenn Zahlen stets am Ende Ihrer Textzeichenfolgen stehen (z. B. „221_ProductID_446“ oder „923Ticket#2021“), möchten Sie wahrscheinlich nur die abschließende Zahl („446“ oder „2021“) extrahieren. Die hier bereitgestellte Formel ist speziell für dieses Szenario entwickelt und stellt sicher, dass ausschließlich numerische Zeichenfolgen am Ende des Textes erfasst werden.
Kopieren Sie die folgende Formel in eine leere Zelle. Für ältere Excel-Versionen bestätigen Sie die Formel mit Strg + Umschalt + Enter, um eine Matrixformel zu erstellen. In neueren Excel-Versionen mit dynamischen Arrays genügt ein einfacher Druck auf Enter:
=RIGHT(A2, LEN(A2) - MAX(IF(ISNUMBER(MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(A2))), 0)))Ziehen Sie den Ausfüllknauf nach unten, um die Formel auf weitere Zeilen anzuwenden. Diese Methode eignet sich ideal für konsistente Formate, bei denen jeweils nur die letzte Zahl der Zeichenfolge benötigt wird – Zahlen in der Mitte oder am Anfang werden ignoriert.
Hinweis: Falls Ihre Daten Leerzeichen, Sonderzeichen oder Positionsabweichungen enthalten, passen Sie die Formel an oder verwenden Sie eine allgemeinere Methode wie oben beschrieben. Prüfen Sie stets, ob das Ergebnis wie erwartet ist, insbesondere wenn Ihre Text endet mit nicht-numerischen Zeichen enthält, die Zahlen ähneln.
Zahl vom Anfang der Textzeichenfolge extrahieren
Müssen Sie ausschließlich Zahlen extrahieren, die ganz am Anfang Ihrer Textzeichenfolgen stehen, hilft Ihnen eine spezielle Formel, genau diesen Teil zu isolieren.
Platzieren Sie die Formel in einer leeren Zielzelle und verwenden Sie für ältere Excel-Versionen Strg + Umschalt + Enter, um eine Matrixformel zu erstellen (in Excel 365/2021 drücken Sie einfach)Enter):
=LEFT(A2, MATCH(FALSE, ISNUMBER(MID(A2, ROW(INDIRECT("1:"&LEN(A2)+1)), 1) *1), 0) -1)Ziehen Sie die Formel anschließend für alle Zeilen nach unten, für die eine Extraktion erforderlich ist. Dieser Ansatz liefert ausschließlich die Zahlen vom Anfang der Zeichenfolge – selbst wenn weitere Ziffern an anderer Stelle vorkommen, wird stets nur der führende numerische Teil erfasst.
Tipp: Falls das erste Zeichen Ihrer Zeichenfolge nicht numerisch ist, liefert diese Formel möglicherweise eine leere Zelle oder einen Fehler. Prüfen und bereinigen Sie Ihre Originaldaten bei Bedarf, um optimale Ergebnisse zu erzielen.
Das Extrahieren von Zahlen aus Textzeichenfolgen ist eine häufige – und oft entscheidende – Aufgabe bei der Datenaufbereitung in Excel. Die passende Methode hängt von Ihrer Datenstruktur, Ihrer Excel-Version sowie Ihrer Vertrautheit mit Formeln oder Add-Ins ab. Sollte Ihr Szenario nicht zu den oben genannten Lösungen passen, kombinieren Sie mehrere Methoden oder greifen Sie auf spezialisierte Add-Ins für anspruchsvollere Anforderungen zurück. Prüfen Sie Ihre extrahierten Ergebnisse stets auf Inkonsistenzen, sichern Sie Ihre Daten und testen Sie neue Vorgehensweisen zunächst an kleineren Datensätzen, um typische Fehler zu vermeiden.
Verwandte Artikel:
Beste Office-Produktivitätstools
Verbessern Sie Ihre Excel-Kenntnisse mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor.Kutools für Excel bietet über 300 erweiterte Funktionen zur Steigerung der Produktivität und Zeit sparen.Klicken Sie hier, um die Funktion zu erhalten, die Sie am dringendsten benötigen...
Office Tab bringt eine tabbasierte Oberfläche in Office und macht Ihre Arbeit viel einfacher
- Aktivieren Sie tabbasiertes Bearbeiten und Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters – statt jedes in einem separaten Fenster zu öffnen.
- Steigert Ihre Produktivität um 50 % und erspart Ihnen täglich Hunderte von Mausklicks!
Alle Kutools-Add-Ins – ein Installationsprogramm
Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook und PowerPoint sowie Office Tab Pro – ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- Alles-in-einem-Paket— Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro
- Ein Installationsprogramm, eine Lizenz— innerhalb weniger Minuten eingerichtet (MSI-fähig)
- Funktioniert besser zusammen— optimierte Produktivität über alle Office-Anwendungen hinweg
- 30-tägige Vollversion zum Testen— keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis— sparen Sie im Vergleich zum Kauf einzelner Add-Ins