Anzeige
Archiv - Navigation
1236to1240
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Hausnummer aus String generieren

Hausnummer aus String generieren
Kai
Hallo,
habe in meiner Tabelle eine Spalte mit Straßennamen UND Hausnummern.
Damit ich in der Spalte aber nur noch die Hausnummern habe, habe ich folgenden Code laufen lassen. (Mit Eurer Hilfe)
Soll alles löschen, was NICHT Ziffer ist:
With Sheets("Quelldaten")
Dim Ende As Long
Ende = .Cells.SpecialCells(xlCellTypeLastCell).Row
For Each zellchen In .Range("F2:F" & Ende)
Text1 = zellchen.Text
Text2 = ""
For i = 1 To Len(Text1)
If Not IsNumeric(Mid(Text1, i, 1)) Then Text2 = Text2 & Mid(Text1, i, 1)
Next i
zellchen.Value = Text2
Next
End With
Klappt auch soweit super.
Nun habe ich festgestellt, dass es auch Angaben gibt, bei denen zB als Hausnummer "StrasseNr. 82-84" oder "StrasseNr.20A"
Nach dem obigen Code hätte ich nun als Ergebnis
"8284" bzw. "20"
Dieses Ergebnis wäre natürlich falsch. Das war aber mein Fehler, habe nicht an solche Kombinationen gedacht. Wie gesagt, bei Angaben mit Hausnummer ohne Buchstabe oder Bindestrich funktioniert es"
UM die richtige Hausnummer aus den Angaben generieren zu können, müsste ich wahrscheinlich prüfen: ab welcher Stelle fängt meine Hausnummer an. Sprich : schneide alles ab, bis die erste Ziffer kommt. Lasse aber Bindestrich zwischen Ziffern oder Angaben nach der Hausnummer stehn.
Wie müsste denn diese Prüfung lauten ?
Danke
Gruß
Kai
AW: Hausnummer aus String generieren
28.11.2011 14:57:58
Harald
Hallo Kai,
so:
Sub test()
With Sheets("Tabelle1")
Dim Ende As Long
Ende = .Cells.SpecialCells(xlCellTypeLastCell).Row
For Each zellchen In .Range("F2:F" & Ende)
Text1 = zellchen.Text
For i = 1 To Len(Text1)
If Not IsNumeric(Left(Text1, 1)) Then
Text1 = Right(Text1, Len(Text1) - 1)
Else
Exit For
End If
Next i
'zellchen.Value = Text1
Debug.Print ">" & Text1 & "

Zur Sicherheit habe ich die Zuweisung an die Zelle auskommentiert und durch eine Debug-Ausgabe ersetzt.
Gruß Harald
Sorry, war falscher Code :-( Bitte noch mal Hilfe
28.11.2011 15:39:43
Kai
Hallo
Sorry, ich glaube, jetzt bin ich, glaube ich, total durcheinander gekommen.
Glaub, ich habe den falschen Code gezeigt.
Also hier noch mal alles komplett.
Mit diesem Code lese ich aus der Spalte F die Hausnummern aus und schreibe sie nach G.
Hierbei habe ich nun das beschriebene Problem, dass wenn im Strassenname stand zB:
"StrasseNr. 18-20" dann steht in G = "1820" drin oder der Strasssenname ist "StrasseNr. 18B"
dann steht in G = "18" drin.
Private Sub CommandButton2_Click()
'Spalte G
'Hausnummern generieren, Alle was nicht ziffer ist löschen
With Sheets("Quelldaten")
'letzte benutzte Zeile
Dim Ende As Long
Ende = .Cells.SpecialCells(xlCellTypeLastCell).Row
'MsgBox Ende
Dim Zelle As Range
Dim Neu As String
Dim i As Integer
For Each Zelle In .Range("F2:F" & Ende)
For i = 2 To Len(Zelle)
Select Case Asc(Mid(Zelle, i, 1))
Case 48 To 57
Neu = Neu & Mid(Zelle, i, 1)
End Select
Next i
Zelle.Offset(0, 1).Value = Neu
Neu = ""
Next Zelle
End With
End Sub

Erste Frage: was muss ich am Code ändern, das bis zur ERSTEN ZIffer abgeschnitten wird aber ZUsätze wie 20A oder 19B erhalten bleiben und dieser neue String nach G eingefügt wird
'###################################################################################
Nachdem die Hausnummern dann in G generiert wurden, will ich mit folgendem Code aus Splate F alles löschen, was zur Hausnummer gehört
Also im prinzip gleiche Problematik, wie Oben.
Private Sub CommandButton3_Click()
'Spalte F
'Alles was Zahl ist löschen, dass nur noch Strasse da steht.
With Sheets("Quelldaten")
Dim Ende As Long
Ende = .Cells.SpecialCells(xlCellTypeLastCell).Row
For Each zellchen In .Range("F2:F" & Ende)
Text1 = zellchen.Text
Text2 = ""
For i = 1 To Len(Text1)
If Not IsNumeric(Mid(Text1, i, 1)) Then Text2 = Text2 & Mid(Text1, i, 1)
Next i
zellchen.Value = Text2
Next
End With
End Sub
Zweite Frage; was muss ich ändern, damit in der Spalte F ab der ersten Ziffen bis zum Ende des Strings alles gelöscht wird.
Also, dass nur noch der reine Strassenname da steht.
Sorry für das durcheinander, hab selber den Durchblick verloren.
Gruß
Kai
Anzeige
AW: Hausnummer aus String generieren
28.11.2011 14:59:56
guentherh
Text2 = Text2 & Mid(Text1, i)
exit for

Next i
Gruß,
Günther
AW: Hausnummer aus String generieren
28.11.2011 15:38:19
Peter
Hallo Kai,
was machst Du denn mit Straße des 17. Juni 35 oder mit den Straßen in Mannheim P1, A7 usw.
Es gibt auch Hausnummern Collenbachstr. 12½ oder Armin-T.-Wegner-Platz 17 a - 19 a
Köln hat z. B. als einzige Stadt in Deutschland auch 4-stellige Hausnummern - z. B. Venloer Straße 1503.
Ich nehme an, alle Extremfälle lassen sich nicht umstellen.
Gruß Peter
AW: Hausnummer aus String generieren
28.11.2011 15:41:37
Kai
Hallo Peter,
habe gerade dazu noch mal geschrieben, siehe meine neue Beschreibung.
GRuß
Kai
AW: Hausnummer aus String generieren
28.11.2011 17:05:58
Peter
Hallo Kai,
versuche es mit der UDF - in Zelle F1 =Hausnummer(G1)
Option Explicit
'
' Man braucht im VBA einen Verweis auf MS VBScript Regular Expression.
' D. h. im VBA im Menue Extras-Verweise dieses Teil suchen und ankreuzen.
'
' Filtert alles ab einer Zahl als Hausnummer
'
Public Function Hausnummer(s As String)
Dim objRegEx As Object
Set objRegEx = CreateObject("vbscript.regexp")
With objRegEx
.Global = True
.IgnoreCase = False
.MultiLine = False
.Pattern = "[^0-9]*?\s?(\d+.?)"
End With
Hausnummer = objRegEx.Replace(s, "$1")
End Function
Aber auch damit wirst Du längst nicht alle Sonder-Schreibweisen erwischen.
Genaueres dazu im Abschnitt „Geschichte“:
http://de.wikipedia.org/wiki/Hausnummer#Verwendung_der_Hausnummer_in_Adressangaben
Gruß Peter
Anzeige
Das würde mir reichen ....
28.11.2011 20:54:25
Kai
Hallo Peter,
ich glaube, das würde für mich reichen. Habs mal getestet und scheint zu funktionieren.
Ist genau das , was ich gesucht habe.
Vielen dank
Kannst Du mir vielleicht dann auch zeigen, wie man in der Spalte F nachher aus den Strassennamen + Hausnummern die Hausnummern per schleife heraus löschen kann.
Wäre genau das gleiche Prinzip, nur dieses Mal anders herum:
im Prinzip alles ab der ersten Ziffer löschen.
Dann dürfte nur noch der Strassenname übrig bleiben.
Danke
Kai
AW: Das würde mir reichen ....
28.11.2011 21:14:40
Peter
Hallo Kai,
hier ist das Gegenstück:
'
' Filtert alles vor einer Zahl als Straße
'
Public Function Strasse(s As String)
Dim objRegEx As Object
Set objRegEx = CreateObject("vbscript.regexp")
With objRegEx
.Global = True
.IgnoreCase = False
.MultiLine = False
.Pattern = "([^0-9]*?)\s?\d+.?"
End With
Strasse = objRegEx.Replace(s, "$1")
End Function

Gruß Peter
Anzeige
AW: Das würde mir reichen ....
28.11.2011 21:28:21
Peter
Hallo Kai,
evtl. noch einfacher so:
'
' angenommen: Straße + Hausnummer in Zelle A, Hausnummer in Zelle B
' Ergebnis dann in Zelle D = nur der Straßenname
'
Public Sub Strassenname()
Dim lZeile  As Long
With ThisWorkbook.Worksheets("Tabelle1") ' den Tabellenblattnamen ggf. anpassen!
For lZeile = 1 To .Cells(Rows.Count, 1).End(xlUp).Row
.Range("D" & lZeile).Value = Replace(.Range("A" & lZeile).Value, _
.Range("B" & lZeile).Value, "")
Next lZeile
End With
End Sub

Gruß Peter
AW: Das würde mir reichen ....
28.11.2011 22:08:04
Kai
Hallo Peter,
Danke, das mit dem Makro würde mir am besten gefallen. Frage, was kommt bei dir raus, wenn du den Code ausführst.
Bei mir wird der Strassenname + Hausnummer einache nach D kopiert (Strassenname + Hausnummer)
Also mit Hausnummer. B bleibt leer
Was kommt bei dir raus
Danke Kai
Anzeige
AW: Das würde mir reichen ....
29.11.2011 08:47:02
Peter
Hallo Kai,
ich bin von folgender Situation ausgegangen:
Straßenname + Hausnummer in Spalte A
Hausnummer in Spalte B
Dann bringt das Makro den Straßennamen aus Spalte A, ohne den Inhalt von Spalte B, nach Spalte D.
Gruß Peter
Ah, jetzt habe ich verstanden ..
29.11.2011 10:40:28
Kai
Hallo Peter,
Ok, jetzt habe ich verstanden, ja das funktioniert.
Ich habe mich immer gewundert, was die seperate Hausnummer in B soll.
Ne, alles klar , das hilft mir weiter.
Danke
Kai

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige