Auch bei der Arbeit mit Excel hat man ständig mit Texten zu tun, die in irgendeiner Weise zu verarbeiten sind: Artikel- oder Kundenlisten, Mitarbeiterlisten, Kataloge jeglicher Art – die Möglichkeiten sind vielfältig.
Bei der Manipulation dieser Texte geht es häufig darum, bestimmte Muster oder Zeichenketten zu finden, Teile davon herauszuschneiden oder auszutauschen. Dabei helfen unter anderem die folgenden Excel-Funktionen:
- FINDEN und SUCHEN
- ERSETZEN und WECHSELN
Da die Funktionspaare sehr ähnlich aufgebaut sind aber teilweise unterschiedlich funktionieren, ist es für den optimalen Einsatz wichtig, diese Unterschiede zu kennen und zu wissen, wie das Zusammenspiel funktioniert.
Und so geht’s:
FINDEN und SUCHEN
Die Syntax ist bei beiden Funktionen die gleiche:
=FINDEN(Suchtext;Text;[ErstesZeichen])
=SUCHEN(Suchtext;Text;[ErstesZeichen])
- Suchtext die Zeichenkette, nach der gesucht wird.
- Text ist die Zeichenkette, in der man nach seiner Zeichenkette sucht.
- ErstesZeichen ist optional, braucht also nicht zwingend angegeben zu werden. Es kommt dann zum Einsatz, wenn man mit der Suche nicht am Textanfang beginnen möchte, sondern erst ab einer bestimmten Stelle.
Dabei kann für beide Argumente entweder ein fester Text in Anführungszeichen oder ein Bezug auf eine Zelle übergeben werden, in sich der Suchtext bzw. der zu durchsuchende Text befindet.
Beide Funktionen liefern als Ergebnis die Position zurück, an der sich die gesuchte Zeichenkette befindet. Kommt die Zeichenkette mehrmals vor, wird die erste gefundene Position angegeben. Wurde nichts gefunden, so wird der Fehler #WERT! zurückgeliefert.
Wo sind also die Unterschiede und wann setze ich welche der beiden Funktionen ein?
Die FINDEN-Funktion berücksichtigt Groß- und Kleinschreibung, erlaubt jedoch keine Platzhalterzeichen.
Bei der SUCHEN-Funktion hingegen spielt die Groß- und Kleinschreibung keine Rolle, dafür sind wiederum die Platzhalterzeichen (? und *) erlaubt.
Man kann sich also merken, dass man SUCHEN für die etwas „gröbere“ Suche verwenden sollte, während FINDEN immer dann zum Einsatz kommt, wenn man nur ganz spezifische Texte mit exakter Übereinstimmung finden möchte.
Die folgenden Beispiele zeigen den Unterschied in der Funktionsweise:
Der Unterschied zwischen SUCHEN und FINDEN
ERSETZEN und WECHSELN
Die Syntax der beiden Funktionen ist etwas unterschiedlich. Sehen wir sie uns nacheinander an:
=ERSETZEN(AlterText;ErstesZeichen;AnzahlZeichen;NeuerText)
Alle vier Argumente sind dabei zwingend notwendig:
- AlterText ist die Zeichenkette, in der man einige Zeichen ersetzen möchte.
- ErstesZeichen gibt die Startposition an, ab der die Zeichen ersetzt werden sollen.
- AnzahlZeichen legt fest, wieviele Zeichen in dem alten Text ersetzt werden sollen.
- NeuerText ist der Text, der an Stelle des alten Textes erscheinen soll.
=WECHSELN(Text;AlterText;NeuerText;[ntes-Auftreten])
- Text ist der Text, in dem etwas ausgewechselt werden soll.
- AlterText ist die Zeichenkette, die ausgewechselt werden soll.
- NeuerText ist der Text, der an Stelle des alten Textes erscheinen soll.
- [ntes-Auftreten] ist ein optionales Argument. Wenn der gesuchte Text mehrmals vorkommt, legt man damit fest, der wievielte davon ausgewechselt werden soll. Lässt man dieses Argument weg, wird der gesuchte Text an allen gefundenen Positionen ausgewechselt.
Diese ersten drei Argumente sind zwingend notwendig. Dabei kann man den Text entweder in Anführungszeichen angeben oder einen Bezug auf die Zelle, die den jeweiligen Text enthält.
Wann sollte man also ERSETZEN und wann WECHSELN verwenden?
WECHSELN kommt dann zum Einsatz, wenn ich einen bestimmten Text durch einen anderen Text auswechseln möchte. Dabei wird auch Groß- und Kleinschreibung berücksichtigt.
ERSETZEN kommt dann zum Einsatz, wenn man in einem Text an einer bestimmten Position beginnende Zeichenketten mit einer festen Länge ersetzen möchte. Man sucht also nicht nach einem bestimmten Text, sondern nach einer bestimmten Position.
Da die Position nicht in allen Fällen bekannt ist, kommt die ERSETZEN-Funktion häufig in Kombination mit SUCHEN bzw. FINDEN zum Einsatz.
Die folgenden Beispiele sollen das etwas verdeutlichen:
ERSETZEN und WECHSELN im Einsatz
Ich hoffe, du konntest aus dem heutigen Artikel wieder etwas für dich mitnehmen. Wenn du weitere praktische Anwendungsmöglichkeiten für diese Funktionen hast, dann lasse es doch die anderen Leser unten in einem Kommentar wissen.
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Hallo Herr Weiß,
ich würde gern im Bereich Kommentare „Autor“ oder „Anwender“ gegen meinen Namen ersetzen.
Mit Suchen /Ersetzten kann ich zwar die gesamte Mappe durchstöbern, habe aber keine Treffer im Kommentarfeld.
Was kann ich tun?
Komisch ist auch, dass beim Setzen eines neuen Kommentares mein Name teilweise korrekt angezeigt wird.
Beim späteren Öffnen aber nicht mehr.
Ich habe zu diesem Thema leider nur Makros gefunden. Damit kenne ich mich aber gar nicht aus.
Zweite Frage:
Ich würde gern maximal Werte in einer Tabelle innerhalb einer Zeile markieren. Die Daten stehen aber an unterschiedlichen Stellen innerhalb einer Zeile. Also nicht schön nebeneinander. zB. in H8, Q8, AB8, AH8 oder AR8
Ich wollte die betreffenden Spalten markieren und dann innerhalb der Zeile den jeweiligen Max / Min in rot / grün anzeigen lassen.
Können Sie mir hierzu auch einen Tipp geben?
Vielen vielen Dank
Mit freundlichen Grßen auch Erfurt
Solvig Nagel
Hallo Herr Nagel,
für das Suchen/Ersetzen von Namen in Kommentaren habe ich leider auch keine Lösung. Vielleicht kann hier ein anderer Leser weiterhelfen.
Ihre zweite Frage lässt sich mit Hilfe der bedingten Formatierung lösen.
Dazu markieren Sie die jeweilige Zeile und legen zwei Formatierungsregeln an. Für die erste wählen Sie als Regeltyp „Nur obere oder untere Werte formatieren“. Dann geben Sie als Regel „Untere“ und den Wert 1 an und legen noch die gewünschte Formatierung fest (z.B. grüner Hintergrund). Danach legen Sie eine zweite Regel an und geben „Obere“ und ebenfalls den Wert 1 an und legen als Formatierung eine andere Farbe fest.
Fertig.
Schöne Grüße,
Martin
Frage: Ich durchsuche Text und möchte wenn Wort A aber nicht Wort B enthalten ist die Ausgabe ABCD haben. Ich bekomme es nicht hin. Das Problem liegt darin, dass man kein „nein“ zurückbekommt, wenn das Wort B nicht enthalten ist, sondern nur die Fehlermeldung #WERT. Was kann ich tun???
Hallo Anne,
ohne etwas mehr Informationen ist das jetzt ein bisschen schwierig…
Wie sieht Deine bisherige Formel aus?
Wie ist der Text aufgebaut, der zu durchsuchen ist: Steht alles in einer einzelnen Zelle oder verteilt sich der Text auf mehrere Zellen?
Wie lang ist der Text ungefähr?
Schöne Grüße,
Martin
Hallo Martin,
beim Lesen dieser „FINDEN“ Funktion fiel mir ein Schreibfehler auf. Beim 2. Bulletpoint fehlt ein Buchstabe:
„Text ist die Zeichnkette ….“
Ansonsten finde ich Deinen Blog echt klasse. Sehr gute und übersichtliche Erläuterungen.
Viele Grüße
Peter
Hallo Peter,
danke für das nette Feedback und den Hinweis, der Fehler ist jetzt korrigiert.
Schöne Grüße,
Martin
Hallo Herr Weiß,
ich beschäftige mich gerade mit dem Unterschied der Funktionen WECHSELN und ERSETZEN. Rein von der Funktion her betrachtet habe ich das recht gut verstanden. Ersetzen zielt auf die Position ab, Wechseln auf einen bestimmten Text. Aber nach wie vor leuchtet mir nicht ein in welchem Zusammenhang ERSETZEN die bessere Wahl sein sollte. Da ich auch bei ERSETZEN zwingend den zu ergänzenden Text (Inhalt) und ebenso den neuen Text, angeben muss, kann ich doch in JEDEM Fall auch die WECHSEL Funktion verwenden. In den 5 Beispielen die sie zeigen ist dies auf jeden Fall so. Z.B Hans, Dampf zu Hans Dampf geht ja auch mit =WECHSELN(B14;“;“;““) usw..
Habe ich etwas übersehen, oder verstehe ich es vielleicht doch noch nicht ganz. Gibt es noch ein eindeutigeres Beispiel, das NUR mit ERSETZEN zu lösen ist.
Vielen Dank schon mal.
Herzliche Grüße
Marcus Holst
Hallo Herr Holst,
bitte schauen sie einmal hier:
https://www.pctipp.ch/praxis/office/excel-ersetzen-oder-wechseln-1860137.html
Ich hoffe, Ihnen ein wenig geholfen zu haben.
Beste Grüße
KD
Hallo Herr Holst,
der Leser KD hat ja schon einen Link mit weiteren Erklärungen genannt, ich möchte trotzdem auch noch kurz antworten. Ja, sehr viele Probleme lassen sich auch mit WECHSELN lösen. ERSETZEN wird jedoch dann notwendig, wenn Sie einen Text mit fester Länge aber variablen Inhalten durch einen anderen Text ersetzen wollen.
Beispiel (zugegeben, kein besonders intelligentes Beispiel):
A1: D-80123 München
A2: D-80124 München
A3: D-80125 München
A4: D-80126 München
Die PLZ soll bei allen auf 80339 geändert werden. Und dafür würde man eben ERSETZEN brauchen.
=ERSETZEN(A1;3;5;“80339″)
Schöne Grüße,
Martin