Office: (Office 2013) Komplette Adresse in einer Zelle

Hallo zusmmen,

Ich habe eine Liste mit knapp 2000 Datensätzen, bei denen die komplette Firmenanschrift, also Strasse, Hausnummer, Postleitzahl und Ort in einer Zelle stehen. Die einzelnen Teile sind nur durch ein Leerzeichen getrennt. Wie kann ich die Postleitzahl und den Ort aus der Zelle ausschneiden (also in der Zelle löschen) und in eine leere Spalte rechts nebenan einfügen?

Ich habe bereits mit TEIL, RECHTS, LINKS, FINDEN etc. herumexperementiert, es aber bislang noch nicht geschafft. Wäre ganz toll, wenn mir jemand helfen könnte.

Gruß,
Andreas

:)

 

Hallo,

wir sind doch keine Hellseher !!!
Macht das die Kälte, oder wieso denkt jeder, dass er nur die Hälfte der Anfrage schreiben muss, und jeder weiß sofort was los ist ?

wie sieht die Zelle aus ... was soll wohin ... mein Gott ... ist dann denn so schwer ??? *mad.gif*

 

\@Sabina: Wetten, dass jetzt genau ein Beispieldatensatz kommen wird?

@andreas: Sabinas Einwand ist vollkommen richtig, eine sinnvolle Antwort ist da eher nicht möglich. Ich versuche mich dennoch mal als Hellseher. Was du daraus machst, bleibt dir überlassen:

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C
1	Teststraße 11, 12345 Ortschaft	12345	Ortschaft
2	Blabla 12 06425 Ort	6425	Ort

Zelle	Formel
B1	=VERWEIS(9^9;1*TEIL(WECHSELN(A1;" ";"#")&"#";SPALTE(A1:XC1);5))
C1	=RECHTS(A1;LÄNGE(A1)-FINDEN(B1;A1)-LÄNGE(B1))
B2	=VERWEIS(9^9;1*TEIL(WECHSELN(A2;" ";"#")&"#";SPALTE(A2:XC2);5))
C2	=RECHTS(A2;LÄNGE(A2)-FINDEN(B2;A2)-LÄNGE(B2))

 

Hallo Sabina,

Du hast natürlich Recht - ich gelobe Besserung und werde das Nächste Mal ein ordentliches Beispiel beifügen. MisterBurns hat seine hellseherischen Fähigkeiten vollkommen richtig eingesetzt - genau so etwas hatte ich gesucht ;-)

LG
Andreas

 

Hallo Berni,

vielen Dank für die schnelle Antwort - genau das hatte ich gesucht. Deine Lösung funktioniert :-)

Gibt es auch eine Möglichkeit, die ausgelesene Postleitzahl und den Ort in der Zelle zu löschen (also in Spalte A)?

VG,
Andreas

 

Das würde ich dann so angehen:

Arbeitsblatt mit dem Namen 'Tabelle2'
	A	B	C	D
1	Teststraße 11, 12345 Ortschaft	Teststraße 11,	12345	Ortschaft
2	Blabla 12 06425 Ort	Blabla 12	6425	Ort

Zelle	Formel
B1	=LINKS(A1;LÄNGE(A1)-((LÄNGE(C1)=4)+LÄNGE(C1))-LÄNGE(D1)-1)
C1	=VERWEIS(9^9;1*TEIL(WECHSELN(A1;" ";"#")&"#";SPALTE(A1:XD1);5))
D1	=RECHTS(A1;LÄNGE(A1)-FINDEN(C1;A1)-LÄNGE(C1))
B2	=LINKS(A2;LÄNGE(A2)-((LÄNGE(C2)=4)+LÄNGE(C2))-LÄNGE(D2)-1)
C2	=VERWEIS(9^9;1*TEIL(WECHSELN(A2;" ";"#")&"#";SPALTE(A2:XD2);5))
D2	=RECHTS(A2;LÄNGE(A2)-FINDEN(C2;A2)-LÄNGE(C2))

Das ist natürlich ein bisschen gefährlich weil automatisierte Adressbearbeitung nie ganz zuverlässig ist. Du müsstest dann fast durchschauen, ob nicht etwas irrtümlich gelöscht wurde. Bei 2000 Datensätzen nicht lustig, aber in annehmbarer Zeit machbar.

Je nachdem, ob die Straße und PLZ mit Beistrich getrennt sind, würde ich dann den Beistrich im Ergebnis einfach mittels Suchen/Ersetzen entfernen.

 

Oder alternativ den Beistrich mittels

Arbeitsblatt mit dem Namen 'Tabelle2'
	B
1	Teststraße 11

Zelle	Formel
B1	=WECHSELN(LINKS(A1;LÄNGE(A1)-((LÄNGE(C1)=4)+LÄNGE(C1))-LÄNGE(D1)-1);",";"")

entfernen.

 

Hi Berni,

vielen Dank für die schnelle Antwort - ich habe auch diese Formel in meiner Tabelle getestet - funktioniert perfekt! Ich muss mir das jetzt noch einmal in Ruhe anschauen, damit ich es auch verstehe.

Vielen Dank - das hat mir wirklich SEHR geholfen *mrcool

 

Gerne auch erklärt:
Code:

Code:

Nimm Links von Zelle A1

Code:

Länge von A1
abzüglich Länge PLZ
abzüglich Länge Ort
abzüglich 1 (für das Leerzeichen)
=====================
Ergibt die Länge, die von der Länge A1 abgezogen werden soll

Noch als Erläuterung des Teils
Code:

Wenn Länge C1 = 4 --> Ergebnis = WAHR = 1
Wenn Länge C1 = 4 --> Ergebnis = FALSCH = 0
=====================
Ergibt immer eine Länge von 5 Stellen für die PLZ, da ja die führende 0 der PLZ nicht übernommen wird.