Office: Str, Plz und Ort trennen

Hi,
ich habe eine Exceltabelle in der sich in einem Feld Str+Nr+Plz+Ort befindet, in der Form:

Straße Nr
Plz Ort

Nun möchte ich aus diesem einem Feld gerne drei Felder machen, um sie später in Access einbinden zu können. D.h. ich bräuchte die Form:

Feld 1: Straße Nr
Feld 2: Plz
Feld 3: Ort

Str und Nr sind mit einer Leerstellen getrennt (sollen aber so bleiben, Str+Nr sind mit einem Absatz von Plz und Ort getrennt. Plz ist wiederrum mit einer Leerstelle vom Ort getrennt.

Kennt jemand ein geeignetes Makro o.ä. ???

Vielen Dank im Vorraus
P.

:)

 

Hallo,

mehrere Zeilen in Zellen sind durch einen sogenanntes weiches Return getrennt. Also nur ein LineFeed. Per InStr lässt sich diese Zelle nach Vorkommen von vbLf (Konstante für das "Zeichen" LineFeed) durchsuchen.

Dim i, Pos As Long
Dim strStrasse, strPLZ, strOrt, strAktuelleZelle As String
For i=1 To ActiveSheet.UsedRange.Rows.Count
strAktuelleZelle = ActiveSheet.Cells(i, 4).Value
Pos=InStr(1, strAktuelleZelle, vbLf, vbTextCompare)
strStrasse=Left(strAktuelleZelle, Pos - 1)
Pos=Pos + 1
strPLZ=Mid(strAktuelleZelle, Pos, InStr(Pos, strAktuelleZelle, vbLf, vbTextCompare) - Pos)
strOrt=Right(strAktuelleZelle, Len(strAktuelleZelle) - InStr(Pos, strAktuelleZelle, vbLf, vbTextCompare))
Next i

Beim Probieren hab ich festgestellt, dass die Erhöhung von Pos erst nach dem Ermitteln von der Strasse erfolgen darf, da Office sich scheinbar nicht ganz schlüssig ist, ob er ein LineFeed in der Zählung der Position der Zeichen mit einbeziehen soll oder nicht... *Smilie

Macht man das selbe für ein Leerzeichen, dann kann die Zeile in der For-Schleife gleich
Pos=InStr(1, strAktuelleZelle, " ", vbTextCompare)
lauten und die Zeile Pos=Pos+1 kann entfallen...

HTH
Gruß
Jens

[ 09. Oktober 2002: Beitrag editiert von: #jensen ]

 

Hallo zusammen,

ich würde das ganze ohne VBA lösen. Unter der Annahme, dass Straße_Hausnummer durch einen Linefeed=Zeichen(10) vom Ort getrennt ist, funktioniert es mit folgenden Formeln. Die komplette Adresse steht in A1.

1) Straße und Hausnummer:
=LINKS(A1;FINDEN(ZEICHEN(10);A1)-1)

2) PLZ:
=TEIL(A1;FINDEN(ZEICHEN(10);A1)+1;5)

3) Ort:
=TEIL(A1;FINDEN(ZEICHEN(10);A1)+7;999)

Ach ja, ich bin davon ausgegangen, dass die PLZ immer 5-stellig rein numerisch angegeben ist, also ohne Landeskennzeichen.

Hth

Johannes

 

Wow, danke schon einmal für die beiden Tipps. Ich muss mich jedoch als totaler Excel-Laie outen. Sprich: Wie setze ich Eure Tipps um?
Wenn möglich eine kleine detaillierte Angabe der Umsetzung.

Danke
P.

 

Hi,

angenommen, in Zelle A1 steht die Adresse, die du zerlegen willst. Dann schreibst Du in Zelle B1 die 1. Formel. Sie extrahiert den Straßennamen und die Hausnummer. In Zelle C1 schreibst Du die 2. Formel (liefert die PLZ) und in D1 kommt die 3. Formel, die den Ort liefert.

Falls die Adresse in einer anderen Zelle steht, musst Du in den Formeln jeweils das A1 durch die entsprechende Zelle ersetzen.

Um eine längere Liste mit Adressen zu bearbeiten kannst du die drei Formeln kopieren und neben die Adressen einfügen.

CU

Johannes