Live-Forum - Die aktuellen Beiträge
Datum
Titel
16.10.2025 00:15:51
Passwort im Herbersforum ändern
15.10.2025 16:22:17
Datum ist nicht Datum :(
15.10.2025 15:02:20
Blockpfeile mit Makro gehen nicht mehr nach freigabe
15.10.2025 13:51:13
VBA - Fortschrittsbalken unabhängig von Zellen
Keine passende Antwort im Forumarchiv gefunden?
Stelle deine Frage im Live-Forum
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1540to1544
1540to1544
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Alle Sonderzeichen in Bereich löschen

Forumthread: Alle Sonderzeichen in Bereich löschen

Alle Sonderzeichen in Bereich löschen
23.02.2017 13:49:52
Basti
Servus Forum,
ich bentötige schon wieder Hilfe.
Meine Exportdatai enhält Sonderzeichen und ich verwende den Inhalt für meine Windowsordnernamen. Daher habe ich versucht einen VBA-Code zu basteln, der alle Sonderzeichen nach einer Vorbotsliste löscht.
Am Anfang & Ende vom String sollen Sonderzeichen einfach gelöschen werden. Die Sonderzeichen Dazwischen sollen durch "_" ersetzt werden.
Bsp. >*A>*B>*C>* wird zu A_B_C
Anbei meine Anfänge. Allerdings löscht dieser Ablauf gerade den gesamten markierten Bereich.
Public Sub SondZeichloeschen2()
Dim C As Range
Dim n As Integer
Dim Temp, Verboten As String
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
For Each C In Selection
With C
Verboten = "\/:*?""|]*_ "
Temp = ""
For n = 1 To Len(Verboten)
Temp = Replace(Temp, Mid(Verboten, n, 1), "")
Next
.Value = Temp
End With
Next C
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
Danke für die Hilfe
Gruß
Basti
Anzeige

8
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Alle Sonderzeichen in Bereich löschen
23.02.2017 16:15:39
Piet
Hallo,
nach meiner Ansicht geht das am besten über 2 Schleifen, wobei du die Selection selbst festlegen kannst.
Ich empfehle zuerst die Sonderzeichen am Anfang und Ende einfach abschneiden, und dann Replace einsetzen.
Probier es einfach mal aus. Der untere Code ist aber ungetestet!! Bitte zuerst in einer Testdatei probieren.
mfg Piet
Public Sub SondZeichloeschen2()
Dim C As Range
Dim n As Integer
Dim Temp, Verboten As String
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Verboten = "\/:*?""|]*_ "
'1. Schleife rechts und links einfach abschneiden
For Each C In Selection
For n = 1 To Len(Verboten)
If Left(C, 1) = Mid(Verboten, n, 1) Then _
C.Value = Right(C, Len(C) - 1)
If Right(C, 1) = Mid(Verboten, n, 1) Then _
C.Value = Left(C, Len(C) - 1)
Next n
Next C
'2. Schleife im String auswechseln
For Each C In Selection
For n = 1 To Len(Verboten)
If InStr(C, 1) = Mid(Verboten, n, 1) Then _
C.Value = Replace(C, Mid(Verboten, n, 1), "_")
Next n
Next C
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

Anzeige
AW: Alle Sonderzeichen in Bereich löschen
23.02.2017 21:08:07
Anton
Hallo Basti,
hab auch noch einen Vorschlag mit RegExp: 
Sub ZeichenErsetzen()
Dim c As Range
Dim wksBlatt As Worksheet
Dim strPattern As String
Dim strGesamt As String
Dim y As Long
Dim regex As Object
y = 1
Set regex = CreateObject("VBScript.RegExp")
Set wksBlatt = ThisWorkbook.Worksheets("Tabelle2") 'Tabellenblatt anpassen
With regex
.Pattern = "\\|\/|\:|\?|\|\||\]|\*|\_"
.Global = True
End With
With wksBlatt
For Each c In Selection
strPattern = ""
strGesamt = ""
For i = 1 To Len(c)
If regex.Test(Mid(c, i, 1)) = True Then
strPattern = strPattern & regex.Replace(Mid(c, i, 1), "_")
Else
strGesamt = strGesamt & strPattern & Mid(c, i, 1)
strPattern = ""
End If
Next i
strGesamt = strGesamt & strPattern
.Cells(y, 3).Value = strGesamt 'Spalte für Ausgabe anpassen
y = y + 1
Next c
End With
End Sub
VG Anton
Anzeige
AW: Alle Sonderzeichen in Bereich löschen
24.02.2017 08:02:06
Basti
Servus Piet, Servus Anton,
zunächst einmal vielen Dank für Eure Hilfe.
Leider haben beide Codes den "bug", dass sie "löschen/ersetzen" von Sonderzeichen nur ausführen, wenn alle und auch in dieser Reihenfolge, wie in der Vorbotsliste, im String enthalten sind. Die Vorbotsliste ist also keine Auswahl sondern eine Zeichenfolge nach der explizit gesucht wird.
Habt Ihr evtl. noch eine Lösung zur Hand?
Gruß
Bastia
Anzeige
AW: Alle Sonderzeichen in Bereich löschen
24.02.2017 08:57:38
Anton
Guten Morgen Basti,
theoretisch müsstest Du dann nur das Pattern durch "\\\/:\*\?""\|\]\*_\s" ersetzen. Dann müssen aber alle Zeichen direkt hintereinander vorkommen. Ich kann es gerade nicht testen, da ich auf dem Mac Book kein Reg Exp Bibliothek zur Verfügung habe.
VG Anton
AW: Alle Sonderzeichen in Bereich löschen
24.02.2017 09:48:31
Basti
Hi Anton,
Irgendwie klappt es bei mir nicht.
Habe die Anpassung wie unten umgestzt, aber selbst wenn ich diese Zeichen in eine Zelle kopiere, dann funktionert die Prozedur nicht.
With regex
.Pattern = "\\\/:\*\?""\|\]\*_\s"
Noch Vorschläge?
Gruß
Basti
Anzeige
AW: Alle Sonderzeichen in Bereich löschen
24.02.2017 10:07:34
Daniel
Hi
mal ausgehend von deiner Datei, probiers mal so:
Public Sub SondZeichloeschen2()
Dim C As Range
Dim n As Integer
Dim Temp As String, Verboten As String
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Verboten = "\/:*?""|]_ "
For Each C In Selection.SpecialCells(xlCellTypeConstants, 1)
Temp = C.Value
For n = 1 To Len(Verboten)
Temp = Replace(Temp, Mid(Verboten, n, 1), " ")
Next
Temp = WorksheetFunction.Trim(Temp)
Temp = Replace(Temp, " ", "_")
C.Value = Temp
Next C
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
der Grund, warum bei dir alles gelöscht wird lieg darin, dass du am Anfang Temp = "" geschrieben hast, dh du übernimmst an keiner Stelle den Wert aus der Zelle.
es muss natürlich lauten: Temp = C.Value

Gruß Daniel
Anzeige
AW: Alle Sonderzeichen in Bereich löschen
24.02.2017 12:10:08
Basti
Servus Daniel,
danke für deine Mitarbeit. Mit deinem Hinweis und nachdem ich 

.SpecialCells(xlCellTypeConstants, 1)
gelöscht habe, funktioniert es.
Wenn ich .specialCells im Ablauf lasse, dann kommt die Fehlermeldung '1004 - No celles were found'!
Public Sub SondZeichloeschen2()
Dim C As Range
Dim n As Integer
Dim Temp As String, Verboten As String
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Verboten = "\/:*?""|]._- "
For Each C In Selection
Temp = C.Value
For n = 1 To Len(Verboten)
Temp = Replace(Temp, Mid(Verboten, n, 1), " ")
Next
Temp = WorksheetFunction.Trim(Temp)
Temp = Replace(Temp, " ", "_")
C.Value = Temp
Next C
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
Gruß
Basti
Anzeige
AW: Alle Sonderzeichen in Bereich löschen
24.02.2017 12:41:38
Daniel
Hi
sorry, der zweite Parameter muss auch eine 2 für Texte sein
mit 1 läuft das .specialCells über Zahlen (ich hoffe, du hast dir das .specialcells auch mal in der Hilfe angeschaut und nicht nur abgeschrieben)
Gruß Daniel
;
Anzeige
Anzeige

Infobox / Tutorial

Alle Sonderzeichen in Excel-Bereich löschen

Schritt-für-Schritt-Anleitung

Um alle Sonderzeichen in einem Excel-Bereich zu entfernen oder zu ersetzen, kannst Du VBA (Visual Basic for Applications) verwenden. Hier ist eine einfache Schritt-für-Schritt-Anleitung:

  1. Öffne Excel und drücke ALT + F11, um den VBA-Editor zu öffnen.

  2. Füge ein neues Modul hinzu: Rechtsklick auf "VBAProject (DeinWorkbookName)" > Einfügen > Modul.

  3. Kopiere den folgenden VBA-Code in das Modul:

    Public Sub SondZeichloeschen2()
   Dim C As Range
   Dim n As Integer
   Dim Temp As String, Verboten As String
   With Application
       .ScreenUpdating = False
       .EnableEvents = False
   End With
   Verboten = "\/:*?""|]_ "
   For Each C In Selection
       Temp = C.Value
       For n = 1 To Len(Verboten)
           Temp = Replace(Temp, Mid(Verboten, n, 1), " ")
       Next
       Temp = WorksheetFunction.Trim(Temp)
       Temp = Replace(Temp, " ", "_")
       C.Value = Temp
   Next C
   With Application
       .ScreenUpdating = True
       .EnableEvents = True
   End With
End Sub

  4. Wähle den Bereich in deiner Excel-Tabelle aus, in dem Du die Sonderzeichen entfernen möchtest.

  5. Führe das Makro aus: Gehe zurück zu Excel, drücke ALT + F8, wähle SondZeichloeschen2 aus der Liste und klicke auf "Ausführen".

Der Code entfernt alle definierten Sonderzeichen und ersetzt Leerzeichen durch Unterstriche.

Häufige Fehler und Lösungen

  • Fehler: '1004 - No cells were found'

    • Lösung: Das passiert, wenn Du .SpecialCells(xlCellTypeConstants, 1) verwendest. Stelle sicher, dass Du diesen Teil entfernst, wenn Du den gesamten Bereich markieren möchtest.

  • Fehler: Alle Zellen werden gelöscht

    • Lösung: Achte darauf, dass Du die Zeile Temp = "" nicht im Anfangscode verwendest. Der Wert von Temp sollte von der Zelle übernommen werden: Temp = C.Value.

Alternative Methoden

Neben VBA gibt es auch die Möglichkeit, Excel-Formeln zu verwenden, um Sonderzeichen zu entfernen. Eine einfache Methode ist die Nutzung von:

  • =WECHSELN(A1; "Sonderzeichen"; "_") für jedes Sonderzeichen, das Du ersetzen möchtest.
  • Für eine umfassendere Lösung kannst Du auch die FILTER-Funktion in Kombination mit TEXTKETTE verwenden, um die gewünschten Zeichen zu extrahieren.

Eine weitere Methode ist die Verwendung von Regulären Ausdrücken (RegEx) in VBA, um komplexere Muster zu erkennen und zu ersetzen.

Praktische Beispiele

Hier sind einige praktische Beispiele, wie Du Sonderzeichen in Excel entfernen oder ersetzen kannst:

  1. Beispiel 1: Wenn Du einen Text wie @Hello*World! hast, wird er durch den VBA-Code zu Hello_World_.
  2. Beispiel 2: Ein Text mit mehreren Sonderzeichen wie #Test%Data^123 wird zu Test_Data_123.

Um sicherzustellen, dass der Code für Deine Excel-Version funktioniert, teste ihn in einer Excel-Version ab 2010.

Tipps für Profis

  • Verwende die Application.Match-Funktion, um zu prüfen, ob ein Zeichen in einer Liste von erlaubten Zeichen enthalten ist, bevor Du es entfernst.
  • Optimiere den RegEx-Code, um mehrere Sonderzeichen in einem Schritt zu ersetzen. Dies kann die Ausführungsgeschwindigkeit verbessern.
  • Teste Deinen Code in einer Kopie Deiner Datei, um unbeabsichtigte Datenverluste zu vermeiden.

FAQ: Häufige Fragen

1. Wie kann ich mehrere Sonderzeichen auf einmal entfernen?
Du kannst alle gewünschten Sonderzeichen in der Verboten-Variable definieren und der Code wird sie alle in einem Durchgang verarbeiten.

2. Funktioniert dieser Code in alle Excel-Versionen?
Ja, dieser VBA-Code sollte ab Excel 2010 in den meisten Versionen funktionieren, solange Du den VBA-Editor nutzen kannst.

3. Was sind die Vorteile der Verwendung von VBA gegenüber Excel-Formeln?
VBA ermöglicht komplexere Operationen und kann große Datenmengen schneller verarbeiten, während Excel-Formeln in der Regel einfacher und leichter verständlich sind.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige