Excel: Zeichen, Wörter, Zahlen aus Textzeichenfolgen entfernen

Wenn Sie in Excel eine Liste von Textzeichenfolgen haben, die unerwünschte Zeichen, Zahlen oder Symbole enthalten, müssen Sie möglicherweise bestimmte Elemente basierend auf ihrer Position oder ihrem Typ entfernen. Sei es das Kürzen von Zeichen von links, rechts oder aus der Mitte, oder das Entfernen bestimmter Zeichen und Zahlen – Excel bietet mehrere Möglichkeiten, Ihre Daten zu bereinigen. Dieses Tutorial stellt Schritt-für-Schritt-Methoden vor, die Formeln, benutzerdefinierte Funktionen (UDFs) und integrierte Funktionen verwenden, um unerwünschte Zeichen, Wörter und Zahlen aus Ihren Textzeichenfolgen mühelos zu entfernen.

 Zeichen von links, rechts oder aus der Mitte der Textzeichenfolgen entfernen

Es ist eine häufige Aufgabe für die meisten von uns, einige Zeichen von links, rechts oder aus der Mitte von Textzeichenfolgen in Excel-Arbeitsblättern zu entfernen. In diesem Abschnitt werden wir einige schnelle und einfache Tricks zur Lösung dieser Aufgabe vorstellen.

1.1 Entfernen der ersten n Zeichen aus Textzeichenfolgen

Wenn Sie die ersten n Zeichen aus einer Liste von Textzeichenfolgen entfernen müssen, können Ihnen die folgenden Methoden helfen.

Durch die Verwendung von Formeln

Normalerweise können Sie zum Löschen von Zeichen am Anfang der Textzeichenfolgen entweder die Funktion ERSETZEN oder eine Kombination aus den Funktionen RECHTS und LÄNGE verwenden.

ERSETZEN-Funktion zum Entfernen der ersten N Zeichen:

• "Zeichenfolge": Die Textzeichenfolge, aus der Sie Zeichen entfernen möchten;
• "num_chars": Die Anzahl der Zeichen, die Sie entfernen möchten.

Um beispielsweise die ersten 2 Zeichen aus den Zellen zu entfernen, verwenden Sie bitte die folgende Formel und ziehen dann den Ausfüllknauf, um die Formel in andere Zellen zu kopieren, siehe Screenshot:

=REPLACE(A4, 1, 2, "")

RIGHT- und LEN-Funktionen zum Entfernen der ersten N Zeichen:

• "Zeichenfolge": Die Textzeichenfolge, aus der Sie Zeichen entfernen möchten;
• "num_chars": Die Anzahl der Zeichen, die Sie entfernen möchten.

Um die ersten 2 Zeichen aus Zellen zu entfernen, wenden Sie bitte die folgende Formel an:

=RIGHT(A4,LEN(A4)-2)

 Durch die Verwendung einer benutzerdefinierten Funktion

Um die ersten n Zeichen aus Zellen zu entfernen, können Sie auch eine benutzerdefinierte Funktion erstellen, um diese Aufgabe zu lösen. Gehen Sie dazu wie folgt vor:

1. Halten Sie die Tasten "Alt + F11" gedrückt, um das Fenster "Microsoft Visual Basic for Applications" zu öffnen.

2. Klicken Sie auf "Einfügen" > "Modul" und fügen Sie den folgenden Code in das Modulfenster ein.

VBA-Code: Entfernen der ersten n Zeichen aus Textzeichenfolgen

Function removeFirstx(rng As String, cnt As Long)
'Updateby Extendoffice
removeFirstx = Right(rng, Len(rng) - cnt)
End Function

3. Gehen Sie dann zurück zum Arbeitsblatt, und geben Sie die Formel "=removefirstx(A4,2)" in eine leere Zelle ein. Ziehen Sie anschließend den Ausfüllknauf nach unten, um die gewünschten Ergebnisse zu erhalten, siehe Screenshot:

Hinweis: In dieser Formel: "A4" ist die Zelle, aus der Sie Zeichen entfernen möchten; Die Zahl "2" gibt die Anzahl der Zeichen an, die Sie vom Anfang der Textzeichenfolge entfernen möchten.

1.2 Entfernen der letzten n Zeichen aus Textzeichenfolgen

Um eine bestimmte Anzahl von Zeichen von der rechten Seite von Textzeichenfolgen zu entfernen, können Sie auch eine Formel oder eine benutzerdefinierte Funktion verwenden.

 Durch die Verwendung von Formeln

Um die letzten n Zeichen aus den Textzeichenfolgen zu entfernen, können Sie eine Formel verwenden, die auf den Funktionen LINKS und LÄNGE basiert.

LINKS- und LÄNGE-Funktionen, um die letzten N Zeichen zu entfernen:

• "string": Die Textzeichenfolge, aus der Sie Zeichen entfernen möchten;
• "num_chars": Die Anzahl der Zeichen, die Sie entfernen möchten.

Um 3 Zeichen vom Ende der Textzeichenfolgen zu löschen, verwenden Sie bitte diese Formel und ziehen dann den Ausfüllknauf, um die Formel in andere Zellen zu kopieren, siehe Screenshot:

=LEFT(A4, LEN(A4) - 3)

 Durch die Verwendung einer benutzerdefinierten Funktion

Eine benutzerdefinierte Funktion kann Ihnen ebenfalls dabei helfen, die letzten n Zeichen aus einer Liste von Zellen zu entfernen. Führen Sie diese Schritte aus:

1. Halten Sie die Tasten "Alt + F11" gedrückt, um das Fenster "Microsoft Visual Basic for Applications" zu öffnen.

2. Klicken Sie auf "Einfügen" > "Modul" und fügen Sie den folgenden Code in das Modulfenster ein.

VBA-Code: Entfernen der letzten n Zeichen aus Textzeichenfolgen

Function removeLastx(rng As String, cnt As Long)
'Updateby Extendoffice
removeLastx = Left(rng, Len(rng) - cnt)
End Function

3. Gehen Sie dann zurück zum Arbeitsblatt und geben Sie diese Formel ein: "=removelastx(A4,3)" in eine leere Zelle, und ziehen Sie den Ausfüllkursor nach unten, um die gewünschten Ergebnisse zu erhalten, siehe Screenshot:

Hinweis: In dieser Formel: "A4" ist die Zelle, aus der Sie Zeichen entfernen möchten; Die Zahl "3" gibt die Anzahl der Zeichen an, die Sie vom Ende der Textzeichenfolge entfernen möchten.

1.3 Entfernen Sie die ersten, letzten n Zeichen oder Zeichen an bestimmten Positionen mithilfe einer leistungsstarken Funktion

Es kann mühsam sein, sich verschiedene Formeln zu merken, um Zeichen von links, rechts oder einer bestimmten Position in Textfolgen zu entfernen. „Kutools for Excel“ bietet eine leistungsstarke Funktion – „Zeichen an einer bestimmten Position entfernen“. Mit diesem praktischen Tool können Sie diese Aufgaben mit wenigen Klicks erledigen, ohne sich Formeln merken zu müssen.

1. Wählen Sie die Zellen aus, aus denen Sie Zeichen entfernen möchten, und klicken Sie dann auf „Kutools“ > „Text“ > „Zeichen an einer bestimmten Position entfernen“. Siehe Screenshot:

2. Führen Sie im Dialogfeld „Zeichen an einer bestimmten Position entfernen“ die folgenden Schritte aus:

2.1 Entfernen der ersten n Zeichen aus Zellen:

• Geben Sie im Textfeld "Zahlen" die Anzahl der Zeichen ein, die Sie aus den Zeichenfolgen entfernen möchten. In diesem Beispiel werde ich die ersten 2 Zeichen entfernen.
• Wählen Sie die Option "Von links" im Abschnitt "Position" aus.
• Klicken Sie dann auf die Schaltfläche "OK" oder "Anwenden", um das Ergebnis wie im folgenden Screenshot gezeigt zu erhalten.

2.2 Entfernen der letzten n Zeichen aus Zellen:

• Geben Sie im Textfeld "Zahlen" die Anzahl der Zeichen ein, die Sie aus den Zeichenfolgen entfernen möchten. In diesem Beispiel werde ich die letzten 3 Zeichen entfernen.
• Wählen Sie die Option "Von rechts" im Abschnitt "Position" aus.
• Klicken Sie dann auf die Schaltfläche "Ok" oder "Anwenden", um das Ergebnis wie im folgenden Screenshot gezeigt zu erhalten.

2.3 Entfernen Sie n Zeichen von einer bestimmten Position in den Zellen:

Wenn Sie eine bestimmte Anzahl von Zeichen ab einer bestimmten Position in den Textzeichenfolgen entfernen müssen, zum Beispiel 3 Zeichen beginnend ab dem dritten Zeichen der Zeichenfolge entfernen.

• Geben Sie im Textfeld "Zahlen" die Anzahl der Zeichen ein, die Sie aus den Zeichenfolgen entfernen möchten. In diesem Beispiel werde ich 3 Zeichen von einer bestimmten Position entfernen.
• Wählen Sie die Option „Angeben“ und geben Sie die Zahl, ab der Sie Zeichen entfernen möchten, in das Textfeld im Abschnitt „Position“ ein. Hier werde ich Zeichen ab dem dritten Zeichen entfernen.
• Klicken Sie dann auf die Schaltfläche "OK" oder "Anwenden", um das Ergebnis wie im folgenden Screenshot gezeigt zu erhalten.

1.4 Entfernen Sie sowohl die ersten n als auch die letzten n Zeichen aus Textzeichenfolgen mit einer Formel

Wenn Sie einige Zeichen auf beiden Seiten von Textzeichenfolgen in Excel entfernen müssen, können Sie die Funktionen MID und LEN kombinieren, um eine Formel zur Bewältigung dieser Aufgabe zu erstellen.

• "Zeichenfolge": Die Textzeichenfolge, aus der Sie Zeichen entfernen möchten;
• "left_chars": Die Anzahl der Zeichen, die von links entfernt werden sollen;
• "right_chars": Die Anzahl der Zeichen, die von rechts entfernt werden sollen.

Um beispielsweise die ersten 7 Zeichen und die letzten 5 Zeichen gleichzeitig aus Textzeichenfolgen zu entfernen, geben Sie bitte die folgende Formel in eine leere Zelle ein:

=MID(A4, 7+1, LEN(A4) - (7+5))

Hinweis: In dieser Formel: "A4" ist die Zelle, aus der Sie Zeichen entfernen möchten; Die Zahl "7" ist die Anzahl der Zeichen, die Sie von der linken Seite entfernen möchten; Die Zahl "5" ist die Anzahl der Zeichen, die Sie von der rechten Seite entfernen möchten.

Ziehen Sie dann den Ausfüllknauf nach unten, bis zu der Stelle, an der Sie diese Formel anwenden möchten, und Sie erhalten das Ergebnis, wie im folgenden Screenshot gezeigt:

 Unerwünschte / spezielle Zeichen aus Textzeichenfolgen entfernen

Beim Importieren von Daten aus einer anderen Quelle in Excel können viele spezielle oder unerwünschte Zeichen in Ihr Arbeitsblatt eingefügt werden. Um diese unerwünschten Zeichen wie #@$%^&, Leerzeichen, Zahlen, nicht-numerische Zeichen, Zeilenumbrüche usw. zu entfernen, bietet dieser Abschnitt einige nützliche Methoden, die Ihnen helfen können.

2.1 Entfernen Sie einige spezielle Zeichen aus Textzeichenfolgen

Wenn es in den Textzeichenfolgen einige Sonderzeichen wie %^&*() gibt, können Sie die folgenden drei Tricks anwenden, um diese Art von Zeichen zu entfernen.

 Entfernen Sie mehrere Sonderzeichen aus Textzeichenfolgen mit der SUBSTITUTE-Funktion

Normalerweise können Sie in Excel mehrere SUBSTITUTE-Funktionen verschachteln, um jedes bestimmte Zeichen durch nichts zu ersetzen. Die allgemeine Syntax lautet:

• "string_cell": Die Zelle enthält die Textzeichenfolge, aus der Sie Sonderzeichen entfernen möchten;
• "char1, char2, char3": Die unerwünschten Zeichen, die Sie entfernen möchten.

Kopieren Sie nun die folgende Formel oder geben Sie sie in eine leere Zelle ein:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "#", ""), "&", ""), "*", ""), "%", "")

Ziehen Sie dann den Ausfüllkursor nach unten in die Zellen, in denen Sie diese Formel anwenden möchten, und alle angegebenen unerwünschten Zeichen werden auf einmal entfernt, siehe Screenshot:

Tipp: Wenn es mehr Zeichen gibt, die Sie entfernen möchten, müssen Sie einfach weitere SUBSTITUTE-Funktionen in die Formel einbetten.

 Entfernen Sie mehrere Sonderzeichen aus Textzeichenfolgen mit einer benutzerdefinierten Funktion

Die oben genesteten SUBSTITUTE-Funktionen funktionieren gut, wenn nur wenige Sonderzeichen entfernt werden müssen. Wenn Sie jedoch Dutzende von Zeichen entfernen müssen, kann die Formel zu lang und schwer zu verwalten werden. In diesem Fall kann die folgende benutzerdefinierte Funktion Ihnen helfen, diese Aufgabe schnell und einfach zu erledigen.

1. Halten Sie die Tasten "Alt + F11" gedrückt, um das Fenster "Microsoft Visual Basic for Applications" zu öffnen.

2. Klicken Sie auf "Einfügen" > "Modul" und fügen Sie den folgenden Code in das Modulfenster ein.

VBA-Code: Mehrere Sonderzeichen aus Textzeichenfolgen entfernen

Function RemoveUnwantedChars(Str As String, xchars As String)
'Updateby Extendoffice
    For Index = 1 To Len(xchars)
        Str = Replace(Str, Mid(xchars, Index, 1), "")
    Next
    RemoveUnwantedChars = Str
End Function

3. Schließen Sie dann das Codefenster und kehren Sie zum Arbeitsblatt zurück. Geben Sie die Formel "=RemoveUnwantedChars(A2, $D$2)" in eine leere Zelle ein, um das Ergebnis auszugeben, und ziehen Sie den Ausfüllkursor nach unten, um die gewünschten Ergebnisse zu erhalten, siehe Screenshot:

Hinweis: In der obigen Formel: "A2" ist die Zelle, aus der Sie Zeichen entfernen möchten; "$D$2" enthält die Sonderzeichen, die Sie entfernen möchten (Sie können beliebige andere Sonderzeichen eingeben, die Sie benötigen).

 Entfernen Sie mehrere Sonderzeichen aus Textzeichenfolgen mithilfe einer erstaunlichen Funktion

Wenn Sie "Kutools für Excel" installiert haben, können Sie mit der Funktion "Bestimmte Zeichen löschen" verschiedene Arten von Zeichen entfernen, wie z. B. numerische Zeichen, alphabetische Zeichen, nicht-druckbare Zeichen… aus einer Liste von Zellen, je nach Bedarf.

1. Wählen Sie den Zellbereich aus, aus dem Sie Sonderzeichen entfernen möchten, und klicken Sie dann auf „Kutools“ > „Text“ > „Remove Characters“. Siehe Screenshot:

2. Im Dialogfeld „Bestimmte Zeichen löschen“:

• Aktivieren Sie die Option „Benutzerdefiniert“ im Abschnitt „Bestimmte Zeichen löschen“.
• Geben Sie dann die Sonderzeichen in das Textfeld ein, die Sie entfernen möchten.
• Klicken Sie dann auf die Schaltfläche "Ok" oder "Anwenden", um die angegebenen Zeichen sofort zu entfernen. Siehe Screenshot:

2.2 Entfernen Sie alle Zahlen aus Textzeichenfolgen

