Unsere Webseiten benötigen JavaScript. Dies scheint in Ihrem Browser jedoch deaktiviert zu sein.

News & Tipps zu Microsoft Excel

Lesen Sie hier einige der besten Beiträge aus SmartTools Excel Weekly sowie ausgewählte Inhalte anderer E-Mail-Newsletter von SmartTools Publishing rund um Microsoft Excel.

Beitrag aus SmartTools Excel Weekly

Automatische Trennung von Straße und Hausnummer

Excel 365 2024 2021 2019 2016 2013

FRAGE Ich bin in einem Verein für die Mitgliederverwaltung zuständig. Die Daten erfasse ich in einer Excel-Tabelle, in der ich separate Spalten für die Straße und die Hausnummer eingerichtet habe. Es passiert mir leider immer wieder, dass ich in der Straßen-Spalte nicht nur den Straßennamen, sondern auch die Hausnummer eingebe. Dann muss ich die Hausnummer erst wieder löschen und dann noch einmal neu in die korrekte Spalte eintragen. Ließe sich diese Fehlerquelle nicht abschalten, indem ein Makro prüft, ob am Ende des Straßennamens Ziffern stehen, und diese gegebenenfalls automatisch aus der Straßen-Spalte in die Hausnummer-Spalte überträgt?

H. Mederle

ANTWORT Das ist durchaus möglich. Sie können dafür eine Ereignisprozedur nutzen, die Excel automatisch bei Änderungen an Ihrem Tabellenblatt ausführt.

In der Prozedur prüfen Sie, ob eine neue Eingabe in der Straßen-Spalte mit Ziffern endet. Sollte das der Fall sein, entfernt die Prozedur die Ziffern und trägt sie automatisch in die angrenzende Zelle für die Hausnummer ein.

Wenn Sie Ihre Mitgliederverwaltung entsprechend erweitern wollen, gehen Sie wie folgt vor:

  1. Öffnen Sie die Arbeitsmappe Ihrer Mitgliederverwaltung.
  2. Klicken Sie mit der rechten Maustaste auf das Register des Tabellenblatts, in dem Sie die Adressen erfassen. Im angezeigten Kontextmenü wählen Sie Code anzeigen an.
  3. Sie gelangen in die Entwicklungsumgebung von Excel, in der das Codefenster des Tabellenblatts geöffnet ist. Am oberen Rand dieses Fensters befinden sich zwei Dropdownfelder. Wählen Sie im linken Dropdownfeld "Worksheet" und im rechten Dropdownfeld "Change" aus.
  4. Es wird automatisch ein Prozedurgerüst für das Worksheet_Change-Ereignis eingefügt. Ergänzen Sie den Programmcode wie folgt:
Private Sub Worksheet_Change(ByVal Target As Range)

Const ZEILE_UEBERSCHRIFT As Long = 3
Const SPALTE_STR As Long = 4
Const SPALTE_HNR As Long = 5

Dim strText As String
Dim i As Long

  'Prüfung auf korrekte Eingabezelle – sonst Abbruch
  If Target.CountLarge > 1 Then Exit Sub
  If Target.Row <= ZEILE_UEBERSCHRIFT Then Exit Sub
  If Target.Column <> SPALTE_STR Then Exit Sub

  On Error GoTo WsChg_Error

  'Straßeneingabe auf Ziffern untersuchen
  strText = Trim$(CStr(Target.Value))
  i = Len(strText)
  Do While i > 0
    If Not Mid$(strText, i, 1) Like "#" Then Exit Do
    i = i - 1
  Loop
  If i = Len(strText) Or i = 0 Then Exit Sub

  Application.EnableEvents = False

  'Straßenzelle: Straßenname ohne Hausnummer
  Target.Value = RTrim$(Left$(strText, i))
  'Hausnr.-Zelle: im Straßenfeld eingegebene Nummer
  With Me.Cells(Target.Row, SPALTE_HNR)
    .Value = Mid$(strText, i + 1)
    .Select
  End With
  Application.SendKeys "{F2}"

WsChg_Error:
  Application.EnableEvents = True
End Sub
  1. Passen Sie die drei Konstanten, die am Anfang der Prozedur in den mit "Const" beginnenden Zeilen definiert werden, an Ihre Tabellenumgebung an, indem Sie den Wert hinter dem Gleichheitszeichen ändern:
  2. ZEILE_UEBERSCHRIFT entspricht der Nummer der Tabellenzeile mit den Spaltenüberschriften Ihrer Adresstabelle (die Eingabe der Straßennamen, Hausnummern usw. beginnt in der Zeile direkt darunter)
  3. SPALTE_STR entspricht der Nummer der Spalte, in der Sie die Straßennamen eingeben
  4. SPALTE_HNR entspricht der Nummer der Spalte, in der Sie die Hausnummern eingeben
  5. Verlassen Sie die Entwicklungsumgebung per Datei-Schließen und zurück zu Microsoft Excel.

Beachten Sie, dass Sie die Arbeitsmappe Ihrer Mitgliederverwaltung nun als Excel-Arbeitsmappe mit Makros, als XLSM-Datei, speichern müssen. Sonst würde die Ereignisprozedur nicht funktionieren bzw. gar nicht erst gespeichert werden.

Wenn Sie in der Straßen-Spalte jetzt zum Beispiel "Hauptstraße 12" eingeben und die Eingabe mit Return oder Tab abschließen, sorgt die Ereignisprozedur automatisch dafür, dass die Hausnummer aus dem Straßen-Feld entfernt und in die Hausnummern-Spalte eingefügt wird. Die Prozedur simuliert sogar den Druck der Taste F2, so dass Sie sich sofort im Bearbeitungsmodus befinden, um die Hausnummer bei Bedarf um weitere Ziffern ergänzen zu können.

Die Ereignisprozedur splittet Straßenname und Hausnummer automatisch auf die passenden Spalten auf.