Ich suche Formel für „Worte“ mit einem bestimmten Buchstaben Mustern zu suchen, wie INNSA1oder INMAA4oder INPTPB, wo das Wort mit beginnt INund endet mit 4, 1, 6, oder B. Die Wörter sind immer genau sechs Zeichen und die Buchstaben können entweder Groß- oder Kleinbuchstaben sein.
Eine Sammlung von Excel-Zellen enthält jeweils einen Satz, in dem ein oder mehrere solcher Wörter gefunden werden (immer mindestens eines). Zum Beispiel:
Das Wort (oder die Wörter, falls mehr als eins) kann sich an einer beliebigen Stelle im Satz befinden. Die Zielwörter sind immer separate Wörter und nicht Teil eines größeren Wortes.
Für jeden Satz (dh Zelle) möchte ich nach dem Vorhandensein eines Wortes suchen, das in das beschriebene Buchstabenmuster passt, und dieses Wort als Wert zurückgeben.
Dies ist nur ein Beispiel, das Sie an Ihr Schema anpassen können.
Bei Daten von A2 bis A26 geben Sie in B2 ein:
=IF(AND(LEFT(A2,2)="IN",OR(RIGHT(A2,1)="B",RIGHT(A2,1)="1",RIGHT(A2,1)="4",RIGHT(A2,1)="6")),1+MAX($B$1:B1),"") und nach unten kopieren:
Wie Sie sehen, wird jedes "gute" Datenelement mit einem einfachen sequenziellen Index gekennzeichnet.
Es gibt viele Möglichkeiten, die einzelnen Probleme zu lösen, aber die Kombination der Anforderungen weist auf einen einfachen und unkomplizierten Ansatz oder eine VBA-Lösung hin. Die Nicht-VBA-Lösung verwendet Hilfssäulen. Das Layout Ihrer Tabelle ist unbekannt, ebenso wie andere Faktoren, wie die Anzahl der Sätze und die mögliche Anzahl der Ergebnisse sowie die Art und Weise, in der Sie die Ergebnisse benötigen und verwenden möchten. Diese Antwort wird daher eher allgemein gehalten als eine spezifische Lösung.
Bei Verwendung von Funktionen wird es schwierig sein, den Satz nach möglicherweise mehr als einem Ziel zu durchsuchen. Ein einfacherer Ansatz besteht darin, den Satz in Wörter aufzuteilen und dann die Wörter zu überprüfen. Eine einfache Möglichkeit zum Analysieren der Sätze bietet der integrierte Assistent zum Konvertieren von Text in Spalten.
Wenn die Sätze in Spalte A sind, lassen Sie die nächsten Spalten leer, um Ihre Ergebnisse zu erhalten. Auf diese Weise befinden sich Ihre Ergebnisse immer an einem bekannten Ort und sind neben Ihren Daten sichtbar. Ich gehe von maximal zwei aus, sodass die Spalten B und C Ergebniswerte enthalten.
Stellen Sie die maximale Anzahl von Wörtern dar, die in einem Satz enthalten sein können, und reservieren Sie die Anzahl der nachfolgenden Spalten für Helfer-Spalten. Diese werden verwendet, um jedes Wort zu testen. Nehmen wir an, Sie lassen fünf Wörter zu. Die Spalten D: H enthalten Formeln, die auf die Spalten I: M zeigen.
Nehmen wir an, Zeile 2 ist Ihre erste Datenzeile, daher wird die erste Formel in D2 stehen und auf I2 zeigen. Die Formel wird der von Garys Student vorgeschlagenen ähneln:
=IF(AND(LEFT(I2,2)="IN",OR(RIGHT(I2,1)="B",RIGHT(I2,1)="1",RIGHT(I2,1)="4",RIGHT(I2,1)="6")),1,0) Eine alternative Formel wäre:
=IFERROR((LEFT(I2,2)="IN")*(SEARCH(RIGHT(I2,1)," 416b")>1),0) Das gibt Ihnen nur ein paar Ideen, wie Sie feststellen können, ob das Zielwort zum Muster passt. Die zweite Formel verwendet SEARCH (weil die Groß- und Kleinschreibung nicht beachtet wird), um zu sehen, ob sich das letzte Zeichen in Ihrer Liste befindet. Wenn Sie die ersten beiden Zeichen INund das letzte Zeichen testen, erhalten Sie jeweils Wahr oder Falsch, die von Excel jeweils als 1und behandelt 0werden. Wenn also beide Tests wahr sind, ist das Ergebnis 1anders 0.
Im SEARCH-Ausdruck begann ich mit einem Leerzeichen und testete auf ein Ergebnis> 1. Ich habe das getan, weil ich MS Office Online zum Testen verwendet habe und meint, dass das Zeichen ganz rechts einer leeren Zelle an Position 1 der Testzeichenfolge zu finden ist.
Wenn SEARCH keine Übereinstimmung findet, wird ein Fehler zurückgegeben. Durch das Umschließen der Formel in IFERROR wird sichergestellt, dass jedes Wort, das nicht übereinstimmt, zu Ergebnissen führt 0.
Also gibt eine der beiden Formeln zurück, 1ob das Wort zu Ihrem Muster passt oder 0nicht (oder wenn die Zielzelle leer ist).
Geben Sie eine der beiden Formeln in D2 ein, und ziehen oder kopieren Sie sie in Richtung H2 und in die letzte Zeile, die Sie benötigen.
Wählen Sie zum Füllen der Zielzellen alle Daten in Spalte A aus, kopieren Sie sie und fügen Sie sie in Spalte I ein (Text in Spalten überschreibt die Daten und beginnt in der Datenspalte). Wählen Sie die Daten in Spalte I aus . Klicken Sie auf der DataRegisterkarte in der Data ToolsGruppe auf Text to Columns. Sie erhalten einen Assistenten, der sich selbst erklärt.
Geben Sie an, dass die Daten begrenzt sind, und wählen Sie spaceals Trennzeichen aus. Sie erhalten eine Vorschau, wie Sie die Wörter analysieren möchten. Wenn Sie den Assistenten beendet haben, wird jedes Wort in eine aufeinanderfolgende Zelle in der Zeile eingefügt.
Ihre Formeln in D: H sollten 1in jeder Zeile mindestens eine anzeigen. Dies sind Ihre Wörter, und ihre Spalte befindet sich in derselben relativen Position wie das analysierte Wort.
Um den Wert des ersten Wortes in Spalte B zu erhalten, suchen Sie mit INDEX und MATCH nach dem ersten 1in der Zeile in D: H und rufen Sie den entsprechenden Wert aus derselben Zeile in I: M ab. Zum Beispiel wäre B2:
=INDEX(I2:M2,MATCH(1,D2:H2,0)) Dadurch wird der Wert aus der Zelle in I2: M2 abgerufen, der der exakten Übereinstimmung mit 1D2: H2 entspricht.
Um die nachfolgenden Werte für Spalte C (und darüber hinaus, falls mehr als zwei vorhanden sind) zu erhalten, gibt es eine Reihe von Lösungen, um die N-te Übereinstimmung zu finden 1. Hier ist ein Link zu einer Methode.