Wie entfernt man Anreden aus Namenszellen in Excel?
Im täglichen Büroalltag oder bei Datenaufbereitungsaufgaben stoßen Sie möglicherweise oft auf Excel-Listen, in denen der Name jeder Person mit einer Anrede wie Herr, Frau, Fräulein usw. beginnt. Für Zwecke wie die Vorbereitung von Marketingkampagnen, das Erstellen von Berichten oder einfach zur Standardisierung Ihres Datensatzes müssen Sie diese Anreden möglicherweise entfernen und nur die eigentlichen Namen behalten. Zum Beispiel könnte Herr John Smith in eine ganze Spalte von Daten in John Smith umgewandelt werden. Dies effizient in Excel zu erreichen kann viel manuelle Arbeit sparen, insbesondere bei der Bearbeitung großer Datensätze oder wenn das Format der Anreden nicht konsistent ist.
Anreden aus Namenszellen mit Formel entfernen
Anreden mit VBA-Makro im großen Stil entfernen
Anreden mit Excels Blitzvollauffüllfunktion entfernen
Anreden und Namen mit Formeln in separate Spalten aufteilen
Anreden aus Namenszellen mit Formel entfernen
Die Verwendung einer Excel-Formel ist eine einfache Methode, um Anreden aus Namenszellen zu entfernen. Diese Lösung ist am besten geeignet, wenn alle Ihre Namen demselben Muster folgen, wo die Anrede zuerst kommt, gefolgt von einem Leerzeichen und dann dem vollständigen Namen der Person. Diese Methode ist schnell, erfordert keine zusätzlichen Add-ons und funktioniert gut für kleinere bis mittlere Listen. Allerdings kann sie komplexere Variationen wie mehrteilige Anreden oder inkonsistente Formate möglicherweise nicht verarbeiten.
Um die Anrede aus einer Namenszelle zu entfernen, führen Sie die folgenden Schritte aus:
1. Geben Sie in eine leere Zelle neben Ihrer Namensliste (angenommen, Ihre Daten beginnen in Zelle A2) die folgende Formel ein:
=RIGHT(A2, LEN(A2) - FIND(" ",A2))Diese Formel findet das erste Leerzeichen in der Zelle und extrahiert den gesamten Text rechts davon, wodurch die Anrede effektiv entfernt wird.
2. Drücken Sie Enter, um die Formel zu bestätigen. Ziehen Sie als Nächstes den Ausfüllknauf (das kleine Quadrat in der unteren rechten Ecke der Zelle) nach unten, um diese Formel für alle Zellen in Ihrer Spalte zu füllen.
Tipps & Vorsichtsmaßnahmen: Wenn es Namen gibt, die keine Anrede enthalten (kein Leerzeichen), könnte die Formel einen Fehler zurückgeben. Überprüfen Sie Ihre Daten auf Inkonsistenzen, bevor Sie diese Methode anwenden.
Anreden mit VBA-Makro im großen Stil entfernen
Für große Datensätze oder in Fällen, in denen Anreden inkonsistente Formate haben oder unterschiedliche Längen aufweisen, ist die Verwendung eines VBA-Makros eine effektive Lösung. Mit VBA können Sie verschiedene Arten von Anreden auf einmal entfernen, was besonders nützlich für das Aufräumen großer Mailinglisten, HR-Datenbanken oder jedes Szenario ist, in dem manuelle Formeln nicht praktikabel sind.
Unten sehen Sie ein Beispiel eines VBA-Skripts, das eine Liste häufiger Anreden (wie Herr, Frau, Fräulein, Dr., Prof.) vom Anfang jedes Namens in einem ausgewählten Bereich entfernt. Sie können die Liste leicht erweitern, um zusätzliche Titel einzuschließen.
1. Klicken Sie auf Entwickler > Visual Basic, um den VBA-Editor zu öffnen. Gehen Sie im VBA-Editor zu Einfügen > Modul und fügen Sie den folgenden Code in das Modulfenster ein:
Sub RemoveSalutationBulk()
Dim WorkRng As Range
Dim Rng As Range
Dim cellValue As String
Dim arrSalutations As Variant
Dim i As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select the range to remove salutations from:", xTitleId, WorkRng.Address, Type:=8)
arrSalutations = Array("Mr. ", "Mr ", "Mrs. ", "Mrs ", "Ms. ", "Ms ", "Miss ", "Dr. ", "Dr ", "Prof. ", "Prof ")
For Each Rng In WorkRng
cellValue = Rng.Value
For i = LBound(arrSalutations) To UBound(arrSalutations)
If InStr(1, cellValue, arrSalutations(i), vbTextCompare) = 1 Then
cellValue = Mid(cellValue, Len(arrSalutations(i)) + 1)
Exit For
End If
Next i
Rng.Value = cellValue
Next Rng
End Sub2Drücken F5 oder klicken Sie auf 
Ausführen , um den Code auszuführen. Ein Dialogfeld erscheint, in dem Sie aufgefordert werden, den Bereich mit den Namen auszuwählen, die Sie verarbeiten möchten – markieren Sie die entsprechenden Zellen und klicken Sie auf OK.
Hinweis: Dieses Makro entfernt nur Anreden, die ganz am Anfang der Zelle stehen, unabhängig von der Groß-/Kleinschreibung. Wenn einige Namen ungewöhnliche Titel verwenden, können Sie diese dem arrSalutations-Array im Code hinzufügen. Erstellen Sie immer eine Sicherungskopie Ihrer Daten, bevor Sie Massenoperationen mit VBA durchführen, um unerwünschte Änderungen zu vermeiden.
Anreden mit Excels Blitzvollauffüllfunktion entfernen
Ab Excel 2013 macht die Blitzvollauffüllfunktion es einfach, Anreden zu entfernen, indem sie Datenmuster erkennt und anwendet. Dies ist eine schnelle, intuitive Methode – insbesondere für Benutzer, die eine visuelle, interaktive Lösung bevorzugen und relativ gut strukturierte Daten haben. Die Blitzvollauffüllfunktion ist ideal, wenn Ihr Datensatz nicht riesig ist und es ein klares, sich wiederholendes Muster zwischen der Anrede und dem Rest des Namens gibt. Allerdings funktioniert sie bei stark unregelmäßigen oder inkonsistenten Daten möglicherweise nicht gut.
1. Erstellen Sie eine neue Spalte neben Ihren Namen. Geben Sie in die Zelle neben der ersten Dateneingabe (zum Beispiel, wenn A2 "Herr John Smith" ist, geben Sie in B2 "John Smith" ein – lassen Sie die Anrede weg).
2. Beginnen Sie, den nächsten Wert in B3 einzugeben. Sobald Excel das Muster erkennt (normalerweise nach 1-2 Einträgen), schlägt es eine Vervollständigung für die restlichen Zellen in der Spalte vor, basierend auf Ihrem Beispiel.
3. Drücken Sie einfach Enter, um die Vorschläge zu akzeptieren, und die Blitzvollauffüllfunktion vervollständigt automatisch den Rest der Spalte und entfernt die Anreden für Sie.
Wenn die Blitzvollauffüllfunktion nicht automatisch erscheint, können Sie sie manuell aktivieren: Klicken Sie auf die Zelle direkt unterhalb Ihres letzten Eintrags, dann klicken Sie auf Daten > Blitzvollauffüllung in der Symbolleiste oder verwenden Sie die Tastenkombination Strg+E.
Tipps: Die Blitzvollauffüllfunktion kann empfindlich auf Inkonsistenzen reagieren. Überprüfen Sie die Ergebnisse auf Fehler, insbesondere wenn einige Namen nicht dem erwarteten Muster entsprechen. Diese Funktion aktualisiert sich nicht automatisch; wenn sich Ihre ursprünglichen Namen ändern, müssen Sie die Blitzvollauffüllfunktion wiederholen.
Vorteile: Kein Formel-Auswendiglernen, sehr visuell, schnell bei Listen mit offensichtlichen Mustern.
Einschränkungen: Nicht geeignet für sehr große Datensätze oder ungleichmäßige Muster, nur in Excel 2013 oder neuer verfügbar.
Anreden und Namen mit Formeln in separate Spalten aufteilen
Wenn Sie die Anrede und den Rest des Namens lieber in zwei separate Spalten aufteilen möchten, statt die Anrede einfach zu entfernen, können Excel-Formeln auch hier helfen. Diese Methode ist besonders nützlich, wenn Sie die Anrede noch für Berichte oder andere Nutzungsszenarien wie personalisierte Kommunikation oder Segmentierung nach Titeln benötigen. Die Formeln funktionieren am besten bei Daten, die konsistent ein einzelnes, festes Trennzeichen (wie ein Leerzeichen) zwischen der Anrede und dem Namen verwenden.
1. Um die Anrede zu extrahieren, geben Sie die folgende Formel in eine leere Zelle ein (z.B. C2, wenn Ihre Namen in Spalte A sind):
=LEFT(A2, FIND(" ",A2)-1)Dies extrahiert den Text links vom ersten Leerzeichen und isoliert so die Anrede.
Ziehen Sie den Ausfüllknauf nach unten, um die Formel auf andere Zeilen zu kopieren. Alle Anreden werden in der neuen Spalte angezeigt.
2. Um nur den Namen zu extrahieren, geben Sie diese Formel in die nächste leere Spalte ein (z.B. D2):
=MID(A2, FIND(" ",A2)+1,1024)Diese Formel ruft den gesamten Text ab dem ersten Leerzeichen ab, welches der tatsächliche Name ist. Ziehen Sie wieder den Ausfüllknauf nach unten, um die anderen Zeilen zu füllen.
Tipps: Sie können die Spaltenreferenzen (A2, etc.) in den Formeln anpassen, um sie an die tatsächliche Position Ihrer Daten anzupassen. Seien Sie vorsichtig, falls ein Namensfeld leer ist oder nicht dem erwarteten Format entspricht, da dies Formelfehler verursachen kann.
Die besten Produktivitätstools für das Büro
Stärken Sie Ihre Excel-Fähigkeiten mit Kutools für Excel und genießen Sie Effizienz wie nie zuvor. Kutools für Excel bietet mehr als300 erweiterte Funktionen, um die Produktivität zu steigern und Zeit zu sparen. Klicken Sie hier, um die Funktion zu erhalten, die Sie am meisten benötigen...
Office Tab bringt die Tab-Oberfläche in Office und macht Ihre Arbeit wesentlich einfacher
- Aktivieren Sie die Tabulator-Bearbeitung und das Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Tabs innerhalb desselben Fensters, statt in neuen Einzelfenstern.
- Steigert Ihre Produktivität um50 % und reduziert hunderte Mausklicks täglich!
Alle Kutools-Add-Ins. Ein Installationspaket
Das Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro und ist ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- All-in-One-Paket — Add-Ins für Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Ein Installationspaket, eine Lizenz — in wenigen Minuten einsatzbereit (MSI-kompatibel)
- Besser gemeinsam — optimierte Produktivität in allen Office-Anwendungen
- 30 Tage kostenlos testen — keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis — günstiger als Einzelkauf der Add-Ins