Live-Forum - Die aktuellen Beiträge
Datum
Titel
20.04.2024 08:56:18
Textbox in Tabellenblatt ohne Bestätigung in Zelle eintragen
20.04.2024 07:57:54
Tablle fürs E-Auto - Preis wenn kürzer als 60 Min Ladezeit
20.04.2024 00:49:19
Excel Sheet in einen Excel Kalender einfügen
20.04.2024 00:08:11
In Ordner nur das Speichern von Dateien erlauben
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
848to852
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
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
848to852
848to852
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Name & Adresse in Zelle: nur Strasse löschen

Name & Adresse in Zelle: nur Strasse löschen
23.02.2007 17:01:00
Gregor
Hallo Profis!
In Spalte B (1:1000) steht in jeder Zelle Texte der folgenden Art (mal mit, mal ohne Komma getrennt):
- Hans Muster AG, Bahnhofstr. 43, München
- XPC AG Ackerstr. 516 Düsseldorf
Gibt es eine Möglichkeit, die Strasse inkl. Nummer aus der Zelle zu löschen?
Danke jetzt schon für deinen Input!!
Grüsse
Gregor

9
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
An irgendwas muss man sich orientieren...
23.02.2007 17:19:18
{Boris}
Hi Gregor,
...können - das ist bei jeder Stringzerlegung so.
Und in Deinem Fall dürfte es wirklich kaum machbar sein.
Selbst wenn man sich beispielsweise an der Hausnummer orientiert (die die erste vorkommene ZAHL in dem String ist), dann weiß man immer noch nicht, aus wievielen Wörtern der Straßenname besteht, denn so was wie "In der Donk" oder "Erfurter Str." sind schon wieder mehr als ein Wort - man bekommt also die Leerzeichen nicht gepackt.
Oder gibt es eine weitestgehend eindeutige Regel?
Grüße Boris
AW: An irgendwas muss man sich orientieren...
23.02.2007 17:32:53
Gregor
Hallo Boris; merci für den Input.
Ja, die Strasse lautet immer auf "str." (Strasse zuvor auf Str. ersetzt); Wege oder "in der Donk" sind unsichtig, können unbeachtet bleiben.
Zusatzfrage:
suchen und finden mit " ? " findet eine Postleitzahl, aber auch Wörter der Art : " UNIQE ".
Wenn man mit suchen/finden/ersetzen mit Variblen arbeiten möchte, wäre es demnach schön, wenn
man wie im Word nur Zahlen suchen könnte? Gibt es so was im Excel?
Grüsse und Merci für den Einsatz
Gregor
Anzeige
AW: An irgendwas muss man sich orientieren...
23.02.2007 17:33:49
Gregor
Sorry, Frage ist noch offen!
AW: An irgendwas muss man sich orientieren...
23.02.2007 21:12:41
Uwe
Du könntest z.B. die boolsche (true/false) Rückgabe von IsNumeric(String) überprüfen...
AW: Name & Adresse in Zelle: nur Strasse löschen
23.02.2007 22:05:12
Peter
Hallo Gregor,
mein Makro erschlägt mit Sicherheit nicht alle Fälle, aber einen großen Teil deiner Adressen kann man sicherlich damit separieren.


Option Explicit
Public Sub Separieren()
Dim lZeile   As Long
Dim aTmp     As Variant
Dim iIndex   As Integer
Dim iPosit   As Integer
   For lZeile = 1 To Range("B65536").End(xlUp).Row
      Range("B" & lZeile).Value = Replace(Range("B" & lZeile).Value, "Strasse", "Str.")
      Range("B" & lZeile).Value = Replace(Range("B" & lZeile).Value, "strasse", "str.")
      Range("B" & lZeile).Value = Replace(Range("B" & lZeile).Value, "Straße", "Str.")
      Range("B" & lZeile).Value = Replace(Range("B" & lZeile).Value, "straße", "str.")
      aTmp = Split(Range("B" & lZeile).Value, " ")
      Range("C" & lZeile & ":E" & lZeile).ClearContents
      For iIndex = 0 To UBound(aTmp)
         iPosit = InStr(LCase(aTmp(iIndex)), "str.")
         If iPosit = 0 Then
            Range("C" & lZeile).Value = Range("C" & lZeile) & " " & aTmp(iIndex)
          Else
            Range("D" & lZeile).Value = Range("D" & lZeile).Value & " " & aTmp(iIndex)
            If IsNumeric(Left(aTmp(iIndex + 1), 1)) Then
               Range("D" & lZeile).Value = _
                  Range("D" & lZeile).Value & " " & aTmp(iIndex + 1)
               Range("E" & lZeile).Value = _
                  Range("E" & lZeile).Value & " " & aTmp(iIndex + 2)
               Exit For
            End If
         End If
      Next iIndex
      Range("C" & lZeile).Value = Trim(Range("C" & lZeile).Value)
      Range("D" & lZeile).Value = Trim(Range("D" & lZeile).Value)
      Range("E" & lZeile).Value = Trim(Range("E" & lZeile).Value)
   Next lZeile
End Sub 


Gruß Peter
Anzeige
AW: Name & Adresse in Zelle: nur Strasse löschen
24.02.2007 08:30:00
Peter
Hallo Gregor,
eine verbesserte Version:


Public Sub Separieren()
Dim lZeile   As Long
Dim aTmp     As Variant
Dim iIndex   As Integer
Dim sTyp     As String
   For lZeile = 1 To Range("B65536").End(xlUp).Row
      Range("B" & lZeile).Value = Replace(Range("B" & lZeile).Value, "Strasse", "Str.")
      Range("B" & lZeile).Value = Replace(Range("B" & lZeile).Value, "strasse", "str.")
      Range("B" & lZeile).Value = Replace(Range("B" & lZeile).Value, "Straße", "Str.")
      Range("B" & lZeile).Value = Replace(Range("B" & lZeile).Value, "straße", "str.")
      Range("B" & lZeile).Value = Replace(Range("B" & lZeile).Value, "Str ", "Str. ")
      Range("B" & lZeile).Value = Replace(Range("B" & lZeile).Value, "str ", "str. ")
      aTmp = Split(Range("B" & lZeile).Value, " ")
      Range("C" & lZeile & ":E" & lZeile).ClearContents
      For iIndex = 0 To UBound(aTmp)
         If InStr(LCase(aTmp(iIndex)), "str.") > 0 Then
            If Len(aTmp(iIndex)) < 5 Then ' Straßen-Name getrennt ?
               aTmp(iIndex - 1) = aTmp(iIndex - 1) & " " & aTmp(iIndex)
               aTmp(iIndex) = ""
               Exit For
            End If
         End If
      Next iIndex
      sTyp = "Name"
      For iIndex = 0 To UBound(aTmp)
         If aTmp(iIndex) <> "" Then
            If InStr(LCase(aTmp(iIndex)), "str.") > 0 Then
               sTyp = "Str"
            End If
            If sTyp = "Str" Then
               If aTmp(iIndex) = _
                  Right(Range("B" & lZeile).Value, Len(aTmp(iIndex))) Then ' Ortsname
                  sTyp = "Ort"
               End If
            End If
            Select Case sTyp
               Case "Name"
                  Range("C" & lZeile).Value = _
                     Range("C" & lZeile) & " " & aTmp(iIndex)
               Case "Str"
                  Range("D" & lZeile).Value = _
                     Range("D" & lZeile).Value & " " & aTmp(iIndex)
               Case "Ort"
                  Range("E" & lZeile).Value = _
                     Range("E" & lZeile).Value & " " & aTmp(iIndex)
            End Select
         End If
      Next iIndex
      Range("C" & lZeile).Value = Trim(Range("C" & lZeile).Value)
      Range("D" & lZeile).Value = Trim(Range("D" & lZeile).Value)
      Range("E" & lZeile).Value = Trim(Range("E" & lZeile).Value)
   Next lZeile
End Sub 


Gruß Peter
Anzeige
AW: Name & Adresse in Zelle: nur Strasse löschen
24.02.2007 14:57:00
Peter
Hallo Gregor,
nun ist es halbwegs perfekt - zumindest für alle Straßen-Namen die den Teil Str., str. Straße, straße, Strasse, strasse, Str , str , enthalten.
Was ich in deinem beiden Musteradressen vermisse ist die Postleitzahl. Gibt es die nicht?
Das Makro löscht die Straßen-Namen nicht, sondern separiert Name, Straße, Ort in die drei benachbarten Zellen C, D, E aus denen du die benötigten Teile holen kannst.


