Text in Excel bearbeiten: Suchmuster, Zahlen, Sonderzeichen

Importierte Daten, Texte aus E-Mails und Freitextfelder sind selten sauber: Telefonnummern in fünf Schreibweisen, Zahlen mitten im Satz, Sonderzeichen, die beim Export stören. Hier sind die häufigsten Aufgaben gesammelt, jeweils mit Formel und VBA.

Suchen und Ersetzen mit Mustern

Reguläre Ausdrücke beschreiben Muster statt fester Texte: \d+ steht für eine oder mehrere Ziffern, \s+ für beliebig viele Leerzeichen. In Microsoft 365 gibt es dafür eigene Funktionen:

FunktionBeispielErgebnis
REGEXEXTRAHIEREN=REGEXEXTRAHIEREN(A1;"\d+")erste Zahl, etwa 55
REGEXEXTRAHIEREN mit 1=REGEXEXTRAHIEREN(A1;"\d+";1)alle Zahlen nebeneinander
REGEXERSETZEN=REGEXERSETZEN(A1;"\s+";" ")mehrfache Leerzeichen werden zu einem
REGEXTESTEN=REGEXTESTEN(A1;"Euro")WAHR oder FALSCH

Für ältere Versionen und für Makros übernimmt das RegExp-Objekt von Windows dieselbe Aufgabe:

Option Explicit

' Ersetzt alle Stellen, die zum Muster passen. Beispiel: RegexErsetzen(A1; "\s+"; " ") macht aus
' mehreren Leerzeichen eines. Ohne Verweis, über das RegExp-Objekt von Windows.
Public Function RegexErsetzen(ByVal text As String, ByVal muster As String, ByVal ersatz As String) As String
    With CreateObject("VBScript.RegExp")
        .Pattern = muster
        .Global = True                              ' alle Treffer, nicht nur den ersten
        .IgnoreCase = True
        RegexErsetzen = .Replace(text, ersatz)
    End With
End Function

' Liefert alle Zahlen eines Texts, getrennt durch ein Zeichen, etwa "25;4" aus "Preis 25 Euro für 4 Stück".
Public Function ZahlenAusText(ByVal text As String, Optional ByVal trenner As String = ";") As String
    Dim treffer As Object, t As Object, liste As String
    With CreateObject("VBScript.RegExp")
        .Pattern = "\d+(,\d+)?"                     ' ganze Zahlen und Dezimalzahlen mit Komma
        .Global = True
        Set treffer = .Execute(text)
    End With
    For Each t In treffer
        liste = liste & IIf(Len(liste) > 0, trenner, "") & t.Value
    Next t
    ZahlenAusText = liste
End Function

Aus „Max Mustermann GmbH“ wird so „Max Mustermann GmbH“, aus „+49 (911) 123-456“ die Folge „+49911123456“, und aus „Der Preis beträgt 55 Euro für diesen 4Artikel, Rabatt 2,5 %“ holt ZahlenAusText die Werte 55, 4 und 2,5.

Zur Zukunft von VBScript: Microsoft schafft VBScript in Windows schrittweise ab, und das RegExp-Objekt stammt aus dieser Bibliothek. Für VBA hat Microsoft angekündigt, die RegExp-Funktionen weiter bereitzustellen. Für neue Lösungen in Microsoft 365 sind die REGEX-Funktionen der Tabelle der zukunftssichere Weg.

Sonderzeichen entfernen

Für Dateinamen, Schnittstellen oder den Abgleich von Namen sollen nur Buchstaben, Ziffern und Leerzeichen übrig bleiben:

Option Explicit

' Entfernt alle Zeichen außer Buchstaben (auch Umlaute), Ziffern und Leerzeichen.
Public Function OhneSonderzeichen(ByVal text As String) As String
    Dim i As Long, z As String
    For i = 1 To Len(text)
        z = Mid$(text, i, 1)
        If z Like "[A-Za-z0-9 ]" Or InStr("ÄÖÜäöüß", z) > 0 Then OhneSonderzeichen = OhneSonderzeichen & z
    Next i
End Function

