Wie extrahiert man Zahlen aus alphanumerischen Zeichenfolgen in Excel?

Dieses Tutorial bietet drei Methoden, um Zahlen aus alphanumerischen Textzeichenfolgen in Excel zu extrahieren.

Zahlen aus alphanumerischen Textzeichenfolgen mit Formel extrahieren
Zahlen mithilfe eines erstaunlichen Tools ganz einfach aus alphanumerischen Textzeichenfolgen extrahieren
Zahlen aus alphanumerischen Textzeichenfolgen mit VBA-Code extrahieren

Zahlen aus alphanumerischen Textzeichenfolgen mit Formel extrahieren

Sie können die folgende Formel anwenden, um Zahlen überall in einer Textzeichenfolge in Excel zu extrahieren.

1. Wählen Sie eine leere Zelle aus, um die extrahierten Zahlen auszugeben, geben Sie die folgende Formel ein und drücken Sie die Eingabetaste. Ziehen Sie dann das AutoFill-Handle nach unten, um die Formel auf andere Zellen anzuwenden.

=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),"")

Hinweis: In der Formel ist A1 die Zelle, die die Textzeichenfolge enthält, aus der Sie Zahlen extrahieren möchten. Bitte ändern Sie dies je nach Bedarf.

Dann werden die Zahlen aus einer beliebigen Position in einer Textzeichenfolge in den angegebenen Zellen extrahiert, wie im obigen Screenshot gezeigt.

Zahlen ganz einfach aus alphanumerischen Textzeichenfolgen mit Kutools für Excel extrahieren

Dieser Abschnitt empfiehlt das Hilfsprogramm „Text extrahieren“ von Kutools für Excel. Mit diesem Tool kann das Extrahieren von Zahlen aus einer Textzeichenfolge leicht gehandhabt werden.

1. Klicken Sie auf Kutools > Text > Text extrahieren, um die Funktion zu aktivieren.

2. Im Dialogfeld „Text extrahieren“ führen Sie bitte die folgenden Einstellungen durch.

2.1) Klicken Sie auf den Reiter „Nach Position extrahieren“ (dieser Reiter ist standardmäßig aktiviert);

2.2) Im Bereich Abschnitt klicken Sie auf die Schaltfläche, um den Zellbereich auszuwählen, aus dem Sie Text extrahieren möchten;

2.3) Im Bereich Optionen: Wählen Sie die Option „Zahlen extrahieren“;

2.4) Klicken Sie auf OK.

Hinweis: Aktivieren Sie das Kontrollkästchen „ Als Formel einfügen“ in der linken unteren Ecke des Dialogfelds, um das Ergebnis als Formel in die Ergebniszellen einzufügen. Wenn sich der Wert der referenzierten Zelle ändert, wird das Ergebnis automatisch aktualisiert.

3. Wählen Sie im Dialogfeld „Text extrahieren“ eine leere Zelle aus, um den extrahierten Text auszugeben, und klicken Sie dann auf OK.

Dann werden die Zahlen aus jeder Position in einer Textzeichenfolge extrahiert. Siehe Screenshot:

Nur Zahlen in alphanumerischen Textzeichenfolgen mit VBA-Code behalten

Der folgende VBA-Code kann Ihnen helfen, alle nicht numerischen Zeichen aus einem bestimmten Bereich zu entfernen und nur Zahlen in den Zellen zu behalten. Bitte gehen Sie wie folgt vor.

Hinweis: Da der Code den ursprünglichen Bereich direkt bearbeitet, bewahren Sie bitte eine Kopie des ursprünglichen Bereichs auf, um Datenverlust zu vermeiden.

1. Drücken Sie gleichzeitig die Tasten Alt + F11, um das Fenster Microsoft Visual Basic for Applications zu öffnen.

2. Klicken Sie im Fenster Microsoft Visual Basic for Applications auf Einfügen > Modul. Kopieren und fügen Sie dann den folgenden VBA-Code in das Modulfenster ein.

VBA-Code: Alle nicht numerischen Zeichen aus Zellen entfernen

Sub GetNumbers()
'Updated by Extendoffice 20210125
    Dim xRegEx As Object
    Dim xRg As Range
    Dim xCell As Range
    Dim xTxt As String
    On Error Resume Next
    xTxt = ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("Pease select range:", "Kutools for Excel", xTxt, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    Set xRegEx = CreateObject("VBScript.RegExp")
    With xRegEx
        .Pattern = "\D+"
        .IgnoreCase = True
        .Global = True
    End With
    xRg.NumberFormat = "@"
    For Each xCell In xRg
        xCell.Value = xRegEx.Replace(xCell.Value, "")
    Next
    Set xRegEx = Nothing
End Sub

3. Drücken Sie die Taste F5, um den Code auszuführen. Dann erscheint ein Dialogfeld von Kutools für Excel, wählen Sie den Bereich aus, der die gemischten alphanumerischen Textzeichenfolgen enthält, aus denen Sie nur die Zahlen behalten möchten, und klicken Sie auf die Schaltfläche OK.

Jetzt wurden alle nicht numerischen Zeichen im ausgewählten Bereich sofort entfernt, und es bleiben nur die Zahlen übrig. Siehe Screenshot: