Verwendung der Funktionen REPLACE und SUBSTITUTE in Excel

Verwendung der Funktionen REPLACE und SUBSTITUTE in Excel – Formelbeispiele

Das Tutorial erklärt die Funktionen Excel REPLACE und SUBSTITUTE mit Anwendungsbeispielen. Erfahren Sie, wie Sie die Funktion REPLACE mit Textzeichenketten, Zahlen und Daten verwenden und wie Sie mehrere REPLACE- oder SUBSTITUTE-Funktionen innerhalb einer Formel verschachteln.

Letzte Woche haben wir verschiedene Möglichkeiten diskutiert, wie Sie die Funktionen excel plz und ort trennen sowie FIND und SEARCH in Ihren Excel-Arbeitsblättern verwenden können. Heute werden wir uns zwei weitere Funktionen genauer ansehen, um Text in einer Zelle basierend auf ihrer Position zu ersetzen oder eine Zeichenkette durch eine andere basierend auf dem Inhalt zu ersetzen. Wie Sie vielleicht schon erraten haben, spreche ich von den Funktionen Excel REPLACE und SUBSTITUTE.
excel plz und ort trennen

 

Excel REPLACE-Funktion

Mit der Funktion REPLACE in Excel können Sie ein oder mehrere Zeichen einer Textzeichenkette mit einem anderen Zeichen oder einem Zeichensatz austauschen.
REPLACE(old_text, start_num, num_chars, new_text)

Wie Sie sehen, hat die Excel REPLACE-Funktion 4 Argumente, die alle benötigt werden.

Alter_Text – der Originaltext (oder eine Referenz auf eine Zelle mit dem Originaltext), in dem Sie einige Zeichen ersetzen möchten.
Start_num – die Position des ersten Zeichens innerhalb von old_text, das Sie ersetzen möchten.
Num_chars – die Anzahl der Zeichen, die Sie ersetzen möchten.
New_text – der Ersetzungstext.

Um beispielsweise das Wort „Sonne“ in „Sohn“ zu ändern, können Sie die folgende Formel verwenden:

=REPLACE(„sun“, 2, 1, 1, „o“)

Und wenn Sie das Originalwort in eine Zelle legen, z.B. A2, können Sie die entsprechende Zellreferenz im old_text-Argument angeben:

=Ersetzen (A2, 2, 1, 1, „o“)
Excel REPLACE-Funktion
Hinweis. Wenn das Argument start_num oder num_chars negativ oder nicht-numerisch ist, gibt eine Excel Replace-Formel den Fehler #VALUE! zurück.

Verwendung der Excel REPLACE-Funktion mit numerischen Werten

Die Funktion REPLACE in Excel ist für die Arbeit mit Textzeichenketten konzipiert. Natürlich können Sie damit z.B. numerische Zeichen ersetzen, die Teil einer Textzeichenkette sind:

=ERSETZEN(A2, 7, 4, „2016“)
Ersetzen von numerischen Zeichen, die Teil einer Textzeichenkette sind.

Beachten Sie, dass wir „2016“ in doppelte Anführungszeichen setzen, wie Sie es normalerweise mit Textwerten tun.

Auf ähnliche Weise können Sie eine oder mehrere Ziffern innerhalb einer Zahl ersetzen. Zum Beispiel:

=ERSETZEN(A4, 4, 4, 4, 4, 4, „6“)

Und wieder müssen Sie den Wiederbeschaffungswert in doppelte Anführungszeichen („6“) setzen.

Achten Sie darauf, den Wiederbeschaffungswert in doppelten Anführungszeichen anzugeben.
Hinweis. Eine Excel REPLACE-Formel gibt immer eine Textzeichenfolge und keine Zahl zurück. Im obigen Screenshot sehen Sie die linke Ausrichtung des zurückgegebenen Textwertes in B2 und vergleichen Sie ihn mit der rechtsbündigen Originalnummer in A2. Und weil es sich um einen Textwert handelt, können Sie ihn nicht in anderen Berechnungen verwenden, es sei denn, Sie konvertieren ihn zurück in die Zahl, z.B. durch Multiplikation mit 1 oder durch Verwendung einer anderen Methode, die unter Wie man Text in Zahl konvertiert.

Excel REPLACE-Funktion mit Datum verwenden

Wie Sie gerade gesehen haben, funktioniert die REPLACE-Funktion gut mit Zahlen, nur dass sie eine Textzeichenkette zurückgibt :) Da im internen Excel-System Daten als Zahlen gespeichert werden, können Sie versuchen, einige Ersetzungsformeln für Daten zu verwenden. Die Ergebnisse wären sehr peinlich.

Zum Beispiel haben Sie ein Datum in A2, z.B. 1-Okt-14, und Sie möchten „Okt“ auf „Nov“ ändern. Sie schreiben also die Formel REPLACE(A2, 4, 3, „Nov“), die Excel anweist, 3 Zeichen in den Zellen A2 beginnend mit dem vierten Zeichen…. zu ersetzen und erhalten folgendes Ergebnis:

Ein falscher Weg, die Funktion REPLACE an Daten zu verwenden.