Wenn Sie eine Liste von Textzeichenketten haben, die Zahlen, Buchstaben und Sonderzeichen enthalten, und Sie nun alle Zahlen entfernen und andere Zeichen beibehalten möchten, bietet dieser Abschnitt einige praktische Methoden, um Ihnen zu helfen.

 Zahlen aus Textzeichenfolgen mit der SUBSTITUTE-Funktion entfernen

In Excel kann die verschachtelte SUBSTITUTE-Funktion helfen, alle Zahlen durch nichts zu ersetzen. Daher können Sie die folgende Formel verwenden, um alle Zahlen aus den Zellen zu entfernen:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""),0,"")

Ziehen Sie dann das Ausfüllkästchen nach unten in die Zellen, in denen Sie diese Formel anwenden möchten, und alle Zahlen werden aus der Liste der Textzeichenfolgen gelöscht, siehe Screenshot:

 Zahlen aus Textzeichenfolgen mit der TEXTJOIN-Funktion entfernen

Wenn Sie Excel 2019, 2021 oder 365 verwenden, kann die neue TEXTVERBINDEN-Funktion ebenfalls dabei helfen, Zahlen aus Textzeichenfolgen zu entfernen.

Bitte kopieren Sie die folgende Formel in eine leere Zelle, und drücken Sie dann gleichzeitig die Tasten Ctrl + Umschalt + Eingabe, um das erste Ergebnis zu erhalten:

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

Kopieren Sie dann die Formel in die darunterliegenden Zellen, in denen Sie diese Formel anwenden möchten, siehe Screenshot:

Hinweis: Diese TEXTVERBINDEN-Funktion ist nur in Excel 2019, 2021 und Office 365 verfügbar.

 Zahlen aus Textzeichenfolgen mit benutzerdefinierter Funktion entfernen

Neben den beiden oben genannten Formeln kann Ihnen auch eine benutzerdefinierte Funktion helfen. Bitte folgen Sie den folgenden Schritten:

1. Halten Sie die Tasten „Alt + F11“ gedrückt, um das Fenster „Microsoft Visual Basic for Applications“ zu öffnen.

2. Klicken Sie auf „Einfügen“ > „Modul“ und fügen Sie den folgenden Code in das Modulfenster ein.

VBA-Code: Zahlen aus Textzeichenfolgen entfernen

Function RemoveNumbers(Txt As String) As String
'Updateby Extendoffice
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "[0-9]"
RemoveNumbers = .Replace(Txt, "")
End With
End Function

3. Schließen Sie dann das Codefenster und kehren Sie zum Arbeitsblatt zurück. Geben Sie die Formel: =RemoveNumbers(A2) in eine leere Zelle ein, und ziehen Sie den Ausfüllknauf nach unten in die Zellen, auf die Sie diese Formel anwenden möchten, siehe Screenshot:

 Zahlen aus Textzeichenfolgen mit einer praktischen Option entfernen

Wenn Sie müde sind von den komplizierten Formeln, lassen Sie mich Ihnen ein einfaches Tool zeigen – „Remove Characters“ von „Kutools für Excel“. Mit dieser praktischen Funktion können Sie diese Aufgabe mit nur wenigen Klicks erledigen.

1. Wählen Sie den Zellbereich aus, in dem Sie Zahlen entfernen möchten, und klicken Sie dann auf „Kutools“ > „Text“ > „Bestimmte Zeichen löschen“.

2. Führen Sie im Dialogfeld „Bestimmte Zeichen löschen“ die folgenden Schritte aus:

• Aktivieren Sie die Option "Numerisch" im Abschnitt "Zeichen entfernen".
• Klicken Sie dann auf die Schaltfläche "Ok" oder "Anwenden", um die Zahlen sofort zu entfernen. Siehe Screenshot:

2.3 Nicht-numerische Zeichen aus Textzeichenfolgen entfernen

Um alle nicht-numerischen Zeichen zu entfernen und nur die Zahlen in Textzeichenfolgen beizubehalten, wird dieser Abschnitt einige Methoden zur Lösung dieser Aufgabe in Excel vorstellen.

 Nicht-numerische Zeichen aus Textzeichenfolgen mit Formel in Excel 2016 und früheren Versionen entfernen

Wenn Sie Excel 2016 oder frühere Versionen verwenden, sollten Sie eine komplexe Formel anwenden, um diese Aufgabe zu erledigen. Bitte kopieren Sie die folgende Formel in eine leere Zelle oder geben Sie sie ein:

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

Kopieren Sie dann die Formel in die darunterliegenden Zellen, in denen Sie diese Formel anwenden möchten, siehe Screenshot:

Hinweis: Wenn die Zahlen in einer Textzeichenfolge mit 0 beginnen, wird die 0 verloren gehen.

 Entfernen Sie nicht-numerische Zeichen aus Textzeichenfolgen mit der TEXTVERBINDEN-Funktion in Excel 2019, 2021, 365

Die obige Formel könnte für die meisten von uns zu schwierig zu verstehen sein. Wenn Sie Excel 2019, 2021 oder 365 haben, gibt es eine praktische Formel, die Ihnen helfen kann.

Bitte kopieren Sie die folgende Formel in eine leere Zelle, oder geben Sie sie ein, und drücken Sie gleichzeitig die Tasten "Strg + Umschalt + Eingabe", um das erste richtige Ergebnis zu erhalten:

=TEXTJOIN("",TRUE,IFERROR(MID(A2,ROW(INDIRECT("1:100")),1)+0,""))

Ziehen Sie dann den Ausfüllkursor nach unten in die Zellen, auf die Sie diese Formel anwenden möchten, und Sie erhalten das Ergebnis, wie im folgenden Screenshot dargestellt:

Hinweis: Mit dieser Formel können Sie sehen, dass die führenden Nullen beibehalten werden, da die Zahlen als Text zurückgegeben werden.

 Entfernen Sie nicht-numerische Zeichen aus Textzeichenfolgen mit einer benutzerdefinierten Funktion

Natürlich können Sie auch eine eigene benutzerdefinierte Funktion mit einer einfacheren Syntax erstellen. Gehen Sie dazu wie folgt vor:

1. Halten Sie die Tasten "Alt + F11" gedrückt, um das Fenster "Microsoft Visual Basic for Applications" zu öffnen.

2. Klicken Sie auf „Einfügen“ > „Modul“ und fügen Sie den folgenden Code in das Modulfenster ein.

VBA-Code: Nicht-numerische Zeichen aus Textzeichenfolgen entfernen

Function Removenonnumeric(str As String) As String
'Updateby Extendoffice
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "[^0-9]"
        Removenonnumeric = .Replace(str, "")
    End With
End Function

3. Schließen Sie dann das Codefenster und kehren Sie zum Arbeitsblatt zurück. Geben Sie die folgende Formel in eine leere Zelle ein: =Removenonnumeric(A2), und ziehen Sie den Ausfüllknauf nach unten in die Zellen, auf die Sie diese Formel anwenden möchten. Es werden nur Zahlen extrahiert, wie im folgenden Screenshot dargestellt:

 Entfernen Sie nicht-numerische Zeichen aus Textzeichenfolgen mit einer einfachen Funktion

Um nicht-numerische Zeichen in einem Zellbereich direkt zu löschen, kann das Hilfsprogramm "Zeichen entfernen" von "Kutools für Excel" dies mit nur wenigen Klicks erledigen.

1. Wählen Sie den Zellbereich aus, aus dem Sie nicht-numerische Zeichen entfernen möchten, und klicken Sie dann auf „Kutools“ > „Text“ > „Bestimmte Zeichen löschen“.

2. Im Dialogfeld „Bestimmte Zeichen löschen“ legen Sie bitte die folgenden Operationen fest:

• Aktivieren Sie die Option "Nicht-numerisch" im Abschnitt "Zeichen entfernen".
• Klicken Sie dann auf die Schaltfläche "Ok" oder "Anwenden", um alle nicht-numerischen Zeichen sofort zu entfernen. Siehe Screenshot:

2.4 Text und Zahlen aus einer Zelle in zwei Spalten aufteilen

Manchmal möchten Sie vielleicht Text und Zahlen aus Textzeichenfolgen in zwei getrennte Spalten extrahieren. Mit den folgenden Methoden können Sie diese Aufgabe schnell und einfach erledigen.

Text und Zahlen aus einer Zelle in zwei Spalten mit benutzerdefinierter Funktion aufteilen

Durch die Verwendung der folgenden benutzerdefinierten Funktion können Sie Text und Zahlen gleichzeitig extrahieren. Bitte folgen Sie den nachstehenden Schritten:

1. Halten Sie die Tasten "Alt + F11" gedrückt, um das Fenster "Microsoft Visual Basic for Applications" zu öffnen.

2. Klicken Sie auf "Einfügen" > "Modul" und fügen Sie den folgenden Code in das Modulfenster ein.

VBA-Code: Text und Zahlen aus Textzeichenfolgen in zwei Spalten aufteilen

Function SplitText(pWorkRng As Range, pIsNumber As Boolean) As String
'Updateby Extendoffice
Dim xLen As Long
Dim xStr As String
xLen = VBA.Len(pWorkRng.Value)
For i = 1 To xLen
    xStr = VBA.Mid(pWorkRng.Value, i, 1)
    If ((VBA.IsNumeric(xStr) And pIsNumber) Or (Not (VBA.IsNumeric(xStr)) And Not (pIsNumber))) Then
        SplitText = SplitText + xStr
    End If
Next
End Function

3. Schließen Sie dann das Codefenster und kehren Sie zum Arbeitsblatt zurück. Geben Sie die folgende Formel in eine leere Zelle ein: =SplitText(A2,FALSE), und ziehen Sie den Ausfüllknauf nach unten in die Zellen, in denen Sie diese Formel anwenden möchten, um den gesamten Text zu erhalten, siehe Screenshot:

4. Geben Sie dann diese Formel ein: =SplitText(A2,TRUE) in eine andere Zelle und ziehen Sie den Ausfüllknauf nach unten in die Zellen, in denen Sie diese Formel ausfüllen möchten, um die Zahlen zu erhalten, siehe Screenshot:

 Text und Zahlen aus einer Zelle mit einer einfachen Funktion in zwei Spalten aufteilen

Wenn Sie "Kutools für Excel" haben, kann Ihnen das Hilfsprogramm "Zellen aufteilen" dabei helfen, Zellen basierend auf beliebigen Trennzeichen, einer bestimmten Breite oder Text und Zahl in mehrere Spalten oder Zeilen aufzuteilen.

1. Wählen Sie den Zellbereich aus, den Sie aufteilen möchten, und klicken Sie dann auf „Kutools“ > „Zusammenführen & Aufteilen“ > „Zellen aufteilen“. Siehe Screenshot:

2. Wählen Sie im Dialogfeld „Zellen aufteilen“ die Option „Daten aufteilen“ unter dem Abschnitt „Typ“ aus, und aktivieren Sie dann „Nach Text und Zahl aufteilen“ im Abschnitt „Trennzeichen“, siehe Screenshot:

3. Klicken Sie dann auf die Schaltfläche "OK", und ein weiteres Dialogfeld „Zellen aufteilen“ wird angezeigt. Wählen Sie eine Zelle aus, um den getrennten Text und die Zahlen auszugeben, und klicken Sie dann auf die Schaltfläche „OK“. Wie Sie nun sehen können, werden der Text und die Zahlen in den ausgewählten Zellen sofort in zwei Spalten aufgeteilt, wie in der folgenden Demo dargestellt:

2.5 Zeilenumbruchzeichen aus Textzeichenfolgen entfernen

Ein Zeilenumbruch ermöglicht es, mehrere Zeilen in derselben Zelle in Excel zu haben. Manchmal erhalten Sie Zeilenumbrüche oder Wagenrückläufe, wenn Sie Daten von einer Website kopieren oder Ihre Zelleninhalte manuell mit der Tastenkombination „Alt + Enter“ trennen. In bestimmten Fällen möchten Sie die Zeilenumbrüche möglicherweise löschen, damit der Zelleninhalt als eine einzige Zeile erscheint, wie im folgenden Screenshot dargestellt. Im Folgenden stelle ich einige Methoden zur Lösung dieser Aufgabe in Excel vor.

 Entfernen Sie Zeilenumbruchzeichen aus Textzeichenfolgen mit der Funktion 'Suchen und Ersetzen'

In Excel können Sie die Funktion "Suchen und Ersetzen" verwenden, um Zeilenumbrüche zu entfernen. Gehen Sie wie folgt vor:

1. Wählen Sie den Datenbereich aus, aus dem Sie die Zeilenumbrüche entfernen möchten.

2. Klicken Sie anschließend auf „Start“ > „Suchen & Auswählen“ > „Ersetzen“ (oder drücken Sie die Tasten „Strg + H“), um das Dialogfeld „Suchen und Ersetzen“ zu öffnen, siehe Screenshot:

3. Im Popup-Dialogfeld „Suchen und Ersetzen“ führen Sie bitte die folgenden Schritte aus:

• Platzieren Sie den Cursor im Feld „Suchen nach“ und drücken Sie „Ctrl + J“ auf der Tastatur. Möglicherweise sehen Sie nichts, aber das Zeichen für den Zeilenumbruch wurde eingefügt.
• Im Feld "Ersetzen durch" lassen Sie dieses Feld leer, um die Zeilenumbrüche einfach zu löschen, oder drücken Sie einmal die Leertaste, um die Zeilenumbrüche durch Leerzeichen zu ersetzen.

4. Klicken Sie dann auf die Schaltfläche „Alle ersetzen“. Alle Zeilenumbrüche in den ausgewählten Zellen werden sofort gelöscht oder durch Leerzeichen ersetzt. Siehe Screenshot:

 Entfernen Sie Zeilenumbruchzeichen aus Textzeichenfolgen mit der SUBSTITUTE-Funktion

Sie können auch eine Formel erstellen, die auf den Funktionen SUBSTITUTE und CHAR basiert, um die Zeilenumbrüche aus den Textzeichenfolgen zu entfernen.

Bitte wenden Sie die folgende Formel an, um das Ergebnis zu erhalten:

=SUBSTITUTE(A2,CHAR(10),"")

Tipp: Die SUBSTITUTE-Funktion sucht und ersetzt das CHAR(10)-Zeichen, das den Zeilenumbruch darstellt, durch nichts. Wenn Sie möchten, dass das Ergebnis durch ein Komma und ein Leerzeichen getrennt wird, können Sie die folgende Formel verwenden:

=SUBSTITUTE(A2,CHAR(10),", ")

 Entfernen Sie Zeilenumbruchzeichen aus Textzeichenfolgen mit VBA-Code

Wenn Sie sich mit der Verwendung von VBA-Code auskennen, finden Sie hier auch einen Code für Sie. Bitte folgen Sie den folgenden Schritten:

1. Halten Sie die Tasten "Alt + F11" gedrückt, um das Fenster "Microsoft Visual Basic for Applications" zu öffnen.

2. Klicken Sie auf "Einfügen" > "Modul" und fügen Sie den folgenden Code in das Modulfenster ein.

VBA-Code: Zeilenumbrüche aus Textzeichenfolgen entfernen

Sub RemoveCarriage()
'Updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    Rng.Value = Replace(Rng.Value, Chr(10), "")
Next
End Sub

3. Drücken Sie dann die Taste "F5", um diesen Code auszuführen, und ein Eingabeaufforderungsfenster wird angezeigt. Wählen Sie den Bereich aus, aus dem Sie die Zeilenumbrüche entfernen möchten, wie im Screenshot unten gezeigt:

4. Klicken Sie anschließend auf die Schaltfläche „OK“, und alle Zeilenumbrüche werden aus dem ausgewählten Datenbereich entfernt.

 Entfernen Sie Zeilenumbruchzeichen aus Textzeichenfolgen mit einer intelligenten Option

Hier kann auch die Funktion "Zeichen entfernen" von "Kutools für Excel" Ihnen helfen, Zeilenumbrüche mühelos zu entfernen.

