Straße, Hausnummer, PLZ und Ort in Spalten aufteilen

[daste]

Hallo,

ich versuche bisher vergeblich meine eportierte Adressspalte auf einzelne Spalten aufzuteilen. Die Funktion "Text in Spalten" bringt mir leider nichts.

Mein Programm wieß mir in einer Zelle jeweils ohne Komma's die Adresse aus.

Beispiel:

Musterstr. 15 89500 Muster
bzw. machmal nur
89599 Irgendwo

da die Datenbank nicht sauber gepflegt wurde.

Kann mit jemand über Formeln weiterhelfen wie ich die einzelne Zelle auf Straße inkl. Hausnummer, Postleitzahl und Ort in Spalten aufteilen kann. Die bisherigen Formeln mit Links... haben bei der den Adressen mit fehlender Straße nicht funktioniert.

Gibt es vielleicht über den Befehl "rechts" die Möglichkeit den Ort und die Postleitzahl zu selektieren und eine Spalte mit dem Restergebnis auszugeben?

Für eure Hilfe bin ich dankbar.

Grüße
Daste

[WillWissen]

Hi,

zeige uns bitte anhand eines Tabellenausschnittes ein paar relevante Datensätze. So etwa 5 reichen aus, sie sollten aber nach Möglichkeit alle Eventualitäten zeigen.

Bitte keinen ScrShot posten! Lies mal diesen Beitrag: http://www.clever-excel-forum.de/thread-47.html

[steve1da]

Hola,

vor allem lies mal:

http://www.clever-excel-forum.de/thread-3037.html

Ist jetzt das dritte Forum!

Gruß,
steve1da

[daste]

Hier mein Ausschnitt

Damit dürfte jede Eventualität abgedeckt sein.

Tabelle1

	A	B	C	D
2	Objektanschrift	Straße mit Hausnummer	Postleitzahl	Ort
3	Ernst-Abbe-Straße 16 89979 Ravensburg
4	Edisonallee 89888 Memmingen
5	Ravensburger Straße 18 89999 Muster
6	Edisonallee 19 89284 Testort
7	87777 lmmerhin
8	Hirschstraße 17-19 82223 Ort

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8



http://www.ms-office-forum.net/forum/sho...p?t=339030

http://www.office-fragen.de/index.php/to...235.0.html

[shift-del]

Hallo

Arbeitsblatt mit dem Namen 'Tabelle1 (2)'
	A	B	C	D	E
1	Objektanschrift	Straße mit Hausnummer	Postleitzahl	Ort
2	Ernst-Abbe-Straße 16 89979 Ravensburg	Ernst-Abbe-Straße 16	89979	Ravensburg	22
3	Edisonallee 89888 Memmingen	Edisonallee	89888	Memmingen	13
4	Ravensburger Straße 18 89999 Muster	Ravensburger Straße 18	89999	Muster	24
5	Edisonallee 19 89284 Testort	Edisonallee 19	89284	Testort	16
6	87777 lmmerhin		87777	lmmerhin	1
7	Hirschstraße 17-19 82223 Ort	Hirschstraße 17-19	82223	Ort	20

Zelle	Formel
B2	=GLÄTTEN(LINKS(A2;E2-1))
C2	=VERWEIS(9^99;--TEIL(A2;SPALTE(2:2);6))
D2	=TEIL(A2;E2+6;9^9)
E2	=SUCHEN(C2;A2)

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

[daste]

Hammer!!!


Vielen Dank für die schnelle Antwort und die damit für mich verbundene Arbeitserleichterung.

[Rabe]

Hi Detlef,

warum kommt bei dieser Formel

=VERWEIS(9^99;--TEIL(A2;SPALTE(2:2);6))

die PLZ raus?

Wenn ich nur den Teil()-Teil der Formel nehme, kommen doch die ersten 6 Zeichen der Straße.

Ich weiß, ich verstehe den VERWEIS nicht.

[shift-del]

Hallo Ralf

Ändere die Formel mal so ab:

Code:

=VERWEIS(9^99;--TEIL(A2;SPALTE(A2:Z2);6))

Und dann in die Formelauswertung.

[Rabe]

Hi,

Und dann in die Formelauswertung.

da sehe ich, daß alle einzelnen Teile der Adresse durchgegangen wird und am Ende plötzlich die PLZ da steht.

Mein Verständnis der Formel in Worten:
Wird beim Verweis mit der 9^99 verglichen und durch das -- ergeben alle anderen Teile einen Fehler und es wird das erste ohne Fehler genommen? Aber warum wird dann nicht die Hausnummer übernommen?
Weil es 6 Zeichen sein müssen, die ohne Fehler sind? PLZ hat ja aber 5.

[shift-del]

da sehe ich, daß alle einzelnen Teile der Adresse durchgegangen wird und am Ende plötzlich die PLZ da steht.

Die Formel zerlegt den Text in 6-Zeichen-Abschnitte startend bei Pos. 1, 2, 3, etc.
Das Doppelminus erzwingt eine Typumwandlung von Text in Zahl. Bei Text-Zahlen gelingt das. Ansonsten Fehler.
VERWEIS() hat den Vorteil Fehler zu ignorieren und die Matrix von hinten nach vorn zu durchsuchen.
Beim Vergleich mit 9^99 wird damit immer die letzte Zahl ermittelt und das ist die PLZ.
Wegen der 6: Ich habe zuerst mit 5 probiert. Kein Erfolg. Dann auf 6 geändert. Passt. Nicht weiter drüber nachgedacht. :)