So identifizieren Sie Fälle mit zwei Namen in einer Liste, die bis zu vier Namen enthalten kann

Question

Diese Frage ist ein Ableger einer früheren Frage:Excel-Formel – Formel zur Namenstrennung für Personen mit mehrteiligen Nachnamen:.

Kontext

Ich habe eine Liste mit über 20.000 Kontakten, in der es nur eine Spalte „Vollständiger Name“ gibt. Ich muss zwei Spalten für den Vornamen und den Nachnamen erstellen. Allerdings passen nicht alle Namen in dieses einfache Muster. Die Liste enthält Namen wie:

David Batte
Guy-Mael Jacobe de Naurois
Jean-Marc Lioutier
Thibaud Le Seguillon
Mrs. Manuela Junghaehnel
Hussain Mohammed Dipu Kabir

Als Ergebnis der früheren Frage gehe ich das Problem an, indem ich Namenssammlungen herausfiltere, die zu bestimmten Mustern passen, auf die ich entsprechende Analyseregeln anwenden kann. Eine relativ kleine Anzahl von Mustern ermöglicht es mir, die Analyse des größten Teils der Liste zu automatisieren, wodurch der Rest zu einer überschaubareren Aufgabe wird.

Diese Frage

Ein erheblicher Teil der vollständigen Namensliste enthält nur einen Vor- und Nachnamen, die leicht zu analysieren sind. Mein Ziel mit dieser Frage ist es, diese Namen mit zwei Namen zu identifizieren, damit sie vom Rest der Liste getrennt werden können.

Answer 1

Es gibt mehrere Möglichkeiten, Fälle zu identifizieren, in denen Namen in einer Liste nur zwei Komponentennamen enthalten, z. B. Vor- und Nachname. Diese Bedingung wird durch die Leerzeichen definiert, die die Komponentennamen trennen.

Methode 1

Diese Frage ist ein Ableger einer früheren (Excel-Formel – Formel zur Namenstrennung für Personen mit mehrteiligen Nachnamen:), wo wir bereits in Kommentaren die Identifizierung von Fällen mit einem einzelnen Namen besprochen haben, wie z. B. nur Vor- oder Nachname. Wenn Sie diese Namen bereits aus der Liste entfernt haben, sodass jeder Eintrag garantiert mindestens zwei Namen enthält, können Sie hier eine ähnliche Technik verwenden und darauf aufbauen.

Dieser Ansatz sucht nach einem Leerzeichen und dann von dort ausgehend nach einem weiteren Leerzeichen. Wenn ein zweites Leerzeichen gefunden wird, hat der Eintrag mehr als zwei Komponentennamen. Wenn sich der Eintrag in A1 befindet, lautet die Grundformel:

=FIND(" ",TRIM(A1),FIND(" ",TRIM(A1))+1)

Das Ergebnis ist entweder eine Zahl (Position des nächsten Leerzeichens) oder ein #VALUE!-Fehler, wenn es keinen gibt. Der #VALUE!-Fehler ist leicht zu erkennen und identifiziert Einträge mit zwei Namen. Beachten Sie, dass er auch Einträge mit einem Namen identifiziert, weshalb diese Methode nur funktioniert, wenn Sie diese bereits entfernt haben.

Sie können dies optisch ansprechender gestalten, indem Sie das Ergebnis auf den Fehlerzustand testen und dann entweder eine Textnachricht für die Fälle mit zwei Namen oder ein Leerzeichen anzeigen. Wenn Sie die obige Formel in einen WENN-Test einbinden, erhalten Sie eine Formel wie diese:

=IF(ISERROR(FIND(" ",TRIM(A1),FIND(" ",TRIM(A1))+1)),"Just 2","")

Methode 2

Ein anderer Ansatz besteht darin, die Leerzeichen zu zählen. Sie könnten diesen Ansatz tatsächlich verwenden, um eine bestimmte Anzahl von Leerzeichen zu finden. Wenn Sie also einfach die Zielanzahl ändern, können Sie auch die Einträge mit einem Namen finden. Dieser Ansatz zählt Leerzeichen, indem er alle Leerzeichen entfernt und beobachtet, um wie viele Zeichen die Zeichenfolge schrumpft. Der Längenvergleich ergibt die Anzahl der Leerzeichen. Sie schließen dies mit einem IF-Test ein, um mit der resultierenden Zahl zu tun, was Sie möchten. Die Formel zum Markieren von Einträgen mit zwei Namen (d. h. mit einem Leerzeichen) würde folgendermaßen aussehen:

=IF(LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))=1,"Just 2","")

Answer 2

Ich werde eine einfachere Methode verwenden, diemöglicherweise nicht optimalfür 20000 Zeilen.

• Teilen Sie die Namen durch Leerzeichen mit Daten -> Text in Spalten
• Die Namen werden auf die vier Spalten A, B, C und D aufgeteilt
• Klicken Sie auf Filter und filtern Sie nach blanksin Spalte C.
• In A und B werden nur die beiden Wortnamen angezeigt. Kopieren Sie sie rüber.
• Sie können die Namen verbinden mit=TEXTJOIN(" ",TRUE,A1:B1)

Answer 3

Ich möchte zwei mögliche Methoden vorschlagen: die erste ist Nicht-VBA und die andere ist VBA.

Methode 1:

Wie es funktioniert:

• Geben Sie diese Formel in die Zelle ein H2, um Hilfswerte zu erstellen (Teile von Namen identifizieren) und füllen Sie es aus.

=LEN(G2)-LEN(SUBSTITUTE(G2," ",""))

• Schreiben Sie Kriterien 2in die Zelle H1.

• Geben Sie diese Array-Formel in Zelle ein I2und beenden Sie mitStrg+Umschalt+Eingabeund fülle es aus.

  {=IFERROR(INDEX($G$2:$G$14,SMALL(IF(H$1=$H$2:$H$14,ROW($G$2:$G$14)- MIN(ROW($G$2:$G$14))+1,""), ROW()-1)),"")}

Achtung

• Durch Ändern der Kriterien in der Zelle H1in 1oder 3können Sie eine Liste mit zwei oder mehr als drei Teilenamen erhalten.

---

---

Methode 2:

Da Sie mit mehr als 20.000 Zeilen arbeiten, ist VBA (Makro) die schnellste Methode, um die Aufgabe zu erledigen.

Wie es funktioniert:

• RechtsklickRegisterkarte „Tabelle“ und im eingeblendeten Menü aufCode anzeigenum einen VB-Editor zu erhalten.

• Kopieren Einfügendiesen Code als Standardmodul und schließlichLAUFENdas Makro.

  Sub DeleteMorethan2()
  
  Dim r As Integer, c As Integer, space_count As Integer
  
  For r = 2 To 15
  space_count = 0
  
  For c = 1 To Len(Range("A" & r))
  
     If Mid(Range("A" & r), c, 1) = " " Then space_count = space_count + 1
     If space_count > 1 Then Range("A" & r).ClearContents
  
    Next
  Next
  
  End Sub
  

---

Du bekommst das:

Achtung

• Dieser Code wird bearbeitet Rows 2 to 15und durch Bearbeiten dieses Teils For r = 2 To 15können Sie weitere Zeilen einfügen.
• Die verwendete Spalte ist A, die ebenfalls bearbeitet werden kann.

Darüber hinaus möchte ich eine Array-Formel vorschlagen, um die Namensliste durch Entfernen leerer Zeilen neu zu erstellen.

• Geben Sie diese Array-Formel in Zelle ein B2und beenden Sie mitStrg+Umschalt+Eingabeund fülle es aus.

  {=IFERROR(INDEX(A$2:A$14,SMALL(IF(A$2:A$14<>"",ROW(A$2:A$14)-ROW(A$2)+1),ROWS(B$2:B2))),"")}

Passen Sie die Zellbezüge in der Formel nach Bedarf an.