'
'   In Spalte B (1:1000) steht in jeder Zelle Texte der folgenden Art
'   (mal mit, mal ohne Komma getrennt):
'
'   - Hans Muster AG, Bahnhofstr. 43, München
'   - XPC AG Ackerstr. 516 Düsseldorf
'
'   Gibt es eine Möglichkeit die Straße inkl. Hausnummer aus der Zelle zu löschen?
'
'   Das nachfolgende Makro löscht die Straßennamen nicht, sondern separiert
'   die Adressteile - Name, Straße, Ort - in drei Zellen (C, D, E), aus denen die
'   gewünschten Teile wieder zusammengefügt bzw. weggelassen werden können.
'
Public Sub Separieren()
Dim lZeile     As Long     ' For/Next Zeilen-Index
Dim aTmp       As Variant  ' Array zur Split-Aufnahme des Textes
Dim iIndex     As Integer  ' Index zum Array
Dim sTyp       As String   ' Typ der Adresse - Name, Straße, Ort
Dim sOrt       As String   ' Kennzeichen Ort = Postlz oder letzter Ortsname
Dim bGefunden  As Boolean  ' Schalter für Postlz gefunden False/True
'
'    es werden die Adressen abgearbeitet
'    dabei werden zuerst alle Straßen-Namen, die in irgendeiner Form auf Straße
'    enden zur späteren Identifikation in Str. oder str. geändert.
'
   For lZeile = 1 To Range("B65536").End(xlUp).Row
      Range("B" & lZeile).Value = Replace(Range("B" & lZeile).Value, "Strasse", "Str.")
      Range("B" & lZeile).Value = Replace(Range("B" & lZeile).Value, "strasse", "str.")
      Range("B" & lZeile).Value = Replace(Range("B" & lZeile).Value, "Straße", "Str.")
      Range("B" & lZeile).Value = Replace(Range("B" & lZeile).Value, "straße", "str.")
      Range("B" & lZeile).Value = Replace(Range("B" & lZeile).Value, "Str ", "Str. ")
      Range("B" & lZeile).Value = Replace(Range("B" & lZeile).Value, "str ", "str. ")
'       den Eingabebereich in ein Array aufsplitten
      aTmp = Split(Range("B" & lZeile).Value, " ")
'       die Ausgabezellen löschen
      Range("C" & lZeile & ":E" & lZeile).ClearContents
'       eine evtl. vorhandene Postleitzahl (5-stellig, numerisch) suchen oder
'       die linksbündig numerische Hausnummer und dann den nachfolgenden Namen
      bGefunden = False
      For iIndex = UBound(aTmp) To 0 Step -1
         If Len(aTmp(iIndex)) = 5 And _
            IsNumeric(aTmp(iIndex)) Then ' ein 5-stelliger, numerisch ?
            bGefunden = True
            Exit For
         ElseIf IsNumeric(Left(aTmp(iIndex), 1)) Then ' Hausnummer gefunden ?
            iIndex = iIndex + 1
            bGefunden = True
            Exit For
         End If
      Next iIndex
'       wenn eine Postlz vorlag, diese, sonst den letzen Ortsnamen speichern
'       um mit diesem Wert den Beginn des Ortes zu identifizieren
      If bGefunden = True Then
         sOrt = aTmp(iIndex)
       Else
         sOrt = aTmp(UBound(aTmp))
      End If
'       getrennte Straßen-Namen (z. B. Hamburger Straße) im Array in eine
'       Array-Zelle zusammenfassen, die leer werdende Zelle löschen
      For iIndex = 0 To UBound(aTmp)
         If InStr(LCase(aTmp(iIndex)), "str.") > 0 Then
            If Len(aTmp(iIndex)) < 5 And _
               iIndex > 2 Then ' Straßen-Name getrennt ?
               aTmp(iIndex - 1) = aTmp(iIndex - 1) & " " & aTmp(iIndex)
               aTmp(iIndex) = ""
               Exit For
            End If
         End If
      Next iIndex
      sTyp = "Name"  ' erster Adress-Typ ist der Name
'
'       den Array abarbeiten und je nach Typ in eine andere Zelle im
'       Tabellenblatt aufteilen bzw. zusammenfassen
'
      For iIndex = 0 To UBound(aTmp)
         If aTmp(iIndex) <> "" Then                        ' Array-Zelle gefüllt ?
            If InStr(LCase(aTmp(iIndex)), "str.") > 0 Then ' Str. gefunden ?
               sTyp = "Str"      ' Typ auf Straße setzen
            End If
            If sTyp = "Str" Then ' nur von Typ Str => Typ Ort
               If aTmp(iIndex) = sOrt Then  ' Postlz bzw. Ortsname gefunden ?
                  sTyp = "Ort"   ' Typ auf Ort
               End If
            End If
            Select Case sTyp     ' Array-Zellen gemäß Typ in die Zellen speichern
               Case "Name"
                  Range("C" & lZeile).Value = _
                     Range("C" & lZeile) & " " & aTmp(iIndex)
               Case "Str"
                  Range("D" & lZeile).Value = _
                     Range("D" & lZeile).Value & " " & aTmp(iIndex)
               Case "Ort"
                  Range("E" & lZeile).Value = _
                     Range("E" & lZeile).Value & " " & aTmp(iIndex)
            End Select
         End If
      Next iIndex
'       die Zellen ohne führende/nachfolgende Leerstellen speichern
'       evtl. vorhandene Semikolon und/oder Kommata entfernen
      Range("C" & lZeile).Value = Trim(Range("C" & lZeile).Value)
      Range("C" & lZeile).Value = Replace(Range("C" & lZeile), ";", "")
      Range("C" & lZeile).Value = Replace(Range("C" & lZeile), ",", "")
      Range("D" & lZeile).Value = Trim(Range("D" & lZeile).Value)
      Range("D" & lZeile).Value = Replace(Range("D" & lZeile), ";", "")
      Range("D" & lZeile).Value = Replace(Range("D" & lZeile), ",", "")
      Range("E" & lZeile).Value = Trim(Range("E" & lZeile).Value)
      Range("E" & lZeile).Value = Replace(Range("E" & lZeile), ";", "")
      Range("E" & lZeile).Value = Replace(Range("E" & lZeile), ",", "")
   Next lZeile
End Sub 


Gruß Peter
Anzeige
AW: Name & Adresse in Zelle: nur Strasse löschen
24.02.2007 16:03:40
Daniel
Hallo
probier mal diese Methode.
ich habs als Funktion geschrieben, dann kann man es auch in Excel verwenden:
Hier das kleine Makro
Sub Strassennamen_raus()
Dim Zelle As Range
For Each Zelle In Range("B1:B1000")
Zelle.Value = StrasseLöschen(Zelle.Value)
Next
End Sub

und hier die Funktion:
Public 
Function StrasseLöschen(Addr As String) As String
Dim Text As String, Text2 As String
Dim i As Long, pt1 As Long, pt2 As Long
Text = Addr
Text = WorksheetFunction.Substitute(Text, "Strasse", "|")
Text = WorksheetFunction.Substitute(Text, "strasse", "|")
Text = WorksheetFunction.Substitute(Text, "Strase", "|")
Text = WorksheetFunction.Substitute(Text, "strase", "|")
Text = WorksheetFunction.Substitute(Text, "Straße", "|")
Text = WorksheetFunction.Substitute(Text, "straße", "|")
Text = WorksheetFunction.Substitute(Text, "Str ", "|")
Text = WorksheetFunction.Substitute(Text, "str ", "|")
Text = WorksheetFunction.Substitute(Text, "Str.", "|")
Text = WorksheetFunction.Substitute(Text, "str.", "|")
Text = WorksheetFunction.Substitute(Text, "Allee", "|")
Text = WorksheetFunction.Substitute(Text, "allee", "|")
Text = WorksheetFunction.Substitute(Text, "Gasse", "|")
Text = WorksheetFunction.Substitute(Text, "gasse", "|")
Text = WorksheetFunction.Substitute(Text, "Weg", "|")
Text = WorksheetFunction.Substitute(Text, "weg ", "|")
For i = 1 To 3 'falls doppelte Leerzeichen vorkommen
Text = WorksheetFunction.Substitute(Text, " |", "|")
Text = WorksheetFunction.Substitute(Text, "| ", "|")
Text = WorksheetFunction.Substitute(Text, "|.", "|")
Next
pt1 = InStr(Text, "|")
'Kein Strassennamen vorhanden
If pt1 = 0 Then
StrasseLöschen = Addr
Exit Function
End If
'Hausnummer löschen
If IsNumeric(Mid(Text, pt1 + 1, 1)) Then
pt2 = InStr(pt1, Text, " ")
Text2 = Mid(Text, pt2)
Text = Left(Text, pt1)
End If
'Strassennamen löschen.
pt1 = InStrRev(Text, " ")
pt2 = InStrRev(Text, Chr(10))
If pt1 > pt2 Then pt2 = pt1
Text = Left(Text, pt2)
StrasseLöschen = Text & Text2
End Function

Gruß, Daniel
Anzeige
AW: Name & Adresse in Zelle: nur Strasse löschen
25.02.2007 07:46:00
Gregor
Hallo Boris, Uwe, Peter und Daniel
Merci für eure Beiträge.
Das Problem ist gelöst.
Schönen Sonntag noch!
Grüsse Gregor

