SUBSTITUTE in Excel (Inhaltsverzeichnis)
- ERSETZEN in Excel
- SUBSTITUTE-Formel in Excel
- Wie verwende ich SUBSTITUTE in Excel?
ERSETZEN in Excel
- Es handelt sich um eine Art Arbeitsblattfunktion. Die Ersatzfunktion ist eine integrierte Funktion, die als Zeichenfolge- oder Textfunktion kategorisiert wird
- Ersetzt neuen Text anstelle von altem oder vorhandenem Text in einer Textzeichenfolge
- Die Ersatzfunktion ist nützlich, wenn Sie ein bestimmtes Wort oder einen Text in einer Textzeichenfolge ersetzen möchten
- Es wird am häufigsten zum Ersetzen von Textzeichenfolgen aus importierten Daten verwendet
- Die Ersatzfunktion wird verwendet, wenn Sie Text anhand seines Inhalts und nicht anhand seiner Position ersetzen möchten
- Es ist nützlich, um Zeilenumbrüche in einer Zelle zu entfernen, die zu einer richtigen Zeichenfolge führen (Erläuterung in Beispiel: 4).
- Hiermit können Sie Konsistenz im gesamten Excel-Arbeitsblatt oder -Buch herstellen
Definition
SUBSTITUTE-Funktion ersetzt den alten Text durch neuen Text in einem Zellverweis oder einer Textzeichenfolge. Es werden alle Instanzen des alten Textes ersetzt, sofern nicht eine bestimmte Instanznummer ausgewählt wurde
SUBSTITUTE-Formel in Excel
Unten ist die SUBSTITUTE-Formel:
Die Funktion SUBSTITUTE hat folgende Argumente:
Text: Dies ist der Text oder Verweis auf eine Zelle, die Text enthält, für den Sie Zeichen einsetzen möchten
Old_text : Text, den Sie ersetzen oder entfernen möchten
Hinweis: Sie müssen dieses Argument immer in Anführungszeichen setzen, wenn Sie eine Textzeichenfolge verwenden. Es wird zwischen Groß- und Kleinschreibung unterschieden
New_text: Dies ist ein neuer Text, der alten Text ersetzt, oder eine Teilzeichenfolge, die Sie als Ersatz verwenden möchten
Hinweis: Sie müssen dieses Argument immer in Anführungszeichen setzen, wenn Sie eine Textzeichenfolge verwenden. Es wird zwischen Groß- und Kleinschreibung unterschieden
Instance_num: Dies ist ein optionaler Parameter. Er gibt an, welches Vorkommen von altem Text durch neuen Text ersetzt werden soll. Wenn dieser Parameter weggelassen oder nicht hinzugefügt wird, wird jedes Vorkommen von altem Text durch neuen Text ersetzt.
Mit diesem Argument können Sie angeben, bei welchem Vorkommen Sie ersetzen möchten
Wie verwende ich die Funktion SUBSTITUTE in Excel?
Diese SUBSTITUTE-Funktion ist sehr einfach zu bedienen. Lassen Sie uns nun anhand einiger Beispiele sehen, wie Sie die Funktion SUBSTITUTE in Excel verwenden.
Sie können diese SUBSTITUTE-Funktion in einer Excel-Vorlage hier herunterladen - SUBSTITUTE-Funktion in einer Excel-VorlageBeispiel 1
So ersetzen Sie die alte Zeichenfolge mit der Funktion SUBSTITUTE durch eine neue.
In diesem Szenario wird die alte Zeichenfolge (alter Wein) durch eine neue Zeichenfolge (neuer Wein) ersetzt, wobei die Ersatzfunktion verwendet wird
Die Argumente der Ersatzfunktion:
Text: Welchen Text möchten wir ändern? Es ist die Referenzzelle der Kontaktnummer, dh B6 ODER ALTER WEIN, dh = ERSATZ (B6,
Old_text: Hier möchten wir den vorhandenen alten Textstring ändern, dh OLD WINE. Geben Sie ihn also in doppelten Anführungszeichen ein. "OLD WINE" oder ein Zellverweis kann angegeben werden, dh = SUBSTITUTE (B6, C6,
New_text: Wir möchten es in NEW WINE ändern, geben Sie es also in doppelten Anführungszeichen ein, dh es kann "NEW WINE" oder eine Zellreferenz angegeben werden, dh = SUBSTITUTE (B6, C6, D6
Instance_num: Gibt an, auf welche bestimmte Instanz wir die Substitution ausrichten?
Hier ändern wir die gesamte Zeichenfolge in einer Zelle, daher muss sie leer bleiben oder weggelassen werden
Hier verwenden wir diese SUBSTITUTE (B6, C6, D6) -Formel:
Die Ausgabe wird sein:
Beispiel # 2
So ersetzen Sie ein bestimmtes Wort in einer Textzeichenfolge mit der Funktion SUBSTITUTE in Excel durch ein neues
Angenommen, ich möchte das erste " g" im Wort "google" durch "d" ersetzen .
Je nachdem, welche Instanznummer Sie im letzten Argument angeben, werden unterschiedliche Ergebnisse zurückgegeben
Fall 1: Angenommen, wenn ich das Argument Instance_num in der Funktion SUBSTITUTE weglasse, wird jedes nächste Vorkommen von altem Text durch neuen Text ersetzt, dh die gesamte Instanz wird ersetzt
SUBSTITUTE Funktion ohne Instance_num-Argument, dh = SUBSTITUTE (B12, C12, D12)
die Ausgabe wird sein:
Fall 2: Angenommen, wenn ich 1 in einem Instance_num-Argument in der SUBSTITUTE-Funktion erwähne, wird es nur beim ersten Auftreten von altem Text durch den angegebenen neuen Text ersetzt, oder es wird nur der Text in der ersten Instanz ersetzt
SUBSTITUTE Funktion mit dem Argument Instance_num ie = SUBSTITUTE (B13, C13, D13, 1),
die Ausgabe wird sein:
Beispiel # 3
Sonderzeichen durch Ersatzfunktion in Excel ersetzen
Ersetzen von "-" in einer Kontaktnummer 778-423-9684 durch "*"
Die Argumente der Ersatzfunktion:
Text: Welchen Text möchten wir ändern? Es ist Referenzzelle der Kontaktnummer, dh 778-423-9684
dh = SUBSTITUTE ("778-423-9684",
old_text: Wir wollen den Bindestrich (-) ändern, also tippe ihn in doppelte Anführungszeichen "-"
dh = SUBSTITUTE ("778-423-9684", "-",
New_text: Wir möchten es in das Sternzeichen "*" ändern, also geben Sie es in doppelten Anführungszeichen ein
dh = SUBSTITUTE ("778-423-9684", "-", "*"
Instance_num: Gibt an, auf welche bestimmte Instanz wir die Substitution ausrichten?
Hier müssen wir es zweimal ersetzen, daher muss es leer bleiben
dh = ERSATZ ("778-423-9684", "-", "*") oder = ERSATZ (K6, "-", "*")
Die Ausgabe wird sein:
Beispiel # 4
Entfernen von Zeilenvorschüben in einer Zelle mit der Funktion SUBSTITUTE.
Ersetzt das Zeilenvorschubzeichen (ANSI CODE 10) durch Leerzeichen und gibt den Ausgabewert der richtigen Zeichenfolge ohne zusätzliche Leerzeichen zurück
Erstellen einer Textzeichenfolge, die Zeilenumbrüche oder Feeds in einer Zelle enthält
Anfangs kann ein Zeilenumbruch in eine Zelle mit der Formel SUBSTITUTE eingefügt werden.
Der Inhalt in Zelle G13, H13 & I13 wird mit dem Et-Zeichen (&) zusammengeklebt. Dies ist ein Verkettungsoperator in Excel. Mit dem Et-Zeichen CHAR (10) wird ein Zeilenumbruch erstellt
CHAR (10): Dies ist ein Zeichencode für einen Zeilenumbruch in Excel
= G13 & CHAR (10) & H13 & CHAR (10) & I13
Es entsteht ein Ausgabetext mit Zeilenumbrüchen:
Hinweis: Bevor Sie eine Formel anwenden, müssen Sie sicherstellen, dass für eine Zelle, die einen Ausgabewert zurückgibt, der Zeilenumbruch aktiviert ist. Nur dann entsteht Text, der Zeilenumbrüche enthält
Anschließend wird eine Textzeichenfolge, die Zeilenumbrüche oder Feeds in einer Zelle enthält, in eine andere Zelle kopiert und durch eine Ersatzfunktion ersetzt
= SUBSTITUTE (L13, CHAR (10), ”“)
Die Ausgabe wird sein:
Es ersetzt das Zeilenvorschubzeichen (CHAR (10) oder ANSI-Code 10) durch ein Leerzeichen, was zu einer richtigen Zeichenfolge führt
Beispiel # 5
NESTED SUBSTITUTE (verschachtelter Ersatz), dh Ersetzen mehrerer Zeichen durch eine einzige Formel
Im folgenden Szenario enthält die Zelle "B18" eine Textzeichenfolge wie "PM1, DT1, T1".
Dabei steht „PM“ für „Projektmanager“, „DT“ für „Diabetes“ und „T“ für „Team“.
Ich möchte diese drei Codes durch vollständige Namen in einer anderen Zelle erweitern oder ersetzen
Dies kann durch Verwendung der folgenden drei Ersatzformeln in Excel erreicht werden
= ERSATZ ( B18, "PM", "Projektleiter")
= ERSATZ ( B18, "DT", "Diabetes")
= SUBSTITUTE ( B18, "T", "Team")
Und dann nisten sie ineinander:
= ERSATZ (ERSATZ (ERSATZ (B18, "PM", "Projektleiter"), "DT", "Diabetes"), "T", "Team")
Die Ausgabe wird sein:
Dinge, die Sie bei der Funktion SUBSTITUTE beachten sollten
- Die Funktion SUBSTITUTE unterstützt keine Platzhalter
- Wenn die Instanznummer nicht angegeben wird, wird die gesamte Instanz ersetzt (Erläuterung in EG 2, Fall 1).
- Instanznummer darf nicht negativ oder null sein
- SUBSTITUTE funktioniert auch für Zahlen und Sonderzeichen
- In der Funktion SUBSTITUTE müssen die Argumente old_text und new_text immer in doppelten Anführungszeichen angegeben werden
- Es wird zwischen Groß- und Kleinschreibung unterschieden
- Mit der Funktion SUBSTITUTE können zwei verschiedene Zeichen gleichzeitig entfernt werden. Wir können mehrere SUBSTITUTE-Funktionen in einer einzigen Formel verschachteln, um mehrere Ersetzungen gleichzeitig durchzuführen. (Erklärt in Beispiel 5: Verschachtelter Ersatz)
Empfohlene Artikel
Dies war eine Anleitung zur Funktion SUBSTITUTE. Hier diskutieren wir die SUBSTITUTE-Formel und die Verwendung der SUBSTITUTE-Funktion in Excel zusammen mit praktischen Beispielen und herunterladbaren Excel-Vorlagen. Sie können auch unsere anderen Artikelvorschläge durchgehen -
- Wie verwende ich TRANSPOSE in Excel?
- Wie verwende ich AND in Excel?
- Wie verwende ich LOOKUP in MS Excel?
- IPMT-Funktion in Excel