Frage von 2001HAL9000, 97

Excel: Suchen im Text?

Hallo Alle miteinander!

Ich habe ein Problem...:

Ich habe einen Text (C2) und eine Liste von Suchwörtern (Q:R). Ich will in B2 jetzt ergründen ob eins der Suchwörter im Text C2 vorkommt.

Wenn ich : =SUCHEN(Q:R;B2)

verwende geht das nicht, weil dann alle suchwörter im Text vorkommen müssten. Hat jemand eine Idee wie ich es schaffe, dass schon ein Treffer ausreicht?

Antwort
von Rubezahl2000, 19

In Hilfsspalte S könntest du für jeden Wert in Spalte Q und R prüfen, ob der im Text in Zelle C2 enthalten ist.
Z.B. in Zelle S1 könntest du prüfen, ob der Inhalt von Q1 oder R1 in C2 enthalten ist mit der Formel:
=ZÄHLENWENN($C$2;"*"&Q1&"*")+ZÄHLENWENN($C$2;"*"&R1&"*")
Diese Formel dann runterkopieren, so weit wie Werte in Spalte Q oder R enthalten sind.
Wenn die Formel als Ergebnis 0 liefert, dann ist in der Zeile der entspr. Inhalt aus Spalte Q und R NICHT in C2 enthalten,
Wenn die Formel als Ergebnis eine Zahl >0 liefert, dann ist in der Zeile der entspr. Inhalt aus Spalte Q bzw. R  in C2 enthalten.

In B2 kannst du dann die Bedingung verwenden, ob die Summe in Spalte S >0 ist.
=WENN(SUMME(S:S)>0;C2;"Nicht vorhanden")
Wenn mindestens 1 Wert aus den Spalten Q oder R im Text von Zelle C2 vorhanden ist, wird jetzt in B2 der Inhalt von C2 eingetragen sonst kommt der Text "Nicht vorhanden"

Funktioniert's bei dir?

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

=Suchen funktioniert nur in einer Zelle, nicht in einem Bereich

Wieviele Suchbegriffe sind denn zu erwarten in B?

Notfalls zerlegt man die anhand der Leerzeichen (Du wolltest ja Wörter suchen).

=TEIL(B2;1;SUCHEN(" ";B2)-1) liefert das Wort bis zum ersten Leerzeichen.
Das könnte man in Vergleich als Suchkriterium einfügen
=VERGLEICH(TEIL(B2;1;SUCHEN(" ";B2)-1);D:D)

=TEIL(B2;LÄNGE(B6)+2;SUCHEN(" ";B2;LÄNGE(B6)+1)) liefert das zweite Wort, was sich dann auch in den Vergleich übernehmen lässt.

Die Lösung braucht auch Hilfsspalte (eine je Wort), dafür kannst Du die aber auch für eine Liste von Suchbegriffen in Spalte B nutzen.

Ggf. hilft bei dieser Art der Suche aber auch nur ein Makro.

Kommentar von 2001HAL9000 ,

Danke, Endlich mal wer mit Ahnung. :)

Allerdings hast Du mich glaube ich falschrum verstanden.
Ich habe in Spalte Q und R in Summe 440 Suchbegriffe und will wissen ob mindestens einer davon in dem Text C2 Vorkommt.

Ist das nämlich der Fall übernehme ich den Text C2 in B2 Unverändert. Wenn nicht greift eine Andere Formel...

Wenn das dann klappt mache ich das dann für jede Zeile so weiter :

B3 C3, B4 C4... B20000 C20000.

Kommentar von Ninombre ,

Deine Suchvariante ist nicht ganz passend zur Logik die Excel mitbringt, daher würde ich es tatsächlich eher per Makro lösen. Normalerweise geht man von einer Zelle aus und ermittelt dazu Suchtreffer - Du willst es hier aber genau anders herum. Mit Formeln wird das dann kompliziert.

Das Makro schreibt Dir in Spalte B ein X wenn ein Teil des Inhaltes von Spalte C in R oder S enthalten ist. Genauer gesagt andersherum: Für jeden Eintrag in Spalte R bzw. S wird geprüft, ob dieser in Spalte C enthalten ist. Wenn ja, wird die Zeile in B markiert.

Sub treffer()
For i = 18 To 19
For j = 1 To Cells(Rows.Count, i).End(xlUp).Row
For k = 1 To Cells(Rows.Count, 3).End(xlUp).Row
If InStr(1, Cells(k, 3).Value, Cells(j, i).Value) > 0 And Cells(k, 3).Value <> "" Then Cells(k, 2).Value = "X"
Next k
Next j
Next i
End Sub
Kommentar von Iamiam ,

Das Makro wird zwar sehr lange brauchen bei 20000 zu durchsuchenden Zellen, die jeweils 400+mal durchsucht werden müssen, aber ich komme mit der schnelleren Variante find / find next
 nicht zurecht, weshalb ich jetzt ebenfalls diese Methode empfehle.

Am besten über Nacht laufen lassen!

Vielleicht wäre es auch nicht aufwändiger gewesen, anstatt X den Suchbegriff einzutragen/anzuhängen

Kommentar von Iamiam ,

ich mach das mal als erweiterte 5.Zeile dieses Makros:

If InStr(1, Cells(k, 3).Value, Cells(j, i).Value) > 0 And Cells(k, 3).Value <> "" Then Cells(k, 2).Value = Cells(k, 2).Value & ", " & Cells(j, i).Value


Damit weiß man dann gleich, WELCHE(R) Suchbegriff(e) da zu finden ist / sind !

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

ich krieg das nur mit einer Hilfsspalte hin (vllt schafft das ja noch jemand in EINER Formel):

Zuerst musst Du die beiden Listen (hab sie bei mir in Q2 und R2 beginnen lassen und nur kurz gemacht: letzte Zeile ist bei mir 6) auf gleiche Länge bringen, indem Du an die Kürzere niemals vorkommende Zeichenfolgen anhängst, zB sowas exotisches wie: ƷӜ̵̨̄Ƹ̵̡

Dann schreibst Du in die letzte gefüllte Zelle S6 (S7 muss leer sein) diese Formel:

=WENN(NICHT(ISTFEHLER(SUCHEN(Q6;C$2)));Q6&", ";"") &WENN(NICHT(ISTFEHLER(SUCHEN(R6;C$2)));R6&", ";"")&S7

Diese Formel ziehst Du nach oben (also auf alle Zellen oberhalb bis S2 kopieren.)

In S2 stehen nun ALLE enthaltenen Begriffe, getrennt durch Komma&Leerzeichen, in der Reihenfolge von oben nach unten.

Das kannst du nun in B2 mit =S2 übernehmen oder -wenn unterhalb B2 alles frei ist- Du ziehst den gesamten Formelblock samt dem leeren S7 nach B2.

Musst Du die Liste der Suchbegriffe erweitern, dann einfach  welche unten anhängen. Danach kannst Du die Formel in S einfach nach unten kopieren. Achte dabei aber darauf, dass Du keine Zelle verschiebst, sondern nur kopierst (sonst könnten sich Bezüge ändern! (und wieder Leerzellen exotisch füllen!)

Viel Erfolg!

Noch was: ich hab SUCHEN verwendet, das unterscheidet Nicht zwischen Groß/klein, im Ggs zu FINDEN, das unterscheidet. Kann beides sinnvoll sein.

Da bekanntlich IBM auch in Schreibmaschine vorkommt, kann es zu unerwünschten Findungen kommen. Willst Du nur Einzelwörter suchen, hilft folgende Modifikation:  
=WENN(NICHT(ISTFEHLER(SUCHEN(" "&Q6&" ";" "&C$2&" ")));Q6&", ";"") ...

Das findet aber Arbeit nicht mehr in Zeitarbeit!

Kommentar von Iamiam ,

Noch eine Warnung zu Letzterem: Bindestriche oder Satz-/Anführungs-etc-Zeichen sind natürlich keine Leerzeichen, also insgesamt kann das Suchen mit " "&...&" " eher neue Probleme schaffen als alte lösen!

Aber Du kannst mit beiden Modifikationen in zwei unterschiedlichen Spalten arbeiten, zum Vergleich. Am besten übereinander darstellen, das zeigt die Unterschiede deutlicher!

Kommentar von Iamiam ,

20000x suchen? Ich denke, da lohnt ein Makro! Auch das wird lange laufen, aber ohne Dein Zutun. Ich überleg mir was, bis heut abend!

Kommentar von Iamiam ,

krieg das heute nicht mehr hin, komme mit find einfach noch nicht zurecht und die Hilfe dazu  ist recht mager. werde mich morgen und ggf am WE nochals damit auseinandersetzen. einstweilen aber noch eine Formel-(Not)-Lösung, die etwas schneller gehen sollte:

Stürze Deine beiden Begriffe-Spalten und hänge sie aneinander, also von zB F1:ca PZ1 (stürzen geht so: eine Spalte bis zum Ende der Einträge kopieren, eine Zelle rechts davon auswählen, Kontextmenü Inhalte einfügen..., da drin das 7. Icon (Symbol stürzen sollte selbsterklärend sein)

Das mit der 2. Spalte wiederholen, die noch vorhandene, nun waagrechte Auswahl ausschneiden, am Ende der ersten Liste anhängen. Die Begriffe sollen jetzt in Zeile 1 zB F1:PZ1 stehen.

Nun setzt Du nach F2 die Formel:

=NICHT(ISTFEHLER(SUCHEN(F$1;$A2)))*1

nach rechts kopieren bis PZ2. Übereinstimmende Begriffe weisen nun eine 1 auf, nicht übereinstimmende eine 0.

Jetzt setzt Du davor, nach E2 die Formel =SUMME(F2:I2)

Die Summe zeigt an, ob mehr als nur ein Begriff in einer Zelle gefunden wurde oder überhaupt keiner (0).

Nun in zB C2 (B2 und E2 bleiben leer für ggf Weiteres, könnten aber auch gelöscht werden) noch diese Formel:

=INDEX(F$1:PZ$1;1;VERGLEICH(1;F2:PZ2;0))


Fenster fixieren mit F2 als Schnittpunktzelle.

Die ganze Formelzeile runterkopieren
bis zum letzten, zu durchsuchenden Eintrag (also ca. Zeile 20000)

Es kommen so zwar 8-9 Mio Formeln zusammen, aber ein halbwegs guter Rechner (möglichst großer Arbeitsspeicher, die Geschwindigkeit ist nicht so wichtig) sollte das noch bewältigen -hoffe ich. Ggf in Abschnitten à 50 Zeilen und Formeln immer wieder zu Wert umsetzen!

Du kannst auch noch für ALLE Formeln ein Bendef Format setzen, welches die Null unauffällig macht, so dass sich die 1 besser abhebt:

Strg+1, Karte Zahlen, Bendef: Standard;[rot]-Standard;[Farbe7]°

Texte werden davon nicht beeinflusst.

bei Übereinstimmung von mehr als einem Begriff (Summe >1) muss manuell nach den weiteren gesucht werden, eine mögliche Matrixformel verbietet sich bei einer derart hohen Zahl von Formeln!

Keine passende Antwort gefunden?

Fragen Sie die Community

Weitere Fragen mit Antworten