ERSATZ-Funktion Excel - Text suchen und ersetzen

Beispielarbeitsbuch herunterladen

Laden Sie die Beispielarbeitsmappe herunter

Dieses Tutorial zeigt, wie Sie das Excel ERSATZ-Funktion in Excel zum Suchen und Ersetzen von Text.

Übersicht über die Funktion SUBSTITUTE

Die SUBSTITUTE-Funktion sucht und ersetzt vorhandenen Text durch eine neue Textzeichenfolge. Groß-/Kleinschreibung beachten.

Um die Excel-Arbeitsblattfunktion ERSATZ zu verwenden, wählen Sie eine Zelle aus und geben Sie Folgendes ein:

(Beachten Sie, wie die Formeleingaben angezeigt werden)

SUBSTITUTE-Funktion Syntax und Eingaben:

1	=ERSETZEN(text,alter_text,neuer_text,instanz_num)

Text - Die ursprüngliche Textzeichenfolge.

alter_text - Die Textzeichenfolge, die Sie suchen und ersetzen möchten.

neuer_test - Der Ersatztext.

Instanz_num - Optional. Die zu ersetzende Instanznummer. Wenn leer, werden alle Instanzen ersetzt.

Was ist ERSATZ?

Die Funktion SUBSTITUTE in Excel wird verwendet, um eine Zeichenfolge in einer Textzeichenfolge durch eine andere angegebene Textzeichenfolge zu ersetzen.

Schauen wir uns das folgende Beispiel an, in dem wir die Zeichenfolge „quick brown“ durch „slow white“ ersetzen.

1	=ERSETZEN(C2,C3,C4)

SUBSTITUTE sucht in der Saite in C2, findet „quick brown“ und ersetzt es durch „slow white“.

So verwenden Sie SUBSTITUTE

Die Funktion SUBSTITUTE nimmt 3 erforderliche Argumente und 1 optionales Argument an:

• Text: Der Text, den Sie ersetzen möchten
• Old_text: Der Text, den Sie ersetzen möchten
• New_text: Der Text, durch den Sie Old_text ersetzen möchten
• Instance_num (optional): Gibt das Vorkommen von Old_text an, das Sie durch New_text ersetzen möchten. Standardmäßig werden alle Vorkommen von Old_text ersetzt, wenn keine Instanz angegeben wird.

Verschachtelung ERSATZ

Was wäre, wenn wir zwei Teile derselben Saite ersetzen müssten? Sehen wir uns an, wie wir zwei SUBSTITUTE-Funktionen kombinieren würden.

1	=ERSETZEN(ERSETZEN(C2,C3,C4),C5,C6)

Beachten Sie, wie das Ergebnis von SUBSTITUTE(C2,C3,C4) als erstes Argument für das zweite SUBSTITUTE verwendet wird. Wenn wir die Formel auseinanderbrechen:

=ERSETZEN(C2,C3,C4) ergibt „The langsam weiß Fuchs springt über den faulen Hund“.

Dann SUBSTITUTE ("Der langsame weiße Fuchs springt über den faulen Hund", C5, C6), was zu "Der langsame Weiße" ausgewertet wird Löwe springt über den faulen Hund“.

Unterschied zwischen ERSATZ und ERSETZEN

Eine ähnliche Funktion wie SUBSTITUTE ist REPLACE. In den meisten Fällen können diese austauschbar verwendet werden, jedoch gilt die folgende Faustregel.

Die Funktion SUBSTITUTE sollte verwendet werden, wenn die Textkette ersetzt werden ist bekannt, oder ein ganzes Wort oder ganze Wörter werden ersetzt, wie in unserem zuvor verwendeten Fuchs-Beispiel.

1	=ERSETZEN(C2,C3,C4)

Die REPLACE-Funktion sollte verwendet werden, wenn die Position der Textzeichen in der Zeichenfolge ersetzt werden sind bekannt, oder wenn ein Teil eines Wortes oder einer Zeichenfolge ersetzt wird. Entfernen Sie beispielsweise einen Bindestrich aus einer Zahlenfolge.

Die REPLACE-Funktion kann auch wie SUBSTITUTE verschachtelt werden. Beachten Sie jedoch, dass sich die relative Position des Startzeichens je nach Länge der zu ersetzenden Zeichenfolge im ersten Schritt der verschachtelten Formel ändern kann.

Wenn Sie beispielsweise „schnelles Braun“ durch „langsames Weiß“ ersetzen, werden 11 Zeichen durch 10 Zeichen ersetzt. Bei einer Verschachtelung wäre das Startzeichen für das zweite REPLACE daher 1 Zeichen von den anfänglichen Zeichenpositionen entfernt.

ERSATZ-Tipps

• SUBSTITUTE kann für Teilzeichenfolgen verwendet werden:
• Denken Sie daran, dass Sie eine instance_num angeben müssen, wenn Sie versuchen, eine Reihe von Zeichen zu ersetzen, die sich in der Zeichenfolge wiederholen:

Beachten Sie, dass die 3030 falsch ist, da sowohl „20“ als auch „20“ in „2020“ durch „30“ ersetzt wurden.

Interessiert an weiteren Textfunktionen?

