Wie extrahiert man Text vor/nach dem zweiten Leerzeichen oder Komma in Excel?

Beim Arbeiten mit Textdaten in Excel stoßen Sie möglicherweise häufig auf Zeichenketten, die mehrere Leerzeichen, Kommas oder andere Trennzeichen enthalten. Während die Funktion "Text in Spalten" in Excel den Inhalt einer Zelle basierend auf einem gewählten Trennzeichen in separate Spalten aufteilen kann, gibt es Zeiten, in denen Sie eine spezifischere Lösung benötigen – wie zum Beispiel alles vor oder nach dem zweiten Leerzeichen oder Komma in einer Zelle zu extrahieren. Dieser Artikel stellt mehrere praktische Methoden vor, um dies zu erreichen, einschließlich Formeln, die Text vor oder nach dem zweiten Leerzeichen/Komma auswählen, robuste Lösungen mit VBA-Makros für Automatisierung oder größere Datensätze und vielseitige Optionen mit Power Query für fortgeschrittene Daten-Vorverarbeitung.

Text vor dem zweiten Leerzeichen oder Komma mit Formel extrahieren

Text nach dem zweiten Leerzeichen oder Komma mit Formel extrahieren

Text vor oder nach dem zweiten Trennzeichen (Leerzeichen/Komma) mit VBA-Code extrahieren

Text vor dem zweiten Leerzeichen oder Komma mit Formel extrahieren

Wenn Sie den Teil einer Zelle isolieren möchten, der vor dem zweiten Leerzeichen erscheint, können Sie eine Kombination von Excel-Funktionen verwenden.

1. Geben Sie die folgende Formel in eine leere Zelle ein, in der Sie das Ergebnis anzeigen möchten (z. B. Zelle C2):

=IF(ISERROR(FIND(" ",A2,FIND(" ",A2,1)+1)),A2,LEFT(A2,FIND(" ",A2,FIND(" ",A2,1)+1)))

2. Drücken Sie die Eingabetaste. Ziehen Sie dann mit dem Ausfüllkästchen die Formel für alle Zeilen nach unten, die Sie verarbeiten möchten. Das Ergebnis zeigt den gesamten Text vor dem zweiten Leerzeichen in jeder entsprechenden Zeile an – siehe Screenshot unten:

=IF(ISERROR(FIND(",",A2,FIND(",",A2,1)+1)),A2,LEFT(A2,FIND(",",A2,FIND(",",A2,1)+1)))

Dieser Ansatz ist schnell und effizient für einmalige Extraktionen oder beim Arbeiten mit moderat großen Datensätzen. Wenn der Text jedoch sehr unregelmäßig ist oder die Anzahl der Trennzeichen zwischen den Zeilen stark variiert, sind möglicherweise zusätzliche Formelanpassungen erforderlich. Achten Sie auch auf zusätzliche Leerzeichen oder leere Zellen, die die Ergebnisse beeinflussen könnten. Überprüfen Sie die Ausgabe immer doppelt, wenn Sie sie auf kritische Daten anwenden.

Entfesseln Sie die Magie von Excel mit Kutools AI

• Intelligente Ausführung: Führen Sie Zellenoperationen durch, analysieren Sie Daten und erstellen Sie Diagramme – alles angetrieben durch einfache Befehle.
• Benutzerdefinierte Formeln: Erstellen Sie maßgeschneiderte Formeln, um Ihre Arbeitsabläufe zu optimieren.
• VBA-Codierung: Schreiben und implementieren Sie VBA-Code mühelos.
• Formelinterpretation: Verstehen Sie komplexe Formeln mit Leichtigkeit.
• Textübersetzung: Überwinden Sie Sprachbarrieren in Ihren Tabellen.

Erweitern Sie Ihre Excel-Fähigkeiten mit KI-gestützten Tools. Laden Sie jetzt herunter und erleben Sie eine Effizienz wie nie zuvor!

Text nach dem zweiten Leerzeichen oder Komma mit Formel extrahieren

