Es gibt keine integrierte Funktion in Excel, um Zahlen aus einer Zeichenfolge in einer Zelle zu extrahieren (und umgekehrt - den numerischen Teil zu entfernen und den Textteil aus einer alphanumerischen Zeichenfolge zu extrahieren).
Dies kann jedoch mit einer Excel-Funktion oder einfachem VBA-Code erfolgen.
Lassen Sie mich Ihnen zuerst sagen, wovon ich spreche.
Angenommen, Sie haben einen Datensatz wie unten und Sie möchten Zahlen aus Zeichenfolgen extrahieren (wie unten):
Die gewählte Methode hängt auch von der verwendeten Excel-Version ab:
- Für Versionen von Excel vor 2016 müssen Sie eine etwas längere Formel verwenden
- Für Excel 2016 können Sie die neu eingeführte TEXTJOIN-Funktion verwenden
- VBA-Methode für alle Excel-Versionen verfügbar
Inhalte
Zahlen aus Zeichenfolgen in Excel extrahieren (Excel 2016-Formeln)
Diese Formel funktioniert nur in Excel 2016, da sie die neu eingeführte Funktion TEXTJOIN verwendet.
Darüber hinaus kann diese Formel Zahlen am Anfang, Ende oder in der Mitte einer Textzeichenfolge extrahieren.
Beachten Sie, dass die in diesem Abschnitt beschriebene TEXTJOIN-Formel alle numerischen Zeichen zusammenfügt.Wenn der Text beispielsweise „10 Tickets kosten 200 $“ lautet, ist das Ergebnis 10200.
Angenommen, Sie haben einen Datensatz wie den folgenden und Sie möchten Zahlen aus Zeichenfolgen in jeder Zelle extrahieren:
Die folgende Formel gibt Ihnen den numerischen Teil einer Zeichenfolge in Excel.
=TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),""))
Dies ist eine Matrixformel, daher müssen Sie " Strg + Umschalt + Eingabe " anstatt die Eingabetaste zu verwenden.
Diese Formel gibt leer (leere Zeichenfolge) zurück, wenn die Textzeichenfolge keine Zahlen enthält.
Wie funktioniert diese Formel?
Lassen Sie mich diese Formel aufschlüsseln und versuchen zu erklären, wie sie funktioniert:
- ROW(INDIRECT(“1:”&LEN(A2))) – Dieser Teil der Formel ergibt eine Reihe von Zahlen, beginnend bei eins.Die LEN-Funktion in der Formel gibt die Gesamtzahl der Zeichen in der Zeichenfolge zurück.在“成本為 100 美元”的情況下，它將返回 19。因此，公式將變為 ROW(INDIRECT(“1:19”)。然後 ROW 函數將返回一系列數字 – {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 gibt ein Array von #WERTEN zurück!Stringbasierte Fehler oder Zahlen.Alle Textzeichen in der Zeichenfolge werden zu #VALUE!Fehler und alle Werte bleiben unverändert.Dies geschieht, wenn wir die MID-Funktion mit 1 multiplizieren.
- IFERROR((MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1)*1),””) – Bei Verwendung der IFERROR-Funktion werden alle #WERTE entfernt!Fehler, es bleiben nur Zahlen.Die Ausgabe dieses Teils sieht folgendermaßen aus: {"";"";"";"";"";"";"";"";"";"";"";"";"";" ";"";"";1;0;0}
- =TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),"")) – Die TEXTJOIN-Funktion kombiniert jetzt einfach Zeichenketten. Immer noch da (nur Zahlen) und ignoriert leere Zeichenketten.
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 wechseln, wählen Sie den Teil der Formel aus, den Sie ausgeben möchten, und drücken Sie F9.Sie werden die Ergebnisse sofort sehen.Denken Sie dann daran, Strg + Z zu drücken oder Escape zu drücken.Drücken Sie nicht die Eingabetaste.
Sie können dieselbe Logik auch verwenden, um Textteile aus alphanumerischen Zeichenfolgen zu extrahieren.Hier ist die Formel, um den Textteil aus der Zeichenfolge zu erhalten:
=TEXTJOIN("",TRUE,IF(ISERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),MID(A2,ROW(INDIRECT("1:"&LEN) (A2))),1),""))
Eine kleine Änderung an dieser Formel besteht darin, dass die IF-Funktion verwendet wird, um das Array, das wir von der MID-Funktion erhalten, auf Fehler zu überprüfen.Wenn es sich um einen Fehler handelt, behalten Sie den Wert bei, ansonsten ersetzen Sie ihn durch ein Leerzeichen.
Verwenden Sie dann TEXTJOIN, um alle Textzeichen zu kombinieren.
注意: Obwohl diese Formel gültig ist, verwendet sie eine flüchtige Funktion (die INDIREKT-Funktion).Dies bedeutet, dass es einige Zeit dauern kann, bis Sie Ergebnisse erhalten, wenn Sie es mit einem großen Datensatz verwenden.Erstellen Sie am besten ein Backup, bevor Sie diese Formel in Excel verwenden.
Zahlen aus Strings in Excel extrahieren (für Excel 2013/2010/2007)
Wenn Sie Excel 2013, 2010 oder 2007 haben, können Sie keine TEXTJOIN-Formeln verwenden, daher müssen Sie dazu komplexe Formeln verwenden.
Angenommen, Sie haben einen Datensatz wie unten und Sie möchten alle Zahlen in Zeichenfolgen in jeder Zelle extrahieren.
Die folgende Formel wird dies tun:
=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 gibt leer (leere Zeichenfolge) zurück, wenn die Textzeichenfolge keine Zahlen enthält.
Obwohl dies eine Matrixformel ist, SieKeine NotwendigkeitVerwenden Sie "Control-Shift-Enter", um es zu verwenden.Für diese Formel funktioniert eine einfache Eingabe.
Die Anerkennung für diese Formel geht an das ErstaunlicheHerr Excel-Forum.
Auch diese Formel extrahiert alle Zahlen in der Zeichenfolge unabhängig von der Position.Wenn der Text beispielsweise „10 Tickets kosten 200 $“ lautet, ist das Ergebnis 10200.
注意: Obwohl diese Formel gültig ist, verwendet sie eine flüchtige Funktion (die INDIREKT-Funktion).Dies bedeutet, dass es einige Zeit dauern kann, bis Sie Ergebnisse erhalten, wenn Sie es mit einem großen Datensatz verwenden.Erstellen Sie am besten ein Backup, bevor Sie diese Formel in Excel verwenden.
Trennen Sie Text und Zahlen in Excel mit VBA
Wenn Sie häufig Text und Zahlen trennen (oder Zahlen aus Text extrahieren) müssen, können Sie auch die VBA-Methode verwenden.
Alles, was Sie tun müssen, ist eine benutzerdefinierte benutzerdefinierte Funktion (UDF) in Excel mit einfachem VBA-Code zu erstellen und dann diese VBA-Formel anstelle einer langen und komplexen Formel zu verwenden.
Lassen Sie mich Ihnen zeigen, wie Sie zwei Formeln in VBA erstellen - eine zum Extrahieren von Zahlen und eine zum Extrahieren von Text aus Zeichenfolgen.
Zahlen aus Strings in Excel extrahieren (mit VBA)
In diesem Teil zeige ich Ihnen, wie Sie eine benutzerdefinierte Funktion erstellen, um nur den numerischen Teil aus einer Zeichenfolge zu erhalten.
Hier 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 = Ergebnisendfunktion
Hier sind die Schritte, um diese Funktion zu erstellen und sie dann in einem Arbeitsblatt zu verwenden:
Sie können jetzt die GetText-Funktion auf dem Arbeitsblatt verwenden.Da wir im Code selbst viel Arbeit geleistet haben, brauchen Sie nur die Formel =GetNumeric(A2) zu verwenden.
Dadurch erhalten Sie sofort den numerischen Teil der Zeichenfolge.
Da die Arbeitsmappe jetzt VBA-Code enthält, müssen Sie sie mit der Erweiterung .xls oder .xlsm speichern.
Wenn Sie diese Formel häufig verwenden müssen, können Sie sie auch in Ihrer persönlichen Makro-Arbeitsmappe speichern.Dadurch können Sie diese benutzerdefinierte Formel in jeder Excel-Arbeitsmappe verwenden, die Sie verwenden.
Text aus String in Excel extrahieren (mit VBA)
In diesem Teil zeige ich Ihnen, wie Sie eine benutzerdefinierte Funktion erstellen, um nur den Textteil aus einer Zeichenfolge zu erhalten.
Hier 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 in einem Arbeitsblatt zu verwenden:
Sie können jetzt die GetNumeric-Funktion im Arbeitsblatt verwenden.Da wir im Code selbst viel Arbeit geleistet haben, brauchen Sie nur die Formel =GetText(A2) zu verwenden.
Dadurch erhalten Sie sofort den numerischen Teil der Zeichenfolge.
Da die Arbeitsmappe jetzt VBA-Code enthält, müssen Sie sie mit der Erweiterung .xls oder .xlsm speichern.
Wenn Sie diese Formel häufig verwenden müssen, können Sie sie auch in Ihrer persönlichen Makro-Arbeitsmappe speichern.Dadurch können Sie diese benutzerdefinierte Formel in jeder Excel-Arbeitsmappe verwenden, die Sie verwenden.