Warum ist das so? Denn „01-Okt-14“ ist nur eine visuelle Darstellung der zugrunde liegenden Seriennummer (41913), die das Datum darstellt. Unsere Ersetzen-Formel ändert also die letzten 3 Ziffern der obigen Seriennummer in „Nov“ und gibt die Zeichenkette „419Nov“ zurück.

Damit die Excel REPLACE-Funktion korrekt mit Datumsangaben arbeiten kann, können Sie Datumsangaben zunächst mit der TEXT-Funktion oder einer anderen Technik, die in How to convert date to text in Excel demonstriert wird, in Textstellen umwandeln. Alternativ können Sie die Funktion TEXT direkt in das Argument old_text der Funktion REPLACE einbetten:

=Ersetzen (TEXT(A2, „dd-mmm-yyy“), 4, 3, „Nov“)
Die richtige Verwendung der Funktion REPLACE an Datumsangaben

Bitte beachten Sie, dass das Ergebnis der obigen Formel eine Textzeichenfolge ist, und daher funktioniert diese Lösung nur, wenn Sie nicht planen, die geänderten Daten für weitere Berechnungen zu verwenden. Wenn Sie eher Datumsangaben als Textzeichenketten benötigen, verwenden Sie die Funktion DATEVALUE, um die von der Funktion Excel REPLACE zurückgegebenen Werte wieder auf Datumswerte zurückzusetzen:

=DATEVALUE(REPLACE(TEXT(A2, „dd-mmm-yyyy“), 4, 3, „Nov“))))
Verschachtelte REPLACE-Funktionen, um mehrere Ersetzungen in einer Zelle durchzuführen.

Sehr oft müssen Sie möglicherweise mehr als einen Ersatz in derselben Zelle durchführen. Natürlich können Sie auch eine Ersetzung durchführen, ein Zwischenergebnis in eine zusätzliche Spalte ausgeben und dann die Funktion REPLACE erneut verwenden. Besser und professioneller ist es jedoch, verschachtelte REPLACE-Funktionen zu verwenden, mit denen Sie mehrere Ersetzungen mit einer einzigen Formel durchführen können. Verschachteln“ bedeutet in diesem Zusammenhang, eine Funktion in eine andere zu stellen.

Betrachten Sie das folgende Beispiel. Angenommen, Sie haben eine Liste von Telefonnummern in Spalte A im Format „123456789“ und möchten diese durch Hinzufügen von Bindestrichen eher wie Telefonnummern aussehen lassen. Mit anderen Worten, Ihr Ziel ist es, „123456789“ in „123-456-789″ zu verwandeln.

Das Einfügen des ersten Bindestrichs ist einfach. Sie schreiben eine übliche Excel Replace-Formel, die Null Zeichen durch einen Bindestrich ersetzt, d.h. einen Bindestrich an der vierten Stelle in einer Zelle hinzufügt:

=ERSETZEN(A2,4,0, 0,“-„)

Das Ergebnis der obigen Ersetzen-Formel lautet wie folgt:
Die REPLACE-Formel, um einen Bindestrich an der vierten Stelle in einer Zelle hinzuzufügen.

Okay, und jetzt müssen wir noch einen weiteren Bindestrich an der 8. Stelle einfügen. Dazu stellen Sie die obige Formel in eine andere Excel REPLACE-Funktion. Genauer gesagt, betten Sie es in das old_text-Argument der anderen Funktion ein, so dass die zweite REPLACE-Funktion den von der ersten REPLACE zurückgegebenen Wert und nicht den Wert in Zelle A2 behandelt:

=ERSETZEN (ERSETZEN(ERSETZEN(A2,4,0,“-„),8,0,“-„)

Als Ergebnis erhalten Sie die Telefonnummern in der gewünschten Formatierung:
Verschachtelte REPLACE-Funktionen in Excel verwenden

In ähnlicher Weise können Sie verschachtelte REPLACE-Funktionen verwenden, um Textzeichenketten wie Datumsangaben aussehen zu lassen, indem Sie gegebenenfalls einen Schrägstrich (/) hinzufügen:

=ERSETZEN(ERSETZEN(ERSETZEN(A2,3,0,“/“),6,0,“/“)))
Eine verschachtelte REPLACE-Formel, um Textzeichenketten wie Datumsangaben aussehen zu lassen.

Darüber hinaus können Sie Textzeichenketten in echte Daten umwandeln, indem Sie die obige REPLACE-Formel mit der Funktion DATEVALUE umbrechen:

=DATUMSWERT(ERSETZEN(ERSETZEN(ERSETZEN(A2,3,0,“/“),6,0,“/“)))

Und natürlich sind Sie nicht in der Anzahl der Funktionen eingeschränkt, die Sie innerhalb einer Formel verschachteln können (die modernen Versionen von Excel 2010, 2013 und 2016 erlauben bis zu 8192 Zeichen und bis zu 64 verschachtelte Funktionen in einer Formel).

Beispielsweise können Sie mit 3 verschachtelten REPLACE-Funktionen eine Zahl in A2 wie Datum und Uhrzeit darstellen lassen:

=ERSETZEN (ERSETZEN (ERSETZEN (ERSETZEN (ERSETZEN (A2,3,0,“/“) ,6,0,“/“), 9,0, “ “ „), 12,0, „:“)

This entry was posted in Exel. Bookmark the permalink.