Um Text zu extrahieren, der nach dem zweiten Leerzeichentrennzeichen in einer Zelle erscheint, können Sie die Funktionen TEIL und FINDEN zusammen verwenden.

1. Geben Sie die folgende Formel in die Zielformel ein (z. B. D2):

=MID(A2, FIND(" ", A2, FIND(" ", A2)+1)+1,256)

2. Drücken Sie die Eingabetaste. Ziehen Sie das Ausfüllkästchen durch alle Zeilen, die extrahiert werden müssen. Dadurch wird alles nach dem zweiten Leerzeichen für jede Zeile angezeigt, wie im Screenshot dargestellt:

=MID(A2, FIND(",", A2, FIND(",", A2)+1)+1,256)

Text vor oder nach dem zweiten Trennzeichen (Leerzeichen/Komma) mit VBA-Code extrahieren

Für größere Datensätze, wiederholte Extraktionen oder Automatisierungsszenarien ist die Verwendung eines VBA-Makros eine effektive Lösung. VBA ermöglicht es Ihnen, Text vor oder nach dem zweiten Leerzeichen, Komma oder einem beliebigen anderen Trennzeichen zu extrahieren – wodurch die Effizienz bei der Arbeit mit verschiedenen Datenstrukturen oder bei Bedarf nach einer wiederholbaren Routine erhöht wird.

1. Wählen Sie Entwickler > Visual Basic. Im geöffneten Microsoft Visual Basic for Applications-Fenster klicken Sie auf Einfügen > Modul und geben den folgenden Code in das Modulfenster ein:

Sub ExtractTextSecondDelimiter()
    Dim rng As Range
    Dim cell As Range
    Dim sep As String
    Dim direction As String
    Dim arr As Variant
    Dim result As String
    Dim pos1 As Long
    Dim pos2 As Long
    Dim xTitleId As String
    Dim outputCell As Range
    Dim i As Long
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    Set rng = Application.Selection
    Set rng = Application.InputBox("Select text range to extract from", xTitleId, rng.Address, Type:=8)
    If rng Is Nothing Then Exit Sub
    sep = Application.InputBox("Enter delimiter (e.g. space or comma)", xTitleId, " ", Type:=2)
    If sep = "" Then Exit Sub
    direction = Application.InputBox("Type 'before' for text before, 'after' for text after the 2nd delimiter", xTitleId, "before", Type:=2)
    If direction = "" Then Exit Sub
    Set outputCell = Application.InputBox("Select the first cell to output the result", xTitleId, Type:=8)
    If outputCell Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    i = 0
    For Each cell In rng
        If Not IsEmpty(cell.Value) Then
            pos1 = InStr(1, cell.Value, sep)
            If pos1 > 0 Then
                pos2 = InStr(pos1 + 1, cell.Value, sep)
                If pos2 > 0 Then
                    If LCase(direction) = "before" Then
                        result = Left(cell.Value, pos2 - 1)
                    ElseIf LCase(direction) = "after" Then
                        result = Mid(cell.Value, pos2 + Len(sep))
                    Else
                        result = cell.Value
                    End If
                Else
                    result = cell.Value
                End If
            Else
                result = cell.Value
            End If
            outputCell.Offset(i, 0).Value = result
        End If
        i = i + 1
    Next
    Application.ScreenUpdating = True
    MsgBox "Extraction completed.", vbInformation, xTitleId
End Sub

2. Nachdem Sie den Code eingefügt haben, klicken Sie auf Ausführen oder drücken Sie F5 um ihn auszuführen. Ein Dialogfeld fordert Sie auf, Ihren Datenbereich auszuwählen, dann das Trennzeichen einzugeben, anzugeben, ob Sie den Text „vor“ oder „nach“ dem zweiten Auftreten möchten, und schließlich eine Zelle auszuwählen, um das Ergebnis auszugeben. Das Makro gibt das extrahierte Ergebnis in Ihrer angegebenen Zelle aus.