|
In einer Tabellenspalte sind die Vor- und Nachnamen der Adressaten durch ein Leerzeichen getrennt eingegeben.
Jetzt möchten Sie die Namen und Vornamen getrennt auf zwei Spalten aufteilen, da das für die Verwendung in Serienbriefen flexibler ist. Vorausgesetzt, die kombinierten Namen stehen in der ersten Spalte ab Zelle A1, und die geteilten Vor- und Nachnamen sollen in Spalte 2 und 3 stehen, lösen Sie das Problem recht einfach mit der folgenden Formel:
Zelle A1: Vorname [Leerzeichen] Nachname
Zelle B1: =LINKS(A1;FINDEN(" ";A1;1)-1)
Zelle C1: =RECHTS(A1;LÄNGE(A1)-FINDEN(" ";A1))
Die Formeln bedienen sich der Excel-Funktion FINDEN, um die Namen anhand des Leerzeichens zu trennen. Vergessen Sie nicht, jeweils das Leerzeichen zwischen den Anführungszeichen einzugeben. Wenn Sie die beiden Formeln in den Spalten nach unten kopieren, stehen in Spalte B die Vornamen und in Spalte C die Nachnamen.
Um die Position des Leerzeichens zu bestimmen, können Sie auch die Funktion SUCHEN verwenden. Sie ermittelt das erste Vorkommen einer Zeichenkette oder eines Zeichens innerhalb einer anderen Zeichenkette. Diese Position übergeben Sie dann an die Funktion TEIL. Sie schneidet aus einem Text ab einer bestimmten Position eine geforderte Anzahl Stellen heraus und liefert diesen Text als Ergebnis zurück. Wenn also der aufzuteilende Name in Zelle A1 steht, erhalten Sie mit folgender einfachen Formel jeweils den Vornamen:
=TEIL(A1;1;SUCHEN(" ";A1;1)-1)
Entsprechend errechnet die nachstehende Suchformel den Nachnamen:
=TEIL(A1;SUCHEN(" ";A1;1)+1;LÄNGE(A1)-SUCHEN(" ";A1;1))
Vergessen Sie dabei nicht, zwischen die Anführungszeichen jeweils das Leerzeichen einzugeben.
Um den Vornamen auszulesen, beginnt die erste Formel an der ersten Stelle in der jeweiligen Zeichenkette und berücksichtigt alle Zeichen, die vor dem Leerzeichen stehen. Der Nachname beginnt entsprechend eine Position hinter der Leerstelle und geht bis zum Ende der gesamten Zeichenkette. Das Ende errechnet sich aus dem absoluten Wert für die Länge der Zeichenkette (Funktion LÄNGE) abzüglich des Werts für die Position des Leerzeichens nach dem Vornamen.
Aufgabe: Vorname und Nachname stehen gemeinsam in Spalte A und sollen in Spalte B (Vorname) und Spalte C (Nachname) getrennt werden.
In Spalte B folgende Formel eintragen:
=LINKS(A1;SUCHEN(" ";A1))
In Spalte C folgende Formel eintragen:
=RECHTS(A1;LÄNGE(A1)-(SUCHEN(" ";A1)))
A | B | C |
Beispiele | Ergebnis "Vorname" | Ergebnis "Nachname" |
Hans Maier | Hans | Maier |
Martha Bauer | Martha | Bauer |
Gerd-Friedrich Gauss | Gerd-Friedrich | Gauss |
Die Formel der Spalte B sucht im Inhalt der linken Zelle das ersten Leerzeichen. Wird ein Leerzeichen gefunden, so sollte alles, was links davon steht, der Vorname sein.
Bei der Formel der Spalte C wird die Länge des Vornamens abgezogen. Der Teil rechts neben dem Leerzeichen sollte den Nachnamen erzeugen. Dies funktioniert aber nur, wenn Vor- und Nachname durch ein Leerzeichen getrennt sind. Wird ein anderes Trennzeichen verwendet, z. B. ein Komma, so muss in der Funktion SUCHEN() der erste Parameter entsprechend angepasst werden.
Bei dem Beispiel "Monika Sabine Weber" hat man leider verloren, da die Dame zwei Vornamen hat, die nicht verbunden sind:
A | B | C |
Beispiel | Ergebnis "Vorname" | Ergebnis "Nachname" |
Monika Sabine Weber | Monika | Sabine Weber |
Straße und Hausnummer stehen gemeinsam in Spalte A und sollen in Spalte B (Straße) und Spalte C (Nummer) getrennt werden.
In Spalte B folgende Formel eintragen:
=LINKS(A1;LÄNGE(A1)-LÄNGE(C1))
In Spalte C folgende lange Matrixformel* eintragen:
{=TEIL(RECHTS(A1;4);VERGLEICH(WAHR;ISTZAHL(TEIL(RECHTS(A1;4);SPALTE(1:1);1)*1);0);LÄNGE(RECHTS(A1;4)))*1}
A | B | C |
Beispiele | Ergebnis "Straße" | Ergebnis "Haus-Nr." |
Mathildenstr. 11 | Mathildenstr. | 11 |
Hans-Herbert-Str. 19 - 21 | Hans-Herbert-Str. 19 - | 21 |
Hans Herbert Str. 19-21 | Hans Herbert Str. | 44440 (zeigt falschen Wert an!) |
Straße des 17. Juni 23 | Straße des 17. Juni | 23 |
Straße des 17. Juni 23a | #WERT! | #WERT! |
Bei dem Beispiel "Straße des 17. Juni 23a" funktioniert dies nicht, da die Hausnummer einen Buchstaben enthält. Lösung siehe weiter unten.
Weiterhin sind Hausnummern wie z. B. "Hans-Herbert-Str. 19 - 21" nicht trennbar.
In Spalte B folgende Formel eintragen:
=LINKS(A1;LÄNGE(A1)-LÄNGE(C1)-1)
In Spalte C folgende lange Matrixformel* eintragen:
{=TEIL(A1;SUCHEN("#";WECHSELN(A1;" ";"#";LÄNGE(A1)-LÄNGE(WECHSELN(A1;" ";))))+1;9)}
A | B | C |
Beispiele | Ergebnis "Straße" | Ergebnis "Haus-Nr." |
Mathildenstr. 11 | Mathildenstr. | 11 |
Hans-Herbert-Str. 19 - 21 | Hans-Herbert-Str. 19 - | 21 |
Hans Herbert Str. 19-21 | Hans Herbert Str. | 19-21 |
Straße des 17. Juni 23 | Straße des 17. Juni | 23 |
Straße des 17. Juni 23a | Straße des 17. Juni | 23a |
Auch hier sind Hausnummern wie z. B. "Hans-Herbert-Str. 19 - 21" nicht trennbar, sofern sich Leerzeichen zwischen Zahlen und Bindestrich befinden.
*Bitte beachten: Bei den Beispielen handelt sich um Matrixformeln. Matrixformeln sind in der Bearbeitungsleiste kenntlich an den sie umgebenden geschweiften Klammern { } und werden mit der Tastenkombination [Strg] [Umschalt] [Return] ohne diese Klammern eingegeben.
A | B | C |
Beispiele | Ergebnis "Straße" | Ergebnis "Haus-Nr." |
Berliner Str. 15 | Berliner Str. | 15 |
Hans Maurer Straße 11b | Hans Maurer Straße | 11b |
Straße des 17. Juni 23 | Straße des | 17. Juni 23 |
Straße des 17. Juni 24 a | Straße des | 17. Juni 24 a |
3. Nebenstraße | 3. Nebenstraße |
Auch hier werden Fehler produziert: Namen wie "Straße des 17. Juni 23" werden nicht korrekt getrennt. Es bliebe dem User nichts anderes übrig, als seine Daten manuell zu überarbeiten.
Sub TrenneStrasseNummer() Dim Zellwert$, Zelle As Range, Zeile% For Each Zelle In ActiveSheet.Range("A2:A6") Zeile = Zeile + 1 Zellwert = ActiveSheet.Range("A" & Zeile + 1).Value ActiveSheet.Range("B" & Zeile + 1).Value = StrName(Zellwert) ActiveSheet.Range("C" & Zeile + 1).Value = HsNr(Zellwert) Next End Sub 'Funktion: Straßennamen extrahieren. Function StrName(StraName As String) As String Dim pos As Integer, Laenge As Integer pos = PosHsNrInStrasse(StraName) Laenge = Len(StraName) 'Trim: führende und nachgestellte Leerzeichen entfernen. If pos > 0 Then StrName = Trim(Left(StraName, pos - 1)) Else StrName = StraName End If End Function 'Funktion: Hausnummer extrahieren. Function HsNr(StraName As String) As String Dim pos As Integer, Laenge As Integer pos = PosHsNrInStrasse(StraName) Laenge = Len(StraName) If pos > 0 Then HsNr = Right(StraName, Laenge - pos + 1) Else HsNr = "" End If End Function
'Von rechts nach links durch Straßennamen gehen (bis auf die drei linken Zeichen) damit Straßen, die mit Zahl beginnen (z. B. "3. Nebenstraße") nicht als Hausnummer erkannt werden.
Function PosHsNrInStrasse(StraName As String) As Integer Dim Zaehler As Integer, Laenge As Integer, x As String Laenge = Len(StraName) PosHsNrInStrasse = 0 For Zaehler = Laenge To 3 Step -1 x = Mid(StraName, Zaehler, 1) 'Aktuell zu prüfendes Zeichen. If IsNumeric(x) Then 'Prüfen, ob Zeichen eine Zahl ist. PosHsNrInStrasse = InStr(StraName, x) 'Position der Zahl. End If Next End Function
© Vorname und Nachname, sowie Straße und Hausnummer per Excel trennen wurde dokumentiert von Winfried Brumma (Pressenet), 2020. Bildnachweis: oben Scrabble Buchstaben, sowie weiter unten Hausnummer 44; beide: CC0 (Public Domain Lizenz).
Archive:
Jahrgänge:
2022 |
2021 |
2020 |
2019 |
2018 |
2017 |
2016 |
2015 |
2014 |
2013 |
2012 |
2011 |
2010 |
2009
Themen:
Autor werden |
Buch-Rezensionen |
Ratgeber |
Sagen & Legenden |
Fantasy Mythologie |
IT & Technik |
Krimi Thriller |
Fachartikel & Essays |
Jugend- & Kinderbücher |
Bedeutung der Tarotkarten |
Bedeutung der Krafttiere
Noch mehr Bücher lesen (Werbung):
Fantasy & Science Fiction
| Krimis & Thriller
| Ratgeber
| Reise & Abenteuer
Sie schreiben anspruchsvolle Romane und Erzählungen? Wir suchen neue Autorinnen und Autoren. Melden Sie sich!
Wenn Sie die Informationen auf diesen Seiten interessant fanden, freuen wir uns über einen Förderbeitrag. Empfehlen Sie uns auch gerne in Ihren Netzwerken. Herzlichen Dank!
Sitemap Impressum Datenschutz RSS Feed