Frage von Kanumir, 86

Formel für Excel um Zelleninhalt mit bestimmten Suchwort auszugeben?

In einer Excel Tabelle stehen in einer Spalte über 16.000 Zitate. Am Ende jedes Zitates steht eine 1-5 stellige Nummer in Klammern, z.B. (1234) Jetzt möchte ich: 1. In einer neuen Spalte alle Zitate ausgeben, die ein bestimmtes Suchwort enthalten (um zum Beispiel diese Treffer nach weiteren Kriterien zu durchsuchen oder um sie in ein Word Dokument zu kopieren) 2. In einer weiteren Spalte nur die Nummern der Zitate anzeigen, die ein bestimmtes Suchwort enthalten. Zum Beispiel: "happy", Ergebnis: 2, 445, 883, 12345, usw. Für jemand der sich mit Excel auskennt wahrscheinlich sehr einfach, für mich leider nicht :-) Vielen Dank schon einmal für die Hilfe!

Expertenantwort
von Iamiam, Community-Experte für Excel, 52

mit

{=KKLEINSTE(WENN(NICHT(ISTFEHLER(SUCHEN("bcd";A:A;1)))*ZEILE(A:A);ZEILE(A:A));ZEILE(A1))}

in zB B1 (Zeile1 ist wichtig, B nicht!) runtergezogen erhältst Du die Zeilen, in denen das Teilzitat zu finden ist (Suchen berücksichtigt nicht Groß/klein, ich danke das ist hier sinnvoll!)

Am Ende, wenn kein weiteres Zitat mehr zutrifft, kommt #Zahl!

baust Du ein

=indirekt("A"&KKleinste(.........))

drumrum, wird das gefundene Zitat selbst ausgegeben zum weitersuchen. Kannst das ganze aber auch in ein anderes Blatt B1 ff versetzen (ausschneiden, nicht kopieren!, dort einfügen)

  • Achtung: Dies ist eine Matrixformel! Die geschweiften {Klammern} NICHT miteingeben, sondern die Formel anstatt mit einfachem Enter mit der Kombination (gleichzeitig) Strg+Shift+Enter abschließen (nach jeder Neubearbeitung wieder !). Die {Klammern} entstehen dann automatisch.
  •  Schreibtechnisch nimmst Du am besten die rechte Strg- und Umschalttaste, dann gehts mit einer Hand und lässt sich auch leichter merken!  - - -
  • Noch ein Tipp: Wenn Du in der Bearbeitungszeile einen selbständigen Formelteil markierst und F9 drückst, erhältst Du ein(e) Ergebnis{liste}. Allerdings dann die Zelle mit Esc verlassen (oder rückgängig machen), Enter würde das gezeigte Ergebnis festschreiben! Vorläufig kurze Bereiche festlegen! (mindestens 2 Zellen markieren, Strg+h, ... ersetzen durch ...) --

Kannst das aber auch mit dem Autofilter -benutzerdefiniert-enthält- filtern.

Zum letzte Zahl auslesen muss ich mir erst noch was überlegen bzw suchen.

Kommentar von Iamiam ,

Ergänzung: hab oben nach bcd gesucht, deshalb "bcd". bcd ist natürlich Dein Text!

wenn das erste gefundene Zitat samt Nummer in C1 steht, liefert

=RECHTS(C1;LOG(
WENN(ISTZAHL(LINKS(RECHTS(C1;2);1)*1);2;1)
*WENN(ISTZAHL(LINKS(RECHTS(C1;3);1)*1);2;1)
*WENN(ISTZAHL(LINKS(RECHTS(C1;4);1)*1);2;1)
*WENN(ISTZAHL(LINKS(RECHTS(C1;5);1)*1);2;1)
*WENN(ISTZAHL(LINKS(RECHTS(C1;6);1)*1);2;1);2)+2)

die Zitatenummer in Klammern (ohne Klammern würde es komplizierter, zumindest erheblich länger. Max 5 Stellen, wie angegeben, für zusätzliche Stellen einfach je eine Zeile mehr.

Die Formel wird auch in hoher Menge von xl problemlos verarbeitet.

Expertenantwort
von Ninombre, Community-Experte für Excel, 53

Wenn Du in eine Spalte hinter die Zitate einfügst:
=WENNFEHLER(SUCHEN("happy";A1);"")

Liefert das für alle Zitate die das Wort "happy" enthalten eine Zahl, sonst ein "leer". Annahme hier, dass die Zitate in Spalte A stehen.

Danach kannst Du entsprechend filtern. Das ist einfacher, als über Formeln in Zitate direkt auflisten zu lassen

Die Nummer auslesen ist nicht ganz trivial, wenn nicht sichergestellt ist, dass sonst keine KIammern im Zitat enthalten ist. Ist das so?

Kommentar von Kanumir ,

Hi,
das ging ja flott :-)

Richtig, die Zitate stehen in Spalte A. Die Formel habe ich in Spalte B eingegeben und mit Doppelklick nach unten kopiert. Dann habe ich in der Spalte B alle leeren Felder herausgefiltert, sodass in B nun verschiedene Zahlen stehen. Was nun?

Leider gibt es bei den vielen Zitaten manche die innerhalb des Zitats eine Klammer enthalten.
Vielleicht hilft es, dass die Nummer immer 1-5 stellig ist und am Ende des Zitats steht. Das wären dann mit den Klammern immer die letzten 3 bis 7 Zeichen. Die Klammern kann man ja vielleicht in einem weiteren Schritt löschen. Immer erstes und letztes Zeichen einer Zelle löschen.

Reicht diese Beschreibung?

Kommentar von Ninombre ,

Die Klammern wird man sicherlich auch noch los, auf die Schnelle aber mal so (kann jetzt leider nicht mehr weiter an der Formel feilen)

=TEIL(RECHTS(A1;7);FINDEN("(";RECHTS(A1;7));FINDEN(")";RECHTS(A1;7)))
Kommentar von Kanumir ,

Hallo Ninombre,

das klappt wunderbar und hilft mir sehr weiter!! Folgende Fragen sind noch entstanden :-)

1. Wie kann ich nun die Klammern (erstes und letztes Zeichen) löschen? (Gleich mit in die Formel einbauen?)

2. Wenn ich die Zahlen in eine neue Excel Tabelle kopiere, dann sind sie als Text formatiert. Wenn ich sie als Zahl umwandele, setzt Excel immer ein Minuszeichen davor. Wegen der Klammer?

3. Kann ich mit der ersten Formel auch Suchworte mit Platzhalter suchen, zum Beispiel "do*" - Ergebnis alle Worte die mit "do" beginnen?
(Groß- und Kleinschreibung wird bei der Suche mit deinem Verfahren glücklicherweise nicht beachtet, sonst würde das Ganze noch komplizierter)

Vielen Dank schon einmal!!!

Wer will ein paar Zitate? Einfach Suchwort als Kommentar ;-)


Kommentar von Ninombre ,

mit den Klammern sollte so gehen, wenn man die bisherigen Rahmenbedingungen beibehält (also max. 5stellige Zahl + 2 Klammern und immer am Schluss des Textes):

=TEIL(RECHTS(A1;7);SUCHEN("(";RECHTS(A1;7))+1;6-SUCHEN("(";RECHTS(A1;7)))

Man kann auch die Funktion =suchen mit "do" einsetzen, allerdings ist dann die Anzahl der Zeichen anders abzufragen, das Wort ist mit dem nächsten Leerzeichen zu Ende. Das * ist hier nicht notwendig

=TEIL(A1;FINDEN("do";A1);FINDEN(" ";A1;FINDEN("do";A1))-FINDEN("do";A1))

Irgendwie hätte ich noch die Hoffnung, dass man das kürzer formulieren kann, allerdings fällt mir da im Augenblick nichts ein.

Keine passende Antwort gefunden?

Fragen Sie die Community