Wie führt man einen teilweisen Zeichenfolgenvergleich in Excel durch?

Question

Ich habe zwei Spalten in Excel. Beide Spalten enthalten eine Liste von Zeichenfolgen, die mit einem zufälligen Zeichen als Suffix versehen sind. Das Format des Präfixes besteht aus zwei Zeichenfolgen, an die ein Unterstrich angehängt wird, und das Suffix wird ebenfalls mit einem Unterstrich hinzugefügt.

dh

ABC_DEF_a => (prefix = "ABC_DEF", suffix = "a") HJDSGDJ_KJ1_a10 => (prefix = "HJDSGDJ_KJ1", suffix = "a10"

Wie vergleiche ich, ob das Präfix in Spalte B auch in Spalte A steht?

Bearbeiten: Ich weiß, dass ich das tun kann, indem ich die Zeichenfolge in drei Abschnitte aufteile, dann die ersten beiden Abschnitte in einer Spalte kombiniere und dann mit einem SVERWEIS überprüfe, ob die Spalte in der anderen ist. Aber ich suchte nach einer Einzeilerfunktion.

Answer 1

Ich gehe davon aus, dass Sie in den Zellen A1 und B1 Folgendes haben:

Und in Spalte C steht ein TRUE(VERDADEIRO) oder FALSE(FALSO), das zurückgibt, ob das Präfix gleich ist oder nicht. (Entschuldigung für das Bild auf Portugiesisch)

Die Formel, die ich mir ausgedacht habe, lautet:

=EXACT(LEFT(SUBSTITUTE(A1;"_";"\";2);SEARCH("\";SUBSTITUTE(A1;"_";"\";2))-1);LEFT(SUBSTITUTE(B1;"_";"\";2);SEARCH("\";SUBSTITUTE(B1;"_";"\";2))-1))

---

Erläuterung:

=EXACT(str1,str2)

Es vergleicht, ob die Zeichenfolgen str1 und str2 gleich sind oder nicht. Wir müssen das Präfix aus den Spalten A und B extrahieren. Dazu verwenden wir:

=LEFT(text, [num_chars])

Es extrahiert [num_chars]Zeichen aus dem String text. Um zu wissen, wie viele Zeichen wir extrahieren müssen, ersetzen wir den zweiten Unterstrich _durch ein Platzhalterzeichen \:

=SUBSTITUTE(text, old_text, new_text, [instance])
=SUBSTITUTE(A1;"_";"\";2)

Der Trick dabei ist das optionale Argument [instance]. Wir setzen es auf 2, um das zweite Vorkommen des Unterstrichs zu ersetzen.

=SEARCH("\";SUBSTITUTE(A1;"_";"\";2))-1)

Mit dieser Formel finden wir die Position des Dummy-Zeichens \. Wenn wir alles zusammenfügen, erhalten wir die obige Formel.

Wenn wir die Formel schrittweise aufschlüsseln, erhalten wir:

Answer 2

Ich verstehe Ihre Frage so, dass Sie für jede Zeichenfolge in Spalte B wissen möchten, ob ihr Präfix irgendwo in Spalte A vorkommt. Diese Antwort löst also dieses Problem.

Ich konnte es nicht in einer Zeile unterbringen (weil es überall in Spalte A suchen muss). Aber es erfordert nur eine zusätzliche Spalte.

Zunächst eine Formel zum Extrahieren des Präfixes aus einer beliebigen Zeichenfolge:

=LEFT(Column_A, FIND(Delimiter, Column_A, 1 + FIND(Delimiter, Column_A)) - 1)

• Delimiterist das Zeichen "_"
• Der innere FIND()Aufruf findet die Position des ersten Unterstrichs. Anschließend verwendet er diese als Ausgangspunkt für die Suche nach FINDder Position des zweiten Unterstrichs.
• Die LEFT()Funktion gibt eine Anzahl von Zeichen zurück, beginnend links von der Zeichenfolge. Wir subtrahieren also 1von der Position des zweiten Unterstrichs und erhalten das Präfix der Zeichenfolge.

Sie verwenden dies also, um eine Liste aller Präfixe in Spalte A zu erhalten. Anschließend überprüfen Sie mithilfe einer Array-Formel, ob jedes Präfix in Spalte B in der Liste der Präfixe in Spalte A vorhanden ist.

{=OR(LEFT(Column_B, FIND(Delimiter, Column_B, 1 + FIND(Delimiter, Column_B)) - 1)=Prefix_A)}

• Prefix_Aist die vollständige Liste der Präfixe in Spalte A.
• Dies ist die gleiche Formel zum Extrahieren von Präfixen, die in Spalte A angewendet wurde.
• Dies muss eine Array-Formel sein, da die Anweisung innerhalb der Funktion ein Array von und Werten OR()zurückgibtTRUEFALSE

Hier ist ein Screenshot der Tabelle, die ich zur Beantwortung dieser Frage erstellt habe:

Ich habe FormulaChop verwendet, um diese Beispielformeln zu generieren (vollständige Offenlegung: Ich habe FormulaChop geschrieben).Hierist ein Screenshot der FormulaChop-Ausgabe für die erste Formel.Hierist ein Link zu der Tabelle, die ich zur Beantwortung dieser Frage erstellt habe.