Wie entfernt man alle Satzzeichen (Komma, Anführungszeichen, Apostroph) aus Zellen?

Beim Arbeiten mit Daten in Excel können Ihre Zellen verschiedene Satzzeichen wie Kommas, Anführungszeichen, Punkte, Fragezeichen, Doppelpunkte und weitere enthalten. Diese Satzzeichen können die Datenanalyse, das Sortieren, die Textextraktion oder die Formatierung stören. Beispielsweise möchten Sie beim Vorbereiten von Texten für weitere Berechnungen, dem Exportieren sauberer Datensätze oder beim Standardisieren von Nutzereingaben alle Satzzeichen entfernen, sodass nur Buchstaben, Zahlen und eventuell Leerzeichen übrig bleiben.

In diesem Artikel finden Sie mehrere praxisnahe Lösungsansätze, wie Sie effizient alle Arten von Satzzeichen aus Zellen in Excel entfernen. Unabhängig davon, ob Sie eine benutzerdefinierte Funktion (User Defined Function), ein spezielles Excel-Add-in oder native Formeln bevorzugen: Hier finden Sie eine Methode, die zu Ihrem Anwendungsfall passt:

• Alle Satzzeichen aus Zellen mit Excel-Formel entfernen (ohne VBA oder Add-ins)
• Alle Satzzeichen aus Zellen mit Kutools für Excel entfernen
• Alle Satzzeichen aus Zellen mit einer User Defined Function entfernen

Alle Satzzeichen aus Zellen mit Excel-Formel entfernen (ohne VBA oder Add-ins)

Diese Lösung eignet sich ideal für Nutzer, die keine Makros verwenden können oder keinen Zugriff auf Excel-Add-ins haben. Sie können verschachtelte Excel-Formeln einsetzen, um sämtlichen Text von Satzzeichen zu bereinigen. Die Methode basiert ausschließlich auf integrierten Excel-Funktionen, ist somit sehr flexibel und entspricht den meisten firmeninternen Sicherheitsrichtlinien. Hinweis: Dieser Ansatz auf Formel-Basis ist sehr robust, kann jedoch bei einer großen Vielfalt an Satzzeichen und sehr langen Zeichenfolgen komplex werden. Außerdem benötigen die Formeln Anpassungen, wenn Sie Ihre eigene Liste der zu entfernenden Satzzeichen festlegen möchten.

Beispiel für ein Szenario: Sie erhalten ein Datenblatt mit Produktbeschreibungen oder -namen, die Kommas, Punkte, Fragezeichen und verschiedene Anführungszeichen enthalten – und Sie müssen all diese für eine saubere Berichterstellung oder einen Import in ein anderes System entfernen.

1. Geben Sie in Ihrer Zielzelle (zum Beispiel B2) die folgende Formel ein, um einige der gängigsten Satzzeichen zu entfernen (passen Sie die Zeichenliste nach Bedarf an):

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, ",", ""), ".", ""), "!", ""), "?", ""), ";", ""), ":", ""), """", "")

2. Drücken Sie die Eingabetaste, um die Formel anzuwenden. Um weitere Zeilen zu bearbeiten, ziehen Sie das Ausfüllkästchen nach unten, um die Formel auch auf zusätzliche Zellen anzuwenden.

Wenn Sie weitere Satzzeichen entfernen möchten, verschachteln Sie zusätzliche SUBSTITUTE-Funktionen, wobei jedes Mal das unerwünschte Zeichen ersetzt wird. Für fortgeschrittene Szenarien oder wenn die Liste an Satzzeichen lang ist, können Sie auch diese Array-Formel mit TEXTJOIN nutzen (Excel2016 und neuer):

=TEXTJOIN("", TRUE, IF(ISNUMBER(FIND(MID(A2, ROW(INDIRECT("1:"&LEN(A2))),1), "~!@#$%^&*()_-+={}[]:;""''<>,./?`\|")), "", MID(A2, ROW(INDIRECT("1:"&LEN(A2))),1)))

Diese Formel funktioniert als Array-Formel und entfernt alle Zeichen, die in der Liste ~!@#$%^&*()_-+={}[]:;""''<>.,./?`\| enthalten sind. Um diese Formel zu verwenden:

• Geben Sie sie in B2 (oder eine andere Zielzelle) ein.
• Wenn Sie Excel365,2019 oder2016 verwenden, drücken Sie einfach die Eingabetaste. In älteren Versionen müssen Sie möglicherweise mit Strg+Shift+Eingabetaste bestätigen, um die Array-Formel zu aktivieren.
• Ziehen Sie die Formel nach unten, um zusätzliche Zeilen zu bearbeiten.

Hinweise:

• Passen Sie bei Bedarf die Zeichenauswahl in der Satzzeichenliste innerhalb der FIND()-Funktion für Ihre speziellen Anforderungen an.
• Seien Sie bei der Verwendung von Anführungszeichen in der Liste vorsichtig: Doppeln Sie sie innerhalb der Zeichenfolge ("" für ein einzelnes wörtliches ").
• Führende und nachfolgende Leerzeichen bleiben erhalten. Verwenden Sie TRIM(), wenn Sie überschüssige Leerzeichen ebenfalls entfernen möchten.
• Array-Formeln können bei sehr großen Datenbereichen die Leistung beeinträchtigen.

Fehlerbehebung: Gibt Ihre Formel einen Fehler aus, stellen Sie sicher, dass der Text in A2 einen Wert enthält und dass die Array-Formeln korrekt eingegeben wurden (achten Sie auf geschweifte Klammern { } in älteren Excel-Versionen nach der Eingabe mit Strg+Shift+Eingabetaste). Leere Ergebnisse bedeuten, dass Ihre Zelle möglicherweise nur Satzzeichen enthält oder selbst leer ist.

Alle Satzzeichen aus Zellen mit Kutools für Excel entfernen

Wenn Sie Texte häufig bereinigen und eine schnelle, grafische Lösung benötigen, kann Ihnen die Bestimmte Zeichen löschen-Funktion von Kutools für Excel helfen, Satzzeichen aus jedem ausgewählten Bereich effizient zu entfernen. Anders als Formeln oder Programmcode bietet dieses Add-In einen unkomplizierten Dialog, in dem Sie direkt festlegen, welche Zeichen entfernt werden sollen. Damit profitieren sowohl Einsteiger als auch erfahrene Nutzer von hoher Flexibilität und einfacher Bedienung.

1. Wählen Sie den Bereich aus, aus dem Sie sämtliche Satzzeichen entfernen möchten, und klicken Sie auf Kutools > Text > Bestimmte Zeichen löschen. Siehe Screenshot:

2. Im daraufhin geöffneten Bestimmte Zeichen löschen-Dialogfeld aktivieren Sie nur die Benutzerdefiniert Option, und geben Sie die Satzzeichen ~!@#$%^&*()_-+={}[]:;""''<>,./?`\| in das Feld unten ein. Klicken Sie dann auf die Ok Schaltfläche, um diese Satzzeichen sofort aus allen ausgewählten Zellen zu entfernen. Siehe Screenshot unten:

Überprüfen Sie die Ergebnisse sofort in Ihrem Arbeitsblatt. Dieses Tool ist besonders nützlich zum Bereinigen importierter Daten, beim Vorbereiten von Datensätzen zur Analyse oder wenn Sie eine konsistente Textformatierung sicherstellen möchten. Die Funktion arbeitet direkt auf Ihren ausgewählten Zellen – prüfen Sie daher die betroffenen Daten, bevor Sie speichern.

Demo: Alle Satzzeichen aus Zellen mit Kutools für Excel entfernen

Alle Satzzeichen aus Zellen mit einer User Defined Function entfernen

Diese Methode stellt eine User Defined Function (UDF) vor, mit der Sie beliebige Satzzeichen aus Zellen in Excel entfernen können. UDFs sind bestens geeignet, wenn Sie diese Aufgabe häufiger durchführen müssen und bereit sind, Makros in Ihrer Arbeitsmappe zu aktivieren. Die Methode ermöglicht eine nahezu vollständige und anpassbare Entfernung von Satzzeichen und ist besonders nützlich, wenn Sie die Funktion schnell teilen oder wiederverwenden möchten.

Beachten Sie aber, dass diese Lösung makro-aktivierte Arbeitsmappen benötigt und in Umgebungen mit strikten Makro-Richtlinien eingeschränkt sein kann. Speichern Sie Ihre Arbeit immer, bevor Sie neuen Code ausführen oder hinzufügen. Wenn Sie die Arbeitsmappe teilen, muss auch die Makro-Kompatibilität beachtet werden.

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

2. Klicken Sie auf Einfügen > Modul und kopieren Sie anschließend den folgenden Code in das neue Modul-Fenster.

VBA: Satzzeichen aus Zellen in Excel entfernen

Function RemovePunctuation(Txt As String) As String
With CreateObject("VBScript.RegExp")
.Pattern = "[^A-Z0-9 ]"
.IgnoreCase = True
.Global = True
RemovePunctuation = .Replace(Txt, "")
End With
End Function

3. Nachdem Sie den Code eingefügt haben, drücken Sie Strg+S, um die User Defined Function zu speichern, und schließen Sie das Visual Basic for Applications-Fenster. Speichern Sie die Arbeitsmappe unbedingt als Makro-aktivierte Arbeitsmappe (*.xlsm), damit die Funktion künftig funktioniert.

4. Zurück in Excel wählen Sie eine leere Zelle, in der das Ergebnis erscheinen soll, und geben die Formel ein =RemovePunctuation(A2) (wobei A2 die zu bereinigende Zelle ist) und ziehen Sie das Ausfüllkästchen nach unten, um die Formel auch auf weitere Zellen anzuwenden.

Nach Anwendung der Formel werden alle Arten von Satzzeichen aus den gewählten Zellen entfernt, sodass nur noch reiner Text und Zahlen übrig bleiben. Wie unten gezeigt:

Tipp: Bei sehr großen Datenmengen kann die UDF längere Zeit zur Neuberechnung benötigen. Falls notwendig, wandeln Sie die Ergebnisse in Werte um, indem Sie die Ausgabe kopieren und als Werte einfügen.

Ähnliche Artikel:

Wie entfernt man Buchstaben aus Zeichenfolgen/Zahlen/Zellen in Excel?

Wie entfernt man Zahlen aus Textzeichenfolgen in Excel?