Mehrere Werte suchen und ersetzen

Um mehrere Werte durch eine Formel zu suchen und zu ersetzen, können Sie mehrere SUBSTITUTE-Funktionen miteinander verschachteln und mithilfe der INDEX-Funktion Such-/Ersetzungspaare aus einer anderen Tabelle eingeben. Im gezeigten Beispiel führen wir 4 separate Suchen- und Ersetzen-Operationen durch. Die Formel in G5 lautet:

= SUBSTITUTE ( SUBSTITUTE (B5, INDEX (find,1), INDEX (replace,1)), INDEX (find,2), INDEX (replace,2))

wo 'finden' ist benannter Bereich E5:E8, und 'replace' ist der benannte Bereich F5:F8. Unten finden Sie Informationen dazu, wie Sie diese Formel leichter lesbar machen.

Vorwort

Es gibt keine integrierte Formel zum Ausführen einer Reihe von Suchen- und Ersetzen-Vorgängen in Excel, daher ist dies eine 'Konzeptformel', um einen Ansatz zu zeigen. Der zu suchende und zu ersetzende Text wird direkt auf dem Arbeitsblatt in einer Tabelle gespeichert und mit der INDEX-Funktion abgerufen. Dies macht die Lösung „dynamisch“ – jeder dieser Werte wird geändert, die Ergebnisse werden sofort aktualisiert. Natürlich ist es nicht erforderlich, INDEX zu verwenden, Sie können Werte in die Formel hartcodieren, wenn Sie dies bevorzugen.

Im Kern verwendet die Formel die Funktion SUBSTITUTE, um jede Ersetzung mit diesem Grundmuster durchzuführen:

 
= SUBSTITUTE ( SUBSTITUTE ( SUBSTITUTE ( SUBSTITUTE (B5, INDEX (find,1), INDEX (replace,1)), INDEX (find,2), INDEX (replace,2)), INDEX (find,3), INDEX (replace,3)), INDEX (find,4), INDEX (replace,4))

'Text' ist der eingehende Wert, 'find' ist der zu suchende Text und 'replace' ist der zu ersetzende Text. Der zu suchende und zu ersetzende Text wird in der Tabelle rechts im Bereich E5:F8 gespeichert, ein Paar pro Zeile. Die Werte auf der linken Seite sind in der benannter Bereich 'find' und die Werte rechts liegen im benannten Bereich 'replace'. Die INDEX-Funktion wird verwendet, um sowohl den 'Suchen'-Text als auch den 'Ersetzen'-Text wie folgt abzurufen:

Wie zähle ich, wie oft ein Wert in einer Spalte in Excel angezeigt wird?

 
= SUBSTITUTE (text,find,replace)

Um die erste Substitution auszuführen (suchen Sie nach 'rot', ersetzen Sie sie durch 'pink') verwenden wir:

 
 INDEX (find,1) // first 'find' value  INDEX (replace,1) // first 'replace' value

Insgesamt führen wir vier separate Ersetzungen durch, und jeder nachfolgende ERSATZ beginnt mit dem Ergebnis des vorherigen ERSATZ:

wie man die Gesamtspalte in Excel hinzufügt

 
= SUBSTITUTE (B5, INDEX (find,1), INDEX (replace,1))

Zeilenumbrüche zur besseren Lesbarkeit

Sie werden feststellen, dass diese Art von verschachtelter Formel ziemlich schwer zu lesen ist. Durch das Hinzufügen von Zeilenumbrüchen können wir die Lesbarkeit und Pflege der Formel erheblich vereinfachen:

 
= SUBSTITUTE ( SUBSTITUTE ( SUBSTITUTE ( SUBSTITUTE (B5, INDEX (find,1), INDEX (replace,1)), INDEX (find,2), INDEX (replace,2)), INDEX (find,3), INDEX (replace,3)), INDEX (find,4), INDEX (replace,4))

Die Bearbeitungsleiste in Excel ignoriert zusätzlichen Leerraum und Zeilenumbrüche, sodass die obige Formel direkt eingefügt werden kann:

Übrigens gibt es a Tastaturkürzel zum Erweitern und Reduzieren der Bearbeitungsleiste.

Weitere Auswechslungen

Der Tabelle können weitere Zeilen hinzugefügt werden, um mehr Suchen/Ersetzen-Paare zu verarbeiten. Jedes Mal, wenn ein Paar hinzugefügt wird, muss die Formel aktualisiert werden, um das neue Paar aufzunehmen. Es ist auch wichtig, sicherzustellen, dass die benannten Bereiche (wenn Sie sie verwenden) aktualisiert werden, um bei Bedarf neue Werte aufzunehmen. Alternativ können Sie a . verwenden richtige Excel-Tabelle für dynamische Bereiche anstelle von benannten Bereichen.

Andere Verwendungen

Der gleiche Ansatz kann verwendet werden, um Text zu bereinigen, indem Satzzeichen und andere Symbole mit einer Reihe von Ersetzungen aus dem Text entfernt werden. Die Formel auf dieser Seite zeigt beispielsweise, wie Sie Telefonnummern bereinigen und neu formatieren .