300 Forumthreads zu ähnlichen Themen

@Hans W. Herber: 1 Million Beiträge - Glückwunsch
mpb

Hallo Hans,
wenn ich die URLs richtig interpretiere, haben wir im Forum soeben die Zahl von 1 Million Beiträgen überschritten.
Herzlichen Glückwunsch zu dem Erfolg Deines "Babys"!
Gruß
Martin
07.08.2008 16:23:47
Thread-ID: 1000004
3 Beiträge
Anzeige
Frage - Spalten einfügen & Spalten untereinander z
André

Hallo zusammen!
Ich habe da als blutiger Anfänger in VBA zwei Fragen:
1.) Wie kann ich in meiner Tabelle in jeder zweiten Spalte 4 leere Spalten einfügen lassen?
2.) Dann möchte ich unter den Inhalt der ersten fünf Spalten (A-E) den Inhalt der zweiten 5 Spalten (F-J) usw. In der grö...
08.08.2008 16:00:00
Thread-ID: 1000340
3 Beiträge
Zellen mit Werten aus darüberliegender Zelle fülle
Andreas

Guten Morgen!
Ich hätte folgendes Problem: Ich erhalte automatisiert Exceltabellen, bei denen nicht alle Zellen einer Zeile gefüllt werden, wenn dort der Inhalt einer darüberliegenden Zeile stehen sollte. Zur Weiterverarbeitung muss ich aber diese Zellen mit den dazugehörigen Werten füllen....
11.08.2008 08:59:25
Thread-ID: 1000713
4 Beiträge
Anzeige
Zellen formatieren mit vorangehenden "Nullen"
Addi

Hallo zusammen,
ich habe eine lange Liste, in der in einer Spalte Daten stehen, wo ab und zu vorangehende Nullen nötig sind (mal eine - mal 2...etc.) - und manchmal auch keine.
Wie kann ich diese Zellen formatieren, das die vorangehenden "Nullen" nicht automatisch weggelassen werden?
11.08.2008 10:37:00
Thread-ID: 1000746
8 Beiträge
Makro beenden bei Option "Schliessen"
Jürgen

Hallo Excel-Profis
ich habe folgenden Code in ein Makro eingebaut:
'Drucken mit Vorschau
Application.ActivePrinter = "\\dataserv\HP LaserJet 5L AV auf Ne05:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Preview:=True, Collate:= _
True
Hier wird der Standard-Drucker an...
11.08.2008 12:39:00
Thread-ID: 1000811
9 Beiträge
Zellen prüfen / Ausgabe wenn Bedingung erfüllt
Kay

Hallo,
Ich brauche dringend Eure Hilfe. Unter https://www.herber.de/bbs/user/54533.xls findet Ihr eine Mappe, in welcher in Spalte A
1 für "Kaufen"
0 für " "
-1 für "Verkaufen"
steht. IN SPALTE B SIND BISHER NUR MANUELL ERSTELLTE BEISPIELEINTRÄGE ENTHALTEN, DAMIT ERSICHTLIC...
12.08.2008 09:51:36
Thread-ID: 1001078
3 Beiträge
"Bild von Excel-Kamara" variabel...
Excel-Kamara"

Hi Experten,
einen Bereich (Zellen, Bild, Grafik...) mit der Excel-Kamara "fotografieren" und in eine andere Excelmappe einfügen ist kein Problem.
Bei mir werden Ecxel_Vorlagen ausgefüllt und unter fortlaufender Dateinummer abgespeichert, zB. QR000123, QR000124, QR000125 usw. Alle Datei...
12.08.2008 10:33:28
Thread-ID: 1001116
4 Beiträge
Formel "Text" ROTE Zahl bei Minuswert?
dieter(drummer)

Hi Formelspezialisten,
in Zelle B2 steht dise Formel (funktionert auc h):
=TEXT(I4-D4;"00.0")&" ("&TEXT(I4/D4-1;"0%")&")".
Mein Ziel, dass eine Minuszahl in ROT erscheint. Hab's mit bedingte Formatierung versucht und bekomme es nicht hin.
Gibt es eine Möglichkeit, a...
12.08.2008 10:59:29
Thread-ID: 1001135
7 Beiträge
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige
Archiv - Verwandte Themen
Forumthread
Beiträge
2
7
2
3
1
7
8
2
3
6
11
5
5
8
15
3
7
4
4
3
2
7
4
3
4