Generische Formel
=SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)),INDEX(find,2),INDEX(replace,2))
Zusammenfassung
Um mehrere Werte zu finden und durch eine Formel zu ersetzen, können Sie mehrere SUBSTITUTE-Funktionen zusammenschachteln und mithilfe der INDEX-Funktion Such- / Ersetzungspaare aus einer anderen Tabelle eingeben. In dem gezeigten Beispiel führen wir 4 separate Such- und Ersetzungsvorgänge durch. Die Formel in G5 lautet:
=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))
Dabei ist "find" der benannte Bereich E5: E8 und "replace" der benannte Bereich F5: F8. Im Folgenden finden Sie Informationen dazu, wie Sie diese Formel leichter lesbar machen können.
Vorwort
Es gibt keine integrierte Formel zum Ausführen einer Reihe von Such- und Ersetzungsvorgängen in Excel. Daher handelt es sich um eine "Konzept" -Formel, die einen Ansatz zeigt. Der zu suchende und zu ersetzende Text wird direkt im 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 fest in die Formel codieren, wenn Sie dies bevorzugen.
Erläuterung
Im Kern verwendet die Formel die SUBSTITUTE-Funktion, um jede Substitution mit diesem Grundmuster durchzuführen:
=SUBSTITUTE(text,find,replace)
"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 links befinden sich im benannten Bereich "find" und die Werte rechts befinden sich im benannten Bereich "replace". Die INDEX-Funktion wird verwendet, um sowohl den "Find" -Text als auch den "Replace" -Text wie folgt abzurufen:
INDEX(find,1) // first "find" value INDEX(replace,1) // first "replace" value
Um die erste Substitution durchzuführen (nach "rot" suchen, durch "pink" ersetzen), verwenden wir:
=SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1))
Insgesamt führen wir vier separate Substitutionen durch, und jedes nachfolgende SUBSTITUT beginnt mit dem Ergebnis des vorherigen SUBSTITUT:
=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))
Zeilenumbrüche zur besseren Lesbarkeit
Sie werden feststellen, dass diese Art von verschachtelter Formel ziemlich schwer zu lesen ist. Durch Hinzufügen von Zeilenumbrüchen können wir das Lesen und Verwalten 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))
In der Formelleiste in Excel werden zusätzliche Leerzeichen und Zeilenumbrüche ignoriert, sodass die obige Formel direkt eingefügt werden kann:
Übrigens gibt es eine Tastenkombination zum Erweitern und Reduzieren der Formelleiste.
Weitere Substitutionen
Der Tabelle können weitere Zeilen hinzugefügt werden, um mehr Such- / Ersetzungspaare zu verarbeiten. Jedes Mal, wenn ein Paar hinzugefügt wird, muss die Formel aktualisiert werden, um das neue Paar einzuschließen. Es ist auch wichtig sicherzustellen, dass die benannten Bereiche (falls Sie sie verwenden) aktualisiert werden, um bei Bedarf neue Werte aufzunehmen. Alternativ können Sie anstelle von benannten Bereichen eine geeignete Excel-Tabelle für Dynamikbereiche verwenden.
Andere Verwendungen
Der gleiche Ansatz kann zum Bereinigen von Text verwendet werden, indem Interpunktion und andere Symbole mit einer Reihe von Ersetzungen aus dem Text "entfernt" werden. Die Formel auf dieser Seite zeigt beispielsweise, wie Telefonnummern bereinigt und neu formatiert werden.
