So filtern Sie Zellen, die wiederholte Textzeichenfolgen (Wörter) enthalten

ein Freund von mirIch spreche oft mit ihm über einige der realen Probleme, mit denen er bei der Arbeit mit Daten in Excel konfrontiert ist.

Oft übersetze ich seine Abfrage in ein Excel-Tutorial auf dieser Seite, weil es auch für meine anderen Leser hilfreich sein könnte.

Dies ist auch so ein Tutorial.

Mein Freund hat mich letzte Woche mit folgenden Fragen angerufen:

Ich habe Adressdaten in einer Spalte in Excel und möchte Zellen identifizieren/filtern, die sich wiederholende Textzeichenfolgen (Wörter) in der Adresse enthalten.

Hier ist ein ähnlicher Datensatz, in dem er Zellen (Zellen mit roten Pfeilen) filtern möchte, die sich wiederholende Textzeichenfolgen enthalten:

Was dies jetzt schwierig macht, ist, dass die Daten nicht konsistent sind.Da es sich um eine Zusammenstellung von Datensätzen handelt, die manuell von Vertriebsmitarbeitern erstellt wurden, kann es zu Abweichungen in den Datensätzen kommen.

Denk darüber nach:

• Jede Textzeichenfolge kann in diesem Datensatz wiederholt werden.Beispielsweise kann es sich um einen Regionsnamen oder einen Städtenamen oder beides handeln.
• Die Wörter werden durch Leerzeichen getrennt, und es besteht keine Übereinstimmung darin, ob der Name der Stadt nach sechs oder acht Zeichen steht.
• Es gibt Tausende solcher Datensätze und es müssen diejenigen gefiltert werden, die sich wiederholende Textzeichenfolgen enthalten.

Nachdem ich viele Optionen wie Text in Spalten und Formeln in Betracht gezogen hatte, entschied ich mich schließlich dafür, VBA zu verwenden, um die Arbeit zu erledigen.

Also habe ich eine benutzerdefinierte VBA-Funktion ('IdDuplicate') erstellt, um diese Zellen zu analysieren und mir TRUE zu geben, wenn es doppelte Wörter in der Textzeichenfolge gibt, und FALSE, wenn es keine Duplikate gibt (wie unten gezeigt):

Diese benutzerdefinierte Funktion analysiert jedes Wort in der Textzeichenfolge und prüft, wie oft es im Text vorkommt.Gibt TRUE zurück, wenn die Anzahl größer als 1 ist;Sonst FALSE zurückgeben.

Außerdem wurde es erstellt, um nur Wörter mit mehr als drei Zeichen zu zählen.

Sobald ich die WAHR/FALSCH-Daten habe, kann ich leicht alle Datensätze filtern, die WAHR sind.

Lassen Sie mich Ihnen nun zeigen, wie Sie dies in Excel tun.

VBA-Code für benutzerdefinierte Funktion

Dies geschieht durch Erstellen einer benutzerdefinierten Funktion in VBA.Diese Funktion kann dann wie jede andere Tabellenfunktion in Excel verwendet werden.

Hier ist sein VBA-Code:

Function IdDuplicates(rng As Range) As String Dim StringtoAnalyze As Variant Dim i As Integer Dim j As Integer Const minWordLen As Integer = 4 StringtoAnalyze = Split(UCase(rng.Value), " ") For i = UBound(StringtoAnalyze) To 0 Step -1 If Len(StringtoAnalyze(i)) < minWordLen Then GoTo SkipA For j = 0 To i - 1 If StringtoAnalyze(j) = StringtoAnalyze(i) Then IdDuplicates = "TRUE" GoTo SkipB End If Next j SkipA: Next i IdDuplicates = "FALSE" SkipB: Funktion beenden

So verwenden Sie diesen VBA-Code

Nachdem Sie nun den VBA-Code haben, müssen Sie ihn in das Backend von Excel einfügen, damit er als normale Arbeitsblattfunktion funktionieren kann.

Hier sind die Schritte, um den VBA-Code in das Backend einzufügen:

1. Gehen Sie zum Entwickler-Tab.
2. Klicken Sie auf Visual Basic (Sie können auch die Tastenkombination ALT+F11 verwenden)
3. Klicken Sie im offenen VB-Editor-Backend mit der rechten Maustaste auf ein beliebiges Arbeitsmappenobjekt.
4. Gehen Sie auf „Einfügen“ und klicken Sie auf „Modul“.Dadurch wird das Modulobjekt der Arbeitsmappe eingefügt.
5. Kopieren Sie im Modulcodefenster den oben genannten VBA-Code und fügen Sie ihn ein.

Sobald Sie den VBA-Code im Backend haben, können Sie die Funktion „IdDuplicates“ wie jede andere reguläre Arbeitsblattfunktion verwenden.

Diese Funktion benötigt einen Parameter, den Zellbezug der Zelle, in der sich der Text befindet.

Das Ergebnis dieser Funktion ist TRUE (wenn es doppelte Wörter gibt) oder FALSE (wenn es keine Duplikate gibt).Sobald Sie diese WAHR/FALSCH-Liste haben, können Sie diejenigen mit WAHR filtern, um alle Zellen zu erhalten, die sich wiederholende Textzeichenfolgen enthalten.

Hinweis: Der von mir erstellte Code dient nur zur Berücksichtigung von Wörtern, die länger als drei Zeichen sind.Dadurch wird sichergestellt, dass Wörter in der Textzeichenfolge, die 1, 2 oder 3 Zeichen lang sind (z. B. 12 A, KGM oder LDA), beim Zählen von Duplikaten ignoriert werden.Sie können dies bei Bedarf einfach im Code ändern.

Diese Funktion ist nur in der Arbeitsmappe verfügbar, in die Sie den Code in das Modul kopiert haben.Wenn dieser Code auch in anderen Arbeitsmappen verfügbar sein soll, müssen Sie diesen Code kopieren und in diese Arbeitsmappen einfügen.Alternativ können Sie ein Add-In erstellen (durch Aktivieren wird die Funktion in allen Arbeitsmappen auf Ihrem System verfügbar).

Denken Sie auch daran, diese Arbeitsmappe mit der Erweiterung .xlsm zu speichern (da sie Makrocode enthält).