1. Wählen Sie den Zellbereich aus, in dem Sie Zeilenumbrüche entfernen möchten, und klicken Sie dann auf „Kutools“ > „Text“ > „Bestimmte Zeichen löschen“.

2. Im Dialogfeld „Bestimmte Zeichen löschen“ legen Sie bitte die folgenden Operationen fest:

• Aktivieren Sie die Option "Nicht druckbare Zeichen" im Abschnitt "Bestimmte Zeichen löschen".
• Klicken Sie dann auf die Schaltfläche "Ok" oder "Anwenden", um alle Zeilenumbrüche aus dem ausgewählten Datenbereich zu entfernen. Siehe Screenshot:

2.6 Leerzeichen (führende, nachstehende, überflüssige oder alle Leerzeichen) aus Textzeichenfolgen entfernen

Das Kopieren und Einfügen von Text aus einer externen Quelle in ein Excel-Arbeitsblatt bringt oft lästige überflüssige Leerzeichen mit sich, und es kann mühsam sein, führende, nachgestellte oder andere zusätzliche Leerzeichen manuell zu entfernen. Glücklicherweise bietet Excel einige einfache Tricks zur Bewältigung dieser Aufgabe.

 Entfernen Sie überflüssige Leerzeichen (führende, nachgestellte, überschüssige) aus Textzeichenfolgen mit der TRIM-Funktion

In Excel können Sie zur Entfernung von führenden, nachgestellten und zusätzlichen Leerzeichen in Textzeichenfolgen die einfache TRIM-Funktion verwenden. Diese Funktion entfernt alle Leerzeichen, außer den einzelnen Leerzeichen zwischen Wörtern.

Bitte geben Sie die folgende Formel in eine leere Zelle ein:

=TRIM(A2)

Ziehen Sie dann den Ausfüllknauf nach unten, um die Formel in andere Zellen zu kopieren. Nun können Sie sehen, dass alle führenden, nachgestellten Leerzeichen sowie zusätzliche Leerzeichen zwischen den Wörtern auf einmal entfernt wurden, wie im Screenshot zu sehen:

 Alle Leerzeichen aus Textzeichenfolgen entfernen

Wenn Sie alle Leerzeichen aus Textzeichenfolgen entfernen möchten, können Ihnen die folgende SUBSTITUTE-Funktion und die Funktion Suchen & Ersetzen einen Gefallen tun.

Durch die Verwendung der Funktion SUBSTITUTE

Sie können die Funktion SUBSTITUTE verwenden, um alle Leerzeichen durch nichts zu ersetzen. Bitte wenden Sie die folgende Formel in einer leeren Zelle an:

=SUBSTITUTE(A2," ","") 

Ziehen Sie dann den Ausfüllknauf nach unten, um diese Formel in andere benötigte Zellen zu kopieren, und alle Leerzeichen werden wie im folgenden Screenshot gezeigt gelöscht:

Durch die Verwendung der Funktion „Suchen und Ersetzen“

Tatsächlich kann die Funktion "Suchen & Ersetzen" in Excel ebenfalls dabei helfen, alle Leerzeichen aus den ausgewählten Zellen zu entfernen. Bitte folgen Sie diesen Schritten:

1. Wählen Sie den Datenbereich aus, aus dem Sie alle Leerzeichen entfernen möchten.

2. Klicken Sie anschließend auf "Start" > "Suchen & Auswählen" > "Ersetzen" (oder drücken Sie die Tasten "Strg + H"), um zum Dialogfeld "Suchen und Ersetzen" zu gelangen. Führen Sie im geöffneten Dialogfeld "Suchen und Ersetzen" bitte die folgenden Schritte aus:

• Drücken Sie die „Leertaste“ im Feld „Suchen nach“;
• Lassen Sie das Feld "Ersetzen durch" leer.

3. Klicken Sie anschließend auf die Schaltfläche „Alle ersetzen“. Alle Leerzeichen in den ausgewählten Zellen werden sofort gelöscht. Siehe Screenshot:

 Entfernen Sie alle Arten von Leerzeichen aus Textzeichenfolgen mit einer leistungsstarken Funktion

"Kutools für Excel" bietet eine leistungsstarke Funktion – "Leerzeichen entfernen". Mit diesem Tool können Sie nicht nur Leerzeichen am Anfang, Leerzeichen am Ende und überflüssige Leerzeichen entfernen, sondern auch alle Leerzeichen in einem ausgewählten Bereich über ein einziges Dialogfeld löschen, was Ihre Arbeitsproduktivität erheblich steigern wird.

1. Wählen Sie den Datenbereich aus, aus dem Sie Leerzeichen entfernen möchten, und klicken Sie dann auf „Kutools“ > „Text“ > „Remove Spaces“. Siehe Screenshot:

2. Wählen Sie im Dialogfeld „Leerzeichen entfernen“ die Art der zu entfernenden Leerzeichen unter „Art der Leerzeichen“ aus:

• Um die führenden Leerzeichen zu entfernen, wählen Sie bitte die Option "Leerzeichen am Anfang".
• Um die Leerzeichen am Ende zu entfernen, wählen Sie bitte die Option "Leerzeichen am Ende".
• Um die führenden und die nachfolgenden Leerzeichen auf einmal zu entfernen, wählen Sie bitte die Option "Führende & nachfolgende Leerzeichen".
• Um alle zusätzlichen Leerzeichen zu entfernen, wählen Sie bitte die Option „Alle überflüssigen Leerzeichen“ aus.
• Um alle Leerzeichen zu entfernen, wählen Sie bitte die Option "Alle Leerzeichen".

3. Klicken Sie dann auf die Schaltfläche „Ok“ oder „Anwenden“, um das gewünschte Ergebnis zu erhalten.

 Zeichen / Text vor oder nach einem bestimmten Zeichen entfernen

In diesem Abschnitt werde ich einige Vorgänge zur Entfernung von Text oder Zeichen vor oder nach dem ersten, letzten oder n-ten Vorkommen eines bestimmten Zeichens vorstellen.

3.1 Text vor oder nach dem ersten bestimmten Zeichen entfernen

Wenn Sie den Text vor oder nach dem ersten bestimmten Zeichen, wie z. B. Leerzeichen oder Komma, aus einer Liste von Textzeichenfolgen entfernen möchten, wie im folgenden Screenshot gezeigt, werde ich Ihnen hier zwei Methoden vorstellen.

 Text vor dem ersten bestimmten Zeichen mit einer Formel entfernen

Um Text oder Zeichen vor dem ersten bestimmten Zeichen zu entfernen, können Sie eine Formel erstellen, die auf den Funktionen RECHTS, LÄNGE und FINDEN basiert. Die allgemeine Syntax lautet:

• "Zelle": Der Zellbezug oder Textstring, aus dem Sie Text entfernen möchten;
• "char": Das spezifische Trennzeichen, basierend auf dem Sie Text entfernen möchten.

Um beispielsweise alles vor dem ersten Komma aus den Zeichenfolgen der Liste zu entfernen, sollten Sie die folgende Formel in eine leere Zelle eingeben und diese dann nach unten in die gewünschten Zellen ziehen, siehe Screenshot:

=RIGHT(A2,LEN(A2)-FIND(",",A2))

Hinweis: In der obigen Formel: "A2" ist die Zelle, aus der Sie Text entfernen möchten; "," ist das spezifische Zeichen, basierend auf dem Sie Text entfernen möchten. Sie können es bei Bedarf in beliebige andere Zeichen ändern.

Text nach dem ersten bestimmten Zeichen mit einer Formel entfernen

Um alles nach dem ersten bestimmten Zeichen zu entfernen, können Sie die Funktionen LINKS und FINDEN verwenden, um das Ergebnis zu erhalten. Die allgemeine Syntax lautet:

• "Zelle": Der Zellbezug oder Textstring, aus dem Sie Text entfernen möchten;
• "char": Das spezifische Trennzeichen, basierend auf dem Sie Text entfernen möchten.

Geben Sie nun bitte die folgende Formel in eine leere Zelle ein, und ziehen Sie dann das Ausfüllkästchen nach unten in andere Zellen, auf die Sie diese Formel anwenden möchten. Alle Zeichen nach dem ersten Komma werden sofort entfernt, siehe Screenshot:

=LEFT(A2,FIND(",",A2)-1)

3.2 Text vor oder nach dem N-ten Vorkommen eines Zeichens entfernen

Manchmal enthalten Textzeichenfolgen mehrere Instanzen eines bestimmten Trennzeichens, und Sie möchten möglicherweise alle Zeichen vor oder nach einer bestimmten Instanz entfernen, wie zum Beispiel der zweiten, dritten oder vierten, je nach Bedarf. Um diesen Typ des Entfernens zu bewältigen, können Sie die folgenden Tricks verwenden:

 Text vor dem N-ten Vorkommen eines Zeichens mit einer Formel entfernen

Um den Text vor dem N-ten Vorkommen eines bestimmten Zeichens zu entfernen, kann Ihnen die folgende Formel helfen, die allgemeine Syntax lautet:

• "Zelle": Der Zellbezug oder Textstring, aus dem Sie Text entfernen möchten;
• "char": Das spezifische Trennzeichen, basierend auf dem Sie Text entfernen möchten;
• "N": Das Vorkommen des Zeichens, vor dem Text entfernt werden soll.

Um beispielsweise alles vor dem zweiten Komma aus den Textzeichenfolgen zu entfernen, sollten Sie die folgende Formel anwenden:

=RIGHT(A2,LEN(A2)-FIND("#",SUBSTITUTE(A2,",","#",2)))

Hinweis: In der obigen Formel: "A2" ist die Zelle, aus der Sie Text entfernen möchten; "," ist das spezifische Zeichen, basierend auf dem Sie Text entfernen möchten. Sie können es bei Bedarf in beliebige andere Zeichen ändern; "2" gibt den n-ten Komma an, vor dem Sie den Text entfernen möchten.

Ziehen Sie dann den Ausfüllkasten, um die Formel in andere Zellen zu kopieren, siehe Screenshot:

 Text nach dem N-ten Vorkommen eines Zeichens mit einer Formel entfernen

Um den Text nach dem N-ten Vorkommen eines bestimmten Trennzeichens zu entfernen, können die Funktionen LINKS, ERSETZEN und FINDEN Ihnen helfen. Die allgemeine Syntax lautet:

• "Zelle": Der Zellbezug oder Textstring, aus dem Sie Text entfernen möchten;
• "char": Das spezifische Trennzeichen, basierend auf dem Sie Text entfernen möchten;
• "N": Das Vorkommen des Zeichens, nach dem Text entfernt werden soll.

Sobald Sie die grundlegende Syntax verstanden haben, kopieren Sie die folgende Formel in eine leere Zelle oder geben Sie sie ein:

=LEFT(A2, FIND("#", SUBSTITUTE(A2, ",", "#", 2)) -1)

Hinweis: In der obigen Formel: "A2" ist die Zelle, aus der Sie Text entfernen möchten; "," ist das spezifische Zeichen, basierend auf dem Sie Text entfernen möchten. Sie können es bei Bedarf in beliebige andere Zeichen ändern; "2" gibt den n-ten Komma an, nach dem Sie Text entfernen möchten.

Ziehen Sie dann den Ausfüllknauf, um die Formel in andere Zellen zu kopieren, und alle Zeichen nach dem zweiten Komma werden auf einmal gelöscht, siehe Screenshot:

 Text vor oder nach dem N-ten Vorkommen eines Zeichens mit einer benutzerdefinierten Funktion entfernen

Wie Sie sehen können, können Sie die Fälle lösen, in denen Text vor oder nach dem N-ten Vorkommen eines Zeichens entfernt werden soll, indem Sie Excel-eigene Funktionen in verschiedenen Kombinationen verwenden. Das Problem ist, dass Sie sich diese komplizierten Formeln merken müssen. In diesem Fall werde ich eine benutzerdefinierte Funktion erstellen, um alle Szenarien abzudecken. Gehen Sie dazu wie folgt vor:

1. Halten Sie die Tasten "Alt + F11" gedrückt, um das Fenster "Microsoft Visual Basic for Applications" zu öffnen.

2. Klicken Sie auf "Einfügen" > "Modul" und fügen Sie den folgenden Code in das Modulfenster ein.

VBA-Code: Text vor oder nach dem N-ten Vorkommen eines Zeichens entfernen

Function RemoveTextOccurrence(Str As String, Delimiter As String, Occurrence As Integer, IsAfter As Boolean)
Dim xStr As String
Dim xStrLen, xF, xIntStart As Integer
xStr = Str
xStrLen = Len(xStr)
xIntStart = 1
For xF = 1 To Occurrence
xIntStart = InStr(xIntStart + 1, xStr, Delimiter, vbTextCompare)
If (xIntStart = 0) Or (xIntStart < 0) Then
    If IsAfter Then
    RemoveTextOccurrence = xStr
    Else
    RemoveTextOccurrence = ""
    End If
    Exit Function
End If
Next
If IsAfter Then
    RemoveTextOccurrence = Mid(Str, 1, xIntStart - 1)
Else
    RemoveTextOccurrence = Mid(Str, xIntStart + 1)
End If
End Function

3. Schließen Sie dann das Codefenster und kehren Sie zum Arbeitsblatt zurück. Verwenden Sie die folgenden Formeln:

Text vor dem zweiten Auftreten eines Kommas entfernen:

=RemoveTextOccurrence(A2, ", ", 2, FALSE)

Text nach dem zweiten Auftreten eines Kommas entfernen

=RemoveTextOccurrence(A2, ", ", 2, TRUE)

3.3 Text vor oder nach dem letzten Vorkommen eines Zeichens entfernen

Wenn Sie den gesamten Text vor oder nach dem letzten bestimmten Zeichen entfernen müssen und nur die Teilstrings davor oder danach übrig bleiben sollen, bietet dieser Abschnitt Formeln zur Lösung dieses Problems.

 Text vor dem letzten Vorkommen eines Zeichens mit einer Formel entfernen

Um alle Zeichen vor dem letzten Vorkommen eines Zeichens zu entfernen, lautet die allgemeine Syntax:

• "Zelle": Der Zellbezug oder Textstring, aus dem Sie Text entfernen möchten;
• "char": Das spezifische Trennzeichen, basierend auf dem Sie Text entfernen möchten;

Wenn Sie jetzt den Text vor dem letzten Auftreten eines Kommas entfernen müssen, kopieren Sie bitte die folgende Formel in eine leere Zelle oder geben Sie sie ein:

=RIGHT(A2,LEN(A2)-SEARCH("#",SUBSTITUTE(A2,",","#",LEN(A2)-LEN(SUBSTITUTE(A2,",","")))))

Hinweis: In der obigen Formel: "A2" ist die Zelle, aus der Sie Text entfernen möchten; "," ist das spezifische Zeichen, basierend auf dem Sie Text entfernen möchten. Sie können es bei Bedarf in beliebige andere Zeichen ändern.

Ziehen Sie dann den Ausfüllknauf, um die Formel in andere Zellen zu kopieren, und alle Zeichen vor dem letzten Komma werden wie im folgenden Screenshot gezeigt gelöscht:

 Text nach dem letzten Vorkommen eines Zeichens mit Formeln entfernen

Wenn die Zellwerte durch eine variable Anzahl von Trennzeichen getrennt sind und Sie alles nach dem letzten Vorkommen dieses Trennzeichens löschen möchten, lautet die allgemeine Syntax:

• "Zelle": Der Zellbezug oder Textstring, aus dem Sie Text entfernen möchten;
• "char": Das spezifische Trennzeichen, basierend auf dem Sie Text entfernen möchten;

Bitte kopieren Sie die folgende Formel in eine leere Zelle, oder geben Sie sie ein, und ziehen Sie dann den Ausfüllkursor nach unten, um weitere benötigte Ergebnisse zu erhalten, siehe Screenshot:

=LEFT(A2,FIND("#",SUBSTITUTE(A2,",>","#",LEN(A2)-LEN(SUBSTITUTE(A2,",",""))))-1)

"Hinweis": In der obigen Formel: "A2" ist die Zelle, aus der Sie Text entfernen möchten; "," ist das spezifische Zeichen, auf dessen Basis Sie Text entfernen möchten. Sie können es bei Bedarf in beliebige andere Zeichen ändern.

3.4 Text zwischen Klammern entfernen

Wenn Sie eine Liste von Textzeichenfolgen haben, bei denen ein Teil der Zeichen in Klammern eingeschlossen ist, möchten Sie vielleicht alle Zeichen innerhalb der Klammern einschließlich der Klammern selbst entfernen, wie im folgenden Screenshot dargestellt. Dieser Abschnitt behandelt einige Tricks zur Lösung dieser Aufgabe in Excel.

 Text zwischen Klammern mit der Funktion 'Suchen und Ersetzen' entfernen

In Excel kann die integrierte Funktion „Suchen und Ersetzen“ helfen, alle Texte innerhalb der Klammern zu finden und sie dann durch nichts zu ersetzen. Bitte gehen Sie wie folgt vor:

1. Wählen Sie die Datenliste aus, aus der Sie die Texte zwischen den Klammern entfernen möchten.

2. Klicken Sie anschließend auf "Start" > "Suchen & Auswählen" > "Ersetzen" (oder drücken Sie die Tasten "Strg + H"), um das Dialogfeld "Suchen und Ersetzen" zu öffnen. Führen Sie im Dialogfeld "Suchen und Ersetzen" die folgenden Schritte aus:

• Geben Sie im Feld "Suchen nach" "(*)" in das Textfeld ein;
• Lassen Sie das Feld "Ersetzen durch" leer.

3. Klicken Sie dann auf die Schaltfläche „Alle ersetzen“. Alle Zeichen innerhalb der Klammern (einschließlich der Klammern) in den ausgewählten Zellen werden auf einmal gelöscht. Siehe Screenshot:

Tipp: Die Funktion "Suchen und Ersetzen" funktioniert auch für zwei oder mehrere Klammernpaare innerhalb der Textzeichenfolgen.

 Text zwischen Klammern mit Formel entfernen

Neben der Funktion 'Suchen und Ersetzen' können Sie auch eine Formel verwenden, um diese Aufgabe in Excel zu lösen. Die allgemeine Syntax lautet:

• "Text": Die Textzeichenfolge oder Zellreferenz, aus der Sie Zeichen entfernen möchten.

Kopieren Sie nun die folgende Formel in eine leere Zelle, oder geben Sie sie ein, um das Ergebnis zu erhalten:

=SUBSTITUTE(A2,MID(LEFT(A2,FIND(")",A2)),FIND("(",A2),LEN(A2)),"")

Ziehen Sie dann den Ausfüllkursor nach unten in die Zellen, in denen Sie diese Formel anwenden möchten, und alle Texte innerhalb der Klammern einschließlich der Klammern werden auf einmal entfernt, siehe Screenshot:

Tipp: Wenn es keine Klammern im Zellenwert gibt, wird nach dem Anwenden der obigen Formel ein Fehler angezeigt. Um den Fehler zu ignorieren, verwenden Sie bitte die folgende Formel:

=IFERROR(SUBSTITUTE(A2,MID(LEFT(A2,FIND(")",A2)),FIND("(",A2),LEN(A2)),""),A2)

 Text zwischen Klammern mit benutzerdefinierter Funktion entfernen

Die obige Formel funktioniert gut, um Text aus einer Klammer zu entfernen. Wenn Sie jedoch Text aus mehreren Klammern innerhalb der Textzeichenfolgen entfernen müssen, wird die Formel nicht korrekt funktionieren. Hier werde ich eine einfache benutzerdefinierte Funktion erstellen, um diese Aufgabe zu lösen.

1. Halten Sie die Tasten "Alt + F11" gedrückt, um das Fenster "Microsoft Visual Basic for Applications" zu öffnen.

2. Klicken Sie auf "Einfügen" > "Modul" und fügen Sie den folgenden Code in das Modulfenster ein.

VBA-Code: Text zwischen Klammern entfernen

Function remtxt(ByVal str As String) As String
'Updateby Extendoffice
  While InStr(str, "(") > 0 And InStr(str, ")") > InStr(str, "(")
    str = Left(str, InStr(str, "(") - 1) & Mid(str, InStr(str, ")") + 1)
  Wend
  remtxt = Trim(str)
End Function

3. Wechseln Sie zurück zum Arbeitsblatt und geben Sie diese Formel in eine leere Zelle ein: "=remtxt(A2)", dann ziehen Sie den Ausfüllknauf nach unten, um die Formel anzuwenden. Der gesamte Text innerhalb der Klammern, einschließlich der Klammern selbst, wird entfernt, wie im Screenshot gezeigt.

 Wörter aus Textzeichenfolgen entfernen

In bestimmten Fällen möchten Sie möglicherweise einige Wörter aus einer Liste von Zellen entfernen, wie zum Beispiel das erste oder letzte Wort oder doppelte Wörter aus einer Zelle. Um diese Art von Entfernung durchzuführen, stellt dieser Abschnitt einige Methoden vor.

4.1 Entfernen des ersten oder letzten Wortes aus einer Textzeichenfolge

Um das erste oder letzte Wort aus einer Liste von Textzeichenfolgen zu entfernen, können die folgenden Formeln hilfreich sein.

 Erste Wort aus Textzeichenfolge mit Formel entfernen

Um die ersten Wörter aus einer Liste von Textzeichenfolgen zu entfernen, können Sie eine einfache Formel erstellen, die auf den Funktionen RECHTS, LÄNGE und FINDEN basiert. Die allgemeine Syntax lautet:

• "Text": Die Textzeichenfolge oder Zellreferenz, aus der Sie das erste Wort entfernen möchten.

Geben Sie nun die folgende Formel in eine leere Zelle ein oder kopieren Sie sie:

=RIGHT(A2,LEN(A2)-FIND(" ",A2))

Ziehen Sie dann den Ausfüllkasten nach unten, um die Formel auf andere Zellen anzuwenden, siehe Screenshot:

Tipp: Wenn Sie die ersten N Wörter aus den Zellen entfernen müssen, verwenden Sie bitte die folgende Formel:

• "Text": Die Textzeichenfolge oder Zellreferenz, aus der Sie die ersten n Wörter entfernen möchten;
• "N": Gibt an, wie viele Wörter Sie vom Anfang der Textzeichenfolge entfernen möchten.

Um beispielsweise die ersten beiden Wörter aus Zellen zu entfernen, kopieren Sie bitte die folgende Formel in eine leere Zelle oder geben Sie sie ein, um das gewünschte Ergebnis zu erhalten, siehe Screenshot:

=MID(TRIM(A2),1+FIND("~",SUBSTITUTE(TRIM(A2)," ","~",2)),255)

  Letztes Wort aus Textzeichenfolge mit Formel entfernen

Um das letzte Wort aus Textzeichenfolgen zu entfernen, können Sie auch eine Formel verwenden, um diese Aufgabe zu lösen. Die allgemeine Syntax lautet:

• "Text": Die Textzeichenfolge oder Zellreferenz, aus der Sie das letzte Wort entfernen möchten;

Bitte verwenden Sie die folgende Formel in eine leere Zelle, und ziehen Sie dann das Ausfüllkästchen nach unten, um die Formel auf andere Zellen anzuwenden, siehe Screenshot:

=LEFT(TRIM(A2),FIND("~",SUBSTITUTE(A2," ","~",LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))))-1)

Tipp: Um die letzten N Wörter aus einer Liste von Zellen zu entfernen, lautet die allgemeine Syntax:

• "Text": Die Textzeichenfolge oder Zellreferenz, aus der Sie die letzten n Wörter entfernen möchten;
• "N": Gibt die Anzahl der Wörter an, die Sie vom Ende der Textzeichenfolge entfernen möchten.

Angenommen, Sie möchten die letzten 3 Wörter aus einer Liste von Zellen löschen, verwenden Sie bitte die folgende Formel, um das Ergebnis zurückzugeben, siehe Screenshot:

=LEFT(A2,FIND("~",SUBSTITUTE(A2," ","~",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-(3-1))))

4.2 Doppelte Zeichen oder Wörter innerhalb einer Zelle entfernen

Wenn es darum geht, doppelte Werte oder Zeilen zu entfernen, bietet Excel einige verschiedene Optionen. Wenn es jedoch darum geht, bestimmte doppelte Zeichen oder Wörter innerhalb einer bestimmten Zelle zu entfernen, gibt es möglicherweise keine gute integrierte Funktion zur Lösung dieses Problems. In diesem Fall hilft dieser Abschnitt dabei, einige benutzerdefinierte Funktionen zu erstellen, um dieses Rätsel zu lösen.

 Entfernen Sie doppelte Zeichen innerhalb einer Zelle mithilfe einer benutzerdefinierten Funktion

Wenn Sie mehrere Vorkommen desselben Zeichens in einer Zelle haben und die doppelten Zeichen innerhalb der Zelle entfernen möchten, sodass nur das erste Vorkommen wie im folgenden Screenshot gezeigt erhalten bleibt, können Sie die folgende benutzerdefinierte Funktion verwenden.

1. Halten Sie die Tasten „Alt + F11“ gedrückt, um das Fenster „Microsoft Visual Basic for Applications“ zu öffnen.

2. Klicken Sie auf "Einfügen" > "Modul" und fügen Sie den folgenden Code in das Modulfenster ein.

VBA-Code: Doppelte Zeichen innerhalb einer Zelle entfernen

Function RemoveDupeschars(pWorkRng As Range) As String
'Updateby Extendoffice
Dim xValue As String
Dim xChar As String
Dim xOutValue As String
Set xDic = CreateObject("Scripting.Dictionary")
xValue = pWorkRng.Value
For i = 1 To VBA.Len(xValue)
    xChar = VBA.Mid(xValue, i, 1)
    If xDic.Exists(xChar) Then
    Else
        xDic(xChar) = ""
        xOutValue = xOutValue & xChar
    End If
Next
RemoveDupeschars = xOutValue
End Function

3. Schließen Sie dann das Codefenster, gehen Sie zurück zum Arbeitsblatt, und geben Sie diese Formel =RemoveDupeschars(A2) in eine leere Zelle neben Ihren Daten ein. Ziehen Sie anschließend den Ausfüllkursor über die Zellen, auf die Sie diese Formel anwenden möchten, siehe Screenshot:

Hinweis: „A2“ ist die Datenzelle, aus der Sie doppelte Zeichen entfernen möchten.

Tipp: Die Funktion unterscheidet zwischen Groß- und Kleinschreibung, daher werden Klein- und Großbuchstaben als unterschiedliche Zeichen behandelt.

 Entfernen Sie doppelte Wörter in einer Zelle mithilfe einer benutzerdefinierten Funktion

Angenommen, Sie haben dieselben Wörter oder Textzeichenfolgen in einer Zelle und möchten alle identischen Wörter aus der Zelle entfernen, wie im folgenden Screenshot gezeigt. Sie können die folgende benutzerdefinierte Funktion verwenden, um diese Aufgabe in Excel zu lösen.

1. Halten Sie die Tasten "Alt + F11" gedrückt, um das Fenster "Microsoft Visual Basic for Applications" zu öffnen.

2. Klicken Sie auf "Einfügen" > "Modul" und fügen Sie den folgenden Code in das Modulfenster ein.

VBA-Code: Doppelte Wörter innerhalb einer Zelle entfernen

Function RemoveDupeswords(txt As String, Optional delim As String = " ") As String
'Updateby Extendoffice
    Dim x
    With CreateObject("Scripting.Dictionary")
        .CompareMode = vbTextCompare
        For Each x In Split(txt, delim)
            If Trim(x) <> "" And Not .exists(Trim(x)) Then .Add Trim(x), Nothing
        Next
        If .Count > 0 Then RemoveDupeswords = Join(.keys, delim)
    End With
End Function

3. Schließen Sie dann das Codefenster, kehren Sie zum Arbeitsblatt zurück, und geben Sie diese Formel =RemoveDupeswords(A2,", ") in eine leere Zelle neben Ihren Daten ein. Ziehen Sie anschließend den Ausfüllkursor über die Zellen, auf die Sie diese Formel anwenden möchten, siehe Screenshot:

Hinweis: „A2“ ist die Zelle, aus der Sie die doppelten Wörter löschen möchten, und das Komma und das Leerzeichen („, “) sind die Trennzeichen, um die Textzeichenfolgen zu trennen. Sie können sie bei Bedarf in beliebige andere Trennzeichen ändern.

Tipp: Diese Funktion unterscheidet nicht zwischen Groß- und Kleinschreibung, Klein- und Großbuchstaben werden als dieselben Zeichen behandelt.

4.3 Textzeichenfolge auf N Wörter kürzen

Wenn Sie in einer Zelle eine lange Textzeichenfolge haben, möchten Sie diese manchmal auf eine bestimmte Anzahl von Wörtern kürzen, was bedeutet, dass nur die ersten n Wörter beibehalten und der Rest abgeschnitten wird. Dieser Abschnitt behandelt einige Tricks, die Ihnen helfen, diese Aufgabe in Excel zu erledigen.

 Textzeichenfolge auf N Wörter mit Formel kürzen

Um eine Textzeichenfolge auf N Wörter zu kürzen, können Sie eine Formel erstellen, die auf den Funktionen LINKS, SUCHEN und ERSETZEN basiert. Die allgemeine Syntax lautet:

• "Text": Die Textzeichenfolge oder Zellreferenz, die Sie kürzen möchten;
• "N": Die Anzahl der Wörter, die Sie vom linken Ende der angegebenen Textzeichenfolge behalten möchten.

Um diese Aufgabe zu erledigen, kopieren Sie bitte die folgende Formel in eine leere Zelle oder geben Sie sie ein:

=LEFT(A2,FIND("~",SUBSTITUTE(A2," ","~",B2))-1)

Ziehen Sie dann den Ausfüllkasten nach unten, um diese Formel auf andere Zellen anzuwenden, siehe Screenshot:

 Textzeichenfolge auf N Wörter mit benutzerdefinierter Funktion kürzen

Neben der oben genannten Formel können Sie auch eine benutzerdefinierte Funktion erstellen, um diese Aufgabe zu lösen. Gehen Sie dazu wie folgt vor:

1. Halten Sie die Tasten "Alt + F11" gedrückt, um das Fenster "Microsoft Visual Basic for Applications" zu öffnen.

2. Klicken Sie auf "Einfügen" > "Modul" und fügen Sie den folgenden Code in das Modulfenster ein.

VBA-Code: Textzeichenfolge auf N Wörter kürzen

Function GetNWords(StrWords As String, Num_of_Words As Integer) As String
'Updateby Extendoffice
Dim xArr
Dim xRes As String
Dim xF As Integer
xStr = StrWords
If (Num_of_Words < 1) Then
    GetNWords = ""
    Exit Function
End If
xArr = Split(xStr, " ")
xRes = ""
On Error Resume Next
For xF = 0 To UBound(xArr)
    If Trim(xArr(xF)) <> "" Then
    Num_of_Words = Num_of_Words - 1
        If xRes = "" Then
            xRes = Trim(xArr(xF))
        Else
            xRes = xRes & " " & Trim(xArr(xF))
        End If
    End If
    If Num_of_Words = 0 Then Exit For
Next
If Num_of_Words = 0 Then
    GetNWords = xRes & "..."
Else
    GetNWords = xRes & "..."
End If
End Function

3. Schließen Sie dann das Codefenster und beenden Sie es, gehen Sie zurück zum Arbeitsblatt, und geben Sie diese Formel ein: =GetNWords(A2,B2) in eine leere Zelle, und ziehen Sie den Ausfüllknauf nach unten, um diese Formel auf andere Zellen anzuwenden. Es werden nur die ersten bestimmten Anzahl von Wörtern wie im folgenden Screenshot gezeigt behalten: