✅ Extrahieren von Zahlen aus einer Zeichenfolge in Excel (mit Formeln oder VBA)

Inhaltsverzeichnis

Extrahieren von Zahlen aus String in Excel (Formel für Excel 2016)
Extrahieren von Zahlen aus String in Excel (für Excel 2013/2010/2007)
Trennen Sie Text und Zahlen in Excel mit VBA

Video ansehen - So extrahieren Sie Zahlen aus einer Textzeichenfolge in Excel (mit Formel und VBA)

Es gibt keine eingebaute Funktion in Excel, um die Zahlen aus einer Zeichenfolge in einer Zelle zu extrahieren (oder umgekehrt - entfernen Sie den numerischen Teil und extrahieren Sie den Textteil aus einer alphanumerischen Zeichenfolge).

Dies kann jedoch mit einem Cocktail von Excel-Funktionen oder einfachem VBA-Code erfolgen.

Lassen Sie mich Ihnen zuerst zeigen, wovon ich rede.

Angenommen, Sie haben einen Datensatz wie unten gezeigt und möchten die Zahlen aus der Zeichenfolge extrahieren (wie unten gezeigt):

Die von Ihnen gewählte Methode hängt auch von der verwendeten Excel-Version ab:

Für Versionen vor Excel 2016 müssen Sie etwas längere Formeln verwenden
Für Excel 2016 können Sie die neu eingeführte TEXTJOIN-Funktion verwenden
Die VBA-Methode kann in allen Excel-Versionen verwendet werden

Klicken Sie hier, um die Beispieldatei herunterzuladen

Extrahieren von Zahlen aus String in Excel (Formel für Excel 2016)

Diese Formel funktioniert nur in Excel 2016, da sie die neu eingeführte TEXTJOIN-Funktion verwendet.

Außerdem kann diese Formel die Zahlen extrahieren, die sich am Anfang, Ende oder in der Mitte der Textzeichenfolge befinden.

Beachten Sie, dass die in diesem Abschnitt behandelte TEXTJOIN-Formel alle numerischen Zeichen zusammen ergibt. Wenn der Text beispielsweise „Der Preis für 10 Tickets beträgt 200 USD“ lautet, erhalten Sie als Ergebnis 10200.

Angenommen, Sie haben den unten gezeigten Datensatz und möchten die Zahlen aus den Zeichenfolgen in jeder Zelle extrahieren:

Unten ist die Formel, die Ihnen einen numerischen Teil aus einer Zeichenfolge in Excel liefert.

=TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),""))

Dies ist eine Array-Formel, also müssen Sie ‘Strg + Umschalt + Eingabetaste‘ statt Enter zu verwenden.

Falls die Textzeichenfolge keine Zahlen enthält, würde diese Formel ein Leerzeichen (leere Zeichenfolge) zurückgeben.

Wie funktioniert diese Formel?

Lassen Sie mich diese Formel brechen und versuchen, zu erklären, wie sie funktioniert:

ROW(INDIRECT(“1:”&LEN(A2))) – dieser Teil der Formel würde eine Reihe von Zahlen ergeben, die bei eins beginnen. Die Funktion LEN in der Formel gibt die Gesamtzahl der Zeichen in der Zeichenfolge zurück. Im Fall von „Die Kosten betragen 100 USD“ wird 19 zurückgegeben. Die Formeln würden somit zu ROW(INDIRECT(“1:19”) werden. Die ROW-Funktion gibt dann eine Reihe von Zahlen zurück – {1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19}
(MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1)*1) - Dieser Teil der Formel würde ein Array von #VALUE zurückgeben! Fehler oder Zahlen basierend auf der Zeichenfolge. Alle Textzeichen in der Zeichenfolge werden zu #VALUE! Fehler und alle numerischen Werte bleiben unverändert. Dies geschieht, wenn wir die MID-Funktion mit 1 multipliziert haben.
IFERROR((MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1)*1)”,”) - Wenn die IFERROR-Funktion verwendet wird, würde sie alle #VALUE! Fehler und nur die Zahlen würden bleiben. Die Ausgabe dieses Teils würde so aussehen - {"";"";"";"";"";""";"";"";"";"";""";"";""; ””;””;””;1;0;0}
=TEXTJOIN(““,TRUE,IFERROR((MID(A2,ROW(INDIRECT(“1:“&LEN(A2))),1)*1)“,“)) - Die TEXTJOIN-Funktion kombiniert jetzt einfach die Zeichenfolgen das bleibt (das sind nur die Zahlen) und ignoriert die leere Zeichenfolge.

Profi-Tipp: Wenn Sie die Ausgabe eines Teils der Formel überprüfen möchten, wählen Sie die Zelle aus, drücken Sie F2, um in den Bearbeitungsmodus zu gelangen, wählen Sie den Teil der Formel aus, für den Sie die Ausgabe wünschen, und drücken Sie F9. Sie werden das Ergebnis sofort sehen. Und denken Sie dann daran, entweder Strg + Z zu drücken oder die Escape-Taste zu drücken. Drücken Sie NICHT die Eingabetaste.

Laden Sie die Beispieldatei herunter

Sie können die gleiche Logik auch verwenden, um den Textteil aus einer alphanumerischen Zeichenfolge zu extrahieren. Unten ist die Formel, die den Textteil aus der Zeichenfolge erhalten würde:

=TEXTJOIN("",TRUE,IF(ISERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),MID(A2,ROW(INDIRECT("1:"&LEN (A2))),1),""))

Eine geringfügige Änderung in dieser Formel besteht darin, dass die IF-Funktion verwendet wird, um zu überprüfen, ob das Array, das wir von der MID-Funktion erhalten, Fehler sind oder nicht. Wenn es sich um einen Fehler handelt, wird der Wert beibehalten, ansonsten wird er durch ein Leerzeichen ersetzt.

Dann wird TEXTJOIN verwendet, um alle Textzeichen zu kombinieren.

Vorsicht: Diese Formel funktioniert zwar hervorragend, verwendet jedoch eine flüchtige Funktion (die INDIREKTE Funktion). Dies bedeutet, dass es einige Zeit dauern kann, bis Sie die Ergebnisse erhalten, wenn Sie dies mit einem großen Datensatz verwenden. Am besten erstellen Sie ein Backup, bevor Sie diese Formel in Excel verwenden.

Extrahieren von Zahlen aus String in Excel (für Excel 2013/2010/2007)

Wenn Sie Excel 2013, 2010 oder 2007 haben, können Sie die TEXTJOIN-Formel nicht verwenden, daher müssen Sie eine komplizierte Formel verwenden, um dies zu erreichen.

Angenommen, Sie haben einen Datensatz wie unten gezeigt und möchten alle Zahlen in der Zeichenfolge in jeder Zelle extrahieren.

Die folgende Formel wird dies tun:

=WENN(SUMME(LEN(A2)-LEN(ERSATZ(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),"")

Falls die Textzeichenfolge keine Zahl enthält, würde diese Formel leer (leere Zeichenfolge) zurückgeben.

Obwohl dies eine Array-Formel ist, brauche nicht 'Strg-Umschalt-Eingabe' verwenden, um dies zu verwenden. Eine einfache Eingabe funktioniert für diese Formel.

Die Anerkennung dieser Formel geht an das erstaunliche Mr. Excel-Forum.

Auch hier extrahiert diese Formel alle Zahlen in der Zeichenfolge, unabhängig von der Position. Wenn der Text beispielsweise „Der Preis für 10 Tickets beträgt 200 USD“ lautet, erhalten Sie als Ergebnis 10200.

Vorsicht: Diese Formel funktioniert zwar hervorragend, verwendet jedoch eine flüchtige Funktion (die INDIRECT-Funktion). Dies bedeutet, dass es einige Zeit dauern kann, bis Sie die Ergebnisse erhalten, wenn Sie dies mit einem großen Datensatz verwenden. Am besten erstellen Sie ein Backup, bevor Sie diese Formel in Excel verwenden.

Trennen Sie Text und Zahlen in Excel mit VBA

Wenn Sie Text und Zahlen trennen (oder Zahlen aus dem Text extrahieren) zu oft müssen, können Sie auch die VBA-Methode verwenden.

Alles, was Sie tun müssen, ist, einen einfachen VBA-Code zu verwenden, um eine benutzerdefinierte benutzerdefinierte Funktion (UDF) in Excel zu erstellen, und dann anstelle langer und komplizierter Formeln diese VBA-Formel zu verwenden.

Lassen Sie mich Ihnen zeigen, wie Sie in VBA zwei Formeln erstellen - eine zum Extrahieren von Zahlen und eine zum Extrahieren von Text aus einer Zeichenfolge.

Extrahieren von Zahlen aus String in Excel (mit VBA)

In diesem Teil zeige ich Ihnen, wie Sie die benutzerdefinierte Funktion erstellen, um nur den numerischen Teil aus einer Zeichenfolge zu erhalten.

Unten ist der VBA-Code, den wir verwenden werden, um diese benutzerdefinierte Funktion zu erstellen:

Funktion GetNumeric(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1) Next i GetNumeric = Ergebnis-Endfunktion

Hier sind die Schritte, um diese Funktion zu erstellen und sie dann im Arbeitsblatt zu verwenden:

Wechseln Sie zur Registerkarte Entwickler.
Klicken Sie auf Visual Basic (Sie können auch die Tastenkombination ALT + F11 verwenden)
Klicken Sie im sich öffnenden VB-Editor-Backend mit der rechten Maustaste auf eines der Arbeitsmappenobjekte.
Gehen Sie zu Einfügen und klicken Sie auf Modul. Dadurch wird das Modulobjekt für die Arbeitsmappe eingefügt.
Kopieren Sie im Fenster Modulcode den oben genannten VBA-Code und fügen Sie ihn ein.
Schließen Sie den VB-Editor.

Jetzt können Sie die GetText-Funktion im Arbeitsblatt verwenden. Da wir die ganze Arbeit im Code selbst erledigt haben, müssen Sie nur die Formel =GetNumeric(A2) verwenden.

Dadurch erhalten Sie sofort nur den numerischen Teil der Zeichenfolge.

Beachten Sie, dass Sie sie mit der Erweiterung .xls oder .xlsm speichern müssen, da die Arbeitsmappe jetzt VBA-Code enthält.

Laden Sie die Beispieldatei herunter

Falls Sie diese Formel häufig verwenden müssen, können Sie diese auch in Ihrer persönlichen Makro-Arbeitsmappe speichern. Auf diese Weise können Sie diese benutzerdefinierte Formel in allen Excel-Arbeitsmappen verwenden, mit denen Sie arbeiten.

Extrahieren von Text aus einer Zeichenfolge in Excel (mit VBA)

In diesem Teil zeige ich Ihnen, wie Sie die benutzerdefinierte Funktion erstellen, um nur den Textteil aus einer Zeichenfolge zu erhalten.

Unten ist der VBA-Code, den wir verwenden werden, um diese benutzerdefinierte Funktion zu erstellen:

Funktion GetText(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If Not (IsNumeric(Mid(CellRef, i, 1))) Then Result = Result & Mid(CellRef, i, 1 ) Weiter i GetText = Ergebnis-Ende-Funktion

Hier sind die Schritte, um diese Funktion zu erstellen und sie dann im Arbeitsblatt zu verwenden:

Wechseln Sie zur Registerkarte Entwickler.
Klicken Sie auf Visual Basic (Sie können auch die Tastenkombination ALT + F11 verwenden)
Klicken Sie im sich öffnenden VB-Editor-Backend mit der rechten Maustaste auf eines der Arbeitsmappenobjekte.
Gehen Sie zu Einfügen und klicken Sie auf Modul. Dadurch wird das Modulobjekt für die Arbeitsmappe eingefügt.
- Wenn Sie bereits ein Modul haben, doppelklicken Sie darauf (Sie müssen kein neues einfügen, wenn Sie es bereits haben).
Kopieren Sie im Fenster Modulcode den oben genannten VBA-Code und fügen Sie ihn ein.
Schließen Sie den VB-Editor.

Jetzt können Sie die GetNumeric-Funktion im Arbeitsblatt verwenden. Da wir die ganze Arbeit im Code selbst erledigt haben, brauchen Sie nur die Formel =GetText(A2) zu verwenden.

Dadurch erhalten Sie sofort nur den numerischen Teil der Zeichenfolge.