Text mit SUBSTITUTE ersetzen

In Daten müssen häufig innerhalb von Formeln Texte gesucht und ersetzt werden: Bindestriche aus Telefonnummern entfernen, ein Wort austauschen oder überflüssige Symbole löschen.

Die Funktion SUBSTITUTE findet einen Textabschnitt in einer Zelle und ersetzt jedes Vorkommen durch einen neuen Text, ohne die ursprüngliche Zelle zu ändern.

Sie ist das formelbasierte Gegenstück zu Suchen & Ersetzen, wird aber automatisch aktualisiert, sobald sich Ihre Daten ändern.

SUBSTITUTE akzeptiert bis zu vier Argumente:

• text die ursprüngliche Zeichenfolge oder Zelle
• old_text der zu suchende Text
• new_text der Text, der an seine Stelle gesetzt wird
• instance_num (optional) welches Vorkommen ersetzt werden soll

Die Syntax lautet =SUBSTITUTE(text, old_text, new_text, [instance_num]). Wenn Sie das letzte Argument weglassen, werden alle Vorkommen ersetzt.

Eine verbreitete Vorgehensweise ist, etwas durch nichts zu ersetzen, um es zu löschen. Setzen Sie dazu new_text auf eine leere Zeichenfolge "".

Wenn A2 den Wert 555-123-4567 enthält, entfernt die folgende Formel jeden Bindestrich und gibt 5551234567 zurück.

Dieses Muster bereinigt Telefonnummern, IDs und andere Werte mit Satzzeichen.

SUBSTITUTE funktioniert auch mit ganzen Wörtern, nicht nur mit einzelnen Zeichen.

Wenn A2 den Wert Jahresbericht 2023 enthält, können Sie das Jahr ersetzen, um es zu aktualisieren:

Das Ergebnis ist Jahresbericht 2024. Jedes übereinstimmende Vorkommen in der Zelle wird geändert.

Wichtig: SUBSTITUTE gleicht Text exakt ab, einschließlich der Groß- und Kleinschreibung.

Wenn A2 den Wert Cat cat CAT enthält, ändert =SUBSTITUTE(A2, "cat", "dog") nur das kleingeschriebene cat. Das Ergebnis lautet Cat dog CAT.

Wenn Sie Ersetzungen ohne Berücksichtigung der Groß- und Kleinschreibung benötigen, normalisieren Sie die Schreibweise zunächst mit LOWER oder UPPER, oder verwenden Sie die regulären Ausdrucksfunktionen aus der nächsten Lektion zu Sheets.

Mit dem optionalen vierten Argument instance_num können Sie ein einzelnes Vorkommen anstelle aller Vorkommen auswählen.

Wenn A2 den Wert a-b-c-d enthält und Sie nur den zweiten Bindestrich ändern möchten, setzen Sie instance_num auf 2.

Das Ergebnis ist a-b/c-d: Nur der zweite Bindestrich wurde zu einem Schrägstrich.

Stellen Sie sich einen importierten Betrag vor, der als Text gespeichert ist: $1,250.00. Um ihn in eine verwendbare Zahl umzuwandeln, müssen Sie das Dollarzeichen und das Komma entfernen.

Verschachteln Sie zwei Aufrufe von SUBSTITUTE, einen im anderen, um beide Zeichen zu entfernen.

Der innere Aufruf entfernt das Komma, der äußere das Dollarzeichen. Übrig bleibt 1250.00.

Sie können so viele Aufrufe von SUBSTITUTE verschachteln wie nötig, wobei jeder Aufruf ein anderes Zeichen bereinigt.

Um einen unübersichtlichen Code mit Leerzeichen, Punkten und Schrägstrichen in einen Code mit sauberen Bindestrichen umzuwandeln, verketten Sie drei Ersetzungen.

Jede Ebene übergibt ihr Ergebnis an die nächste und baut so die abschließend bereinigte Zeichenfolge auf.

Verwechseln Sie SUBSTITUTE nicht mit der ähnlichen Funktion REPLACE.

• SUBSTITUTE ersetzt Text, indem die Funktion seinen Inhalt abgleicht (dieses Wort finden und austauschen).
• REPLACE ersetzt Text anhand seiner Position (4 Zeichen ab Position 5 ersetzen).

Verwenden Sie SUBSTITUTE, wenn Sie wissen, was gefunden werden soll, und REPLACE, wenn Sie wissen, wo es sich befindet.

Ein cleverer Zusatz: Kombinieren Sie SUBSTITUTE mit LEN, um zu zählen, wie oft etwas vorkommt.

Subtrahieren Sie die Länge nach dem Entfernen eines Zeichens von der ursprünglichen Länge. Wenn A2 den Wert a,b,c,d enthält, werden damit die Kommas gezählt und 3 zurückgegeben.

Dieser Trick eignet sich hervorragend, um Elemente in einer durch Trennzeichen getrennten Liste zu zählen.

Angenommen, A2 enthält unübersichtliche Nummern mit Punkten und Leerzeichen wie 555.123 4567. Sie möchten ein einheitliches Format mit Bindestrichen: 555-123-4567.

Entfernen Sie zunächst die Punkte und Leerzeichen. Anschließend könnten Sie die Bindestriche wieder einsetzen; vorerst vereinheitlichen Sie einfach die Trennzeichen zu Bindestrichen.

Durch das Verschachteln von Ersetzungen werden beliebige vorkommende Trennzeichen vereinheitlicht.

Testen Sie Ihr Verständnis von SUBSTITUTE.

Sie haben gelernt, Texte in Formeln zu suchen und zu ersetzen:

• =SUBSTITUTE(text, old_text, new_text, [instance_num])
• Verwenden Sie "" als new_text, um Zeichen zu löschen.
• Bei der Funktion wird die Groß- und Kleinschreibung berücksichtigt; normalisieren Sie die Schreibweise bei Bedarf zuerst.
• Mit dem optionalen instance_num können Sie ein einzelnes Vorkommen auswählen.
• Verschachteln Sie Aufrufe, um mehrere Zeichen zu bereinigen, und kombinieren Sie sie mit LEN, um Vorkommen zu zählen.

Sie haben den Abschnitt zum Verketten und Ändern von Text abgeschlossen. Hervorragend – Sie erstellen immer sauberere und konsistentere Daten!