Weitere Informationen zu REPLACE oder zur Verwendung anderer Textfunktionen in Excel wie LEFT, MID und RIGHT finden Sie in unseren anderen Artikeln.

ERSATZ in Google Tabellen

Die SUBSTITUTE-Funktion funktioniert in Google Sheets genauso wie in Excel:

Zusätzliche Bemerkungen

Bei der SUBSTITUTE-Funktion muss die Groß-/Kleinschreibung beachtet werden!. Sie können die Funktionen LOWER oder UPPER verwenden, um Ihre Textzeichenfolgen in eine konsistente Groß-/Kleinschreibung zu konvertieren, bevor Sie die Funktion SUBSTITUTE verwenden. Anschließend können Sie, falls gewünscht, die Funktion PROPER verwenden, um den ersten Buchstaben jedes Wortes groß zu schreiben.

Alternativ können Sie mit den Funktionen SEARCH und REPLACE ein SUBSTITUTE simulieren, bei dem die Groß-/Kleinschreibung nicht beachtet wird. Verwenden Sie die SEARCH-Funktion, um die Anfangsposition der Textzeichenfolge zu finden. Bei der SEARCH-Funktion wird die Groß-/Kleinschreibung nicht beachtet. Verwenden Sie dann die REPLACE-Funktion, um den Text zu ersetzen. Sie müssen das Ergebnis der SEARCH-Funktion als Start_num-Eingabe in REPLACE verwenden. Sie müssen auch die Anzahl der zu ersetzenden Zeichen definieren ( num_chars). Sie können sie manuell zählen oder die LEN-Funktion verwenden, um die Anzahl der Zeichen zu zählen. Beispiel:

Ersetzen - Bindestriche entfernen

Eine häufige Frage ist, wie Bindestriche aus Text entfernt werden. Der Trick besteht darin, den Bindestrich durch nichts zu ersetzen.

Nehmen wir für dieses Beispiel an, dass Zelle A1 Text mit Bindestrichen enthält. So entfernen Sie sie

1. Geben Sie in eine leere Hilfszelle ein =ERSETZEN(
2. Klicken Sie auf die Zelle, in der Sie die Zeichen ersetzen möchten, oder A1 (dies füllt automatisch Ihre Formel)
3. Typ, (Komma)
4. Geben Sie „-“ ein
5. Typ, (Komma)
6. Geben Sie „“ ein und drücken Sie die Eingabetaste
Ihr Endergebnis in Ihrer Helferzelle sollte so aussehen:
=ERSETZEN(A1“,-“,““)

3 Weitere Ersatzbeispiele:

In Zelle B3 möchten wir das Wort „krank“ durch „groß“ ersetzen. Wir können den folgenden Ausdruck verwenden:

1	=ERSETZEN(B3,"krank","groß",1)

Wenn der Text, den wir ersetzen möchten, mehr als einmal vorkommt, müssen wir die Instanz angeben, die wir meinen. Im zweiten Beispiel haben wir zwei Vorkommen des Wortes „alt“. Wenn wir also die zweite Instanz in das Wort "grau" ändern möchten, müssen wir Folgendes verwenden:

1	=ERSETZEN(B4,"alt","grau",2)

Beachten Sie, dass alle Instanzen ersetzt werden, wenn das Vorkommen nicht angegeben wird. So:

1	=ERSETZEN(B4,"alt","grau")

Würde alle Vorkommen des Wortes „alt“ entfernen und durch das Wort „grau“ ersetzen. Beachten Sie, dass die Zeichenfolge unverändert bleibt, wenn der alte Text nicht gefunden wird. Betrachten wir also das letzte Beispiel:

1	=ERSETZEN(B5,"schwarz","grau",1)

Bedeutet, dass wir versuchen, das Wort „schwarz“ durch das Wort „grau“ zu ersetzen. Da „schwarz“ jedoch nicht vorkommt, blieb die Originalsaite unverändert in den USA versandt:

Um die .XLSX-Datei aus diesem Artikel herunterzuladen, klicken Sie hier

ERSATZ-Beispiele in VBA

Sie können auch die SUBSTITUTE-Funktion in VBA verwenden. Typ:
application.worksheetfunction.substitute(text,alter_text,neuer_text,instanz_num)
Für die Funktionsargumente (Text usw.) können Sie diese entweder direkt in die Funktion eingeben oder stattdessen Variablen definieren.

Wenn der folgende Code ausgeführt wird

1	Range("A1") = WorksheetFunction.Substitute(Range("A1"), "using", "", 1)

Der Inhalt von Zelle A1 wird zu:

Das Wort „using“ wurde also durch eine leere Zeichenfolge ersetzt. Der letzte Parameter (1) gibt an, welche Instanz des Textes ersetzt werden soll, falls der Text mehr als einmal vorhanden ist. Wenn wir also zum Beispiel die folgende Anweisung ausführen

1	Range("A1") = WorksheetFunction.Substitute(Range("A1"), "the", "an", 1)

Die A1-Zelle sieht so aus

Beachten Sie, dass das erste Vorkommen von „the“ durch „an“ ersetzt wurde, während das zweite „the“ nicht betroffen war. Wenn wir den letzten Parameter weggelassen hätten, wären beide „die“ Wörter ersetzt worden

Zurück zur Liste aller Funktionen in Excel