' Ändert die Schreibweise der markierten Zellen: 1 = klein, 2 = GROSS, 3 = Erster Buchstabe groß.
' Zellen mit Formeln bleiben unverändert.
Public Sub SchreibweiseAendern(ByVal art As Long)
    Dim zelle As Range
    If TypeName(Selection) <> "Range" Then Exit Sub
    For Each zelle In Selection.Cells
        If Not zelle.HasFormula And VarType(zelle.Value) = vbString Then
            Select Case art
                Case 1: zelle.Value = LCase$(zelle.Value)
                Case 2: zelle.Value = UCase$(zelle.Value)
                Case 3: zelle.Value = StrConv(zelle.Value, vbProperCase)
            End Select
        End If
    Next zelle
End Sub

OhneSonderzeichen("(Hallo!) Wer ist da? Grüße, Jörg & Co.") ergibt „Hallo Wer ist da Grüße Jörg Co“. Die Funktion legt fest, welche Zeichen erlaubt sind, statt eine Liste verbotener Zeichen zu pflegen, die nie vollständig ist. Wo ein entferntes Zeichen zwischen zwei Leerzeichen stand, bleiben zwei Leerzeichen stehen. GLÄTTEN in der Tabelle oder Application.Trim in VBA bereinigt das.

In Microsoft 365 als Formel: =REGEXERSETZEN(A1;"[^A-Za-zÄÖÜäöüß0-9 ]";"").

Zeichen und Wörter zählen

Ohne VBA zählt diese Formel, wie oft „e“ in A1 vorkommt:

=LÄNGE(A1)-LÄNGE(WECHSELN(A1;"e";""))

WECHSELN entfernt alle „e“, der Längenunterschied ist ihre Anzahl. WECHSELN unterscheidet Groß- und Kleinschreibung. Sollen beide zählen, verwenden Sie WECHSELN(KLEIN(A1);"e";""). Die Anzahl der Wörter liefert in Microsoft 365 =ANZAHL2(TEXTTEILEN(GLÄTTEN(A1);" ")). Für „Wo steht das geschrieben?“ ergab das 4 Wörter und 4-mal „e“.

Groß- und Kleinschreibung ändern

Als Formel: =GROSS(A1), =KLEIN(A1) oder =GROSS2(A1) für den ersten Buchstaben jedes Worts groß. Soll der Text in den Zellen selbst geändert werden, hilft das Makro SchreibweiseAendern aus dem Modul oben: Markieren Sie die Zellen und rufen Sie es mit 1 (klein), 2 (groß) oder 3 (erster Buchstabe groß) auf.

Ältere Fassungen dieses Makros schreiben jede Zelle mit zelle = LCase(zelle.Value) neu. Steht in einer Zelle eine Formel, ist sie danach durch ihr Ergebnis ersetzt. Das Makro oben überspringt Zellen mit Formeln und Zahlen. Aus „MÜLLER gmbh“ wird „Müller Gmbh“, Formeln bleiben erhalten.

Ohne Formel und Makro geht es auch mit der Blitzvorschau: In der Nachbarspalte das gewünschte Ergebnis für die erste Zeile eintippen, in der zweiten Zeile Strg+E drücken.

Typische Fehler

  • Muster zu großzügig: \d+ findet auch Ziffern in Artikelnummern oder Datumsangaben. Testen Sie Muster an echten Daten und grenzen Sie sie bei Bedarf ein, etwa mit \d+(?= Euro) für Zahlen vor „Euro“ (nur mit REGEX-Funktionen).
  • Kein Treffer: REGEXEXTRAHIEREN liefert #NV, wenn nichts passt. WENNFEHLER fängt das ab.
  • Umlaute fehlen: Ein Muster wie [A-Za-z] umfasst keine Umlaute. Ergänzen Sie ÄÖÜäöüß ausdrücklich.

Passende Beiträge

Lieber machen lassen?

Wir programmieren die Lösung für Ihre Datei.

Vom einzelnen Makro bis zur Anwendung für die ganze Abteilung. Mit KI-Unterstützung setzen wir vieles schneller und günstiger um, jede Lösung testen wir vor der Übergabe.