Wie extrahiert man Zahlen aus alphanumerischen Textzeichenfolgen in Excel?
Dieses Tutorial bietet drei Methoden, um die Extraktion von Zahlen aus gemischten alphanumerischen Textzeichenfolgen in Excel zu vereinfachen.
Extrahieren Sie Zahlen aus gemischten alphanumerischen Textzeichenfolgen mithilfe einer Formel
Extrahieren Sie Zahlen aus gemischten alphanumerischen Textzeichenfolgen mühelos mit einem leistungsstarken Tool
Extrahieren Sie Zahlen aus gemischten alphanumerischen Textzeichenfolgen mithilfe von VBA-Code
Extrahieren Sie Zahlen aus gemischten alphanumerischen Textzeichenfolgen mithilfe einer Formel
Mit der folgenden Formel können Sie Zahlen aus beliebigen Positionen innerhalb einer Textzeichenfolge in Excel 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 anschließend den AutoAusfüll-Griff nach unten, um die Formel auf weitere 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 steht A1 für die Zelle, die die Textzeichenfolge enthält, aus der Sie Zahlen extrahieren möchten. Passen Sie diese Zellreferenz entsprechend Ihren Anforderungen an.
Anschließend werden Zahlen aus beliebigen Positionen innerhalb einer Textzeichenfolge in den angegebenen Zellen extrahiert, wie im obigen Screenshot dargestellt.
Extrahieren Sie Zahlen aus gemischten alphanumerischen Textzeichenfolgen mühelos mit Kutools für Excel
In diesem Abschnitt stellen wir Ihnen das „Text extrahieren“-Tool von Kutools für Excel vor – mit ihm extrahieren Sie ganz einfach Zahlen aus einer Textzeichenfolge.
1. Klicken Sie auf Kutools > Text > Text extrahieren, um die Funktion zu aktivieren.
2. Führen Sie im Text extrahieren-Dialogfeld die folgenden Einstellungen durch.
, um den Zellbereich auszuwählen, aus dem Sie Text extrahieren möchten;
Hinweis: Wenn Sie das Kontrollkästchen Als Formel einfügenWenn Sie die Option in der linken unteren Ecke des Dialogfelds aktivieren, wird das Ergebnis als Formel in die Ergebniszellen eingefügt und aktualisiert sich automatisch, sobald sich der Wert der Bezugszelle ändert.
3. Wählen Sie im Text extrahieren-Dialogfeld eine leere Zelle aus, um das Ergebnis der Textextraktion auszugeben, und klicken Sie anschließend auf OK.
Anschließend werden Zahlen aus beliebigen Positionen innerhalb einer Textzeichenfolge extrahiert. Siehe Screenshot:
Wenn Sie eine kostenlose 30-Tage-Testversion dieses Dienstprogramms nutzen möchten, klicken Sie bitte hier, um sie herunterzuladen, und führen Sie anschließend die Aktion gemäß den oben beschriebenen Schritten aus.
Behalten Sie ausschließlich Zahlen in gemischten alphanumerischen Textzeichenfolgen mithilfe von VBA-Code
Der folgende VBA-Code hilft Ihnen, alle nicht-numerischen Zeichen aus einem begrenzten Bereich zu entfernen und ausschließlich Zahlen in den Zellen zu behalten. Gehen Sie dazu wie folgt vor:
HinweisDa der Code den ursprünglichen Bereich direkt bearbeitet, sichern Sie unbedingt eine Kopie, um Datenverlust zu vermeiden.
1. Drücken Sie gleichzeitig die Tasten Alt+F11, um das Fenster Microsoft Visual Basic für Applikationen zu öffnen.
2. Klicken Sie im Fenster Microsoft Visual Basic für Applikationenauf Einfügen>Modul. Kopieren Sie anschließend den folgenden VBA-Code und fügen Sie ihn in das Modulfenster ein.
VBA-Code: Entfernen aller nicht-numerischen Zeichen aus Zellen
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 Sub3. Drücken Sie die Taste F5, um den Code auszuführen. Es öffnet sich daraufhin das Dialogfeld Kutools für Excel. Wählen Sie den Bereich mit den gemischten alphanumerischen Textzeichenfolgen aus, aus denen Sie ausschließlich die Zahlen behalten möchten, und klicken Sie auf die Schaltfläche OK.
Nun wurden alle nicht-numerischen Zeichen im ausgewählten Bereich sofort entfernt – es verbleiben ausschließlich Zahlen. Siehe Screenshot:
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