Live-Forum - Die aktuellen Beiträge
Datum
Titel
29.04.2024 22:12:12
Datei Attribute auslesen
29.04.2024 20:24:42
Datum nach Tag Monat Jahr sortieren
29.04.2024 19:27:41
Farbvorkommen innerhalb einer Produktkategorie
29.04.2024 17:40:50
Formel Index mit Vergleich kombiniert mit Addition
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1908to1912
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
1908to1912
1908to1912
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

PLZ Trennen

PLZ Trennen
17.12.2022 11:12:10
philipp
Guten Morgen,
ich möchte gern die Straß von der Nummer trennen und den Ort von der Postleitzahl.
Anbei mein Muster:
https://www.herber.de/bbs/user/156793.xlsx
Straße steht in Spalte F, nun sollte von rechts angefangen bis zur ersten Leer-Stelle in Spalte G die Nummer kopiert werden.
Ort steht in Spalte i, nun sollte von links angefangen bis zur ersten Leer Stelle die PLZ in Spalte H die kopiert werden.
Kann auch Makro sein, es sind 5.000 Datensätze.
mfg philipp

17
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: PLZ Trennen
17.12.2022 11:34:14
RPP63
Moin!
Erst mal etwas für aktuelle Excel-Versionen.
Sind spillende Formeln, es werden tatsächlich nur vier Formelzellen benötigt:
 EFGHIJ
3 StrasseHaus-Nr.PLZOrt 
4HildstraßeHildstraße 114123941239 MönchengladbachMönchengladbach
5MercatorstraßeMercatorstraße 17174054540545 DüsseldorfDüsseldorf
6MorianstraßeMorianstraße 14F14F4623846238 BottropBottrop
7Elsa-Brändström-StraßeElsa-Brändström-Straße 775207052070 AachenAachen
8Aachener StraßeAachener Straße 2182184022340223 DüsseldorfDüsseldorf
9MecklingsbankMecklingsbank 11114527945279 EssenEssen

ZelleFormel
E4=TEXTVOR(F4:F66;" ";-1)
G4=TEXTNACH(F4:F66;" ";-1)
H4=LINKS(I4:I66;5)
J4=TEIL(I4:I66;6;9^9)

Mit Deinem "alten" Excel geht es auch, halt nur kompliziert (Formeln ziehen):
 EFGHIJ
3 StrasseHaus-Nr.PLZOrt 
4HildstraßeHildstraße 114123941239 MönchengladbachMönchengladbach
5MercatorstraßeMercatorstraße 17174054540545 DüsseldorfDüsseldorf
6MorianstraßeMorianstraße 14F14F4623846238 BottropBottrop
7Elsa-Brändström-StraßeElsa-Brändström-Straße 775207052070 AachenAachen
8Aachener StraßeAachener Straße 2182184022340223 DüsseldorfDüsseldorf

ZelleFormel
E4=LINKS(F4;FINDEN("#";WECHSELN(F4;" ";"#";LÄNGE(F4)-LÄNGE(WECHSELN(F4;" ";""))))-1)
G4=TEIL(F4;FINDEN("#";WECHSELN(F4;" ";"#";LÄNGE(F4)-LÄNGE(WECHSELN(F4;" ";""))))+1;9)
H4=LINKS(I4:I66;5)
J4=TEIL(I4:I66;6;9^9)

Gruß Ralf
Anzeige
kleine Korrektur
17.12.2022 11:37:20
RPP63
H4 und J4 hatten noch die Spillformeln.
Richtig für ältere Excel-Versionen:
H4: =LINKS(I4;5)
J4: =TEIL(I4;6;9^9)
(aber das sind ja sicherlich auch Dir bekannte Funktionen)
AW: kleine Korrektur
17.12.2022 11:42:00
philipp
Guten Morgen Ralf,
danke für das Beispiel.
Werde direkt testen.
mfg philipp
Die korrekte Reihenfolge ist …
17.12.2022 12:13:10
RPP63
… erst testen, dann antworten, Philipp. ;)
So setzt Du den Thread unnötigerweise auf unerledigt.
Außerdem sind meine (kopierfähigen) Formeln ja nichts, was länger als 5 Minuten zum testen braucht.
Gruß Ralf
leider Fehler
17.12.2022 13:23:14
philipp
Hallo Ralf,
beim Ort klappt es aber bei der Straße nicht.
https://www.herber.de/bbs/user/156794.xlsx
mfg philipp
Anzeige
AW: PLZ Trennen
17.12.2022 13:45:18
Daniel
Hi
Solange du in Deutschland bleibst, ist die PLZ 5 Zeichen lang und der ORT steht an der Position 7, dh das kannst du fest eingeben, wie die Kollegen schon gezeigt haben:
PLZ: =Links(I4;5)
Ort: =Teil(I4;7;999)
Etwas aufwendiger wirds bei der Hausnummer.
Wenn ich mir deine Daten anschaue, wirst du das beste Ergebnis erzielen, wenn du nach der ersten Ziffer suchst (bspw dann, wenn der Hausnummernbuchstabe nicht direkt auf die Ziffern folgt, oder beim
Postfach)
Position der ersten Ziffer: =Aggregat(15;6;Finden({1;2;3;4;5;6;7;8;9;0};F4);1)
Hausnummer: =TEIL(F4;AGGREGAT(15;6;FINDEN({1;2;3;4;5;6;7;8;9;0};F4);1);9)
Straße: =Links(F4;AGGREGAT(15;6;FINDEN({1;2;3;4;5;6;7;8;9;0};F4);1)-2)
Gruß Daniel
Anzeige
Danke Daniel und Ralf jetzt klappt auch Straße -)
17.12.2022 15:47:20
philipp
herber.de/excelformeln/src/call.pl?idx=457
17.12.2022 16:03:58
lupo1
AW: herber.de/excelformeln/src/call.pl?idx=457
17.12.2022 16:24:11
philipp
Hallo,
was bedeutet das: herber.de/excelformeln/src/call.pl?idx=457
mfg philipp
AW: herber.de/excelformeln/src/call.pl?idx=457
17.12.2022 17:02:29
Daniel
Das soll ein Link sein zu einer Seite, die mögliche Lösungen für dein Problem enthält.
Manche Foristen haben sich selbst das Ziel gesetzt, möglichst viele Information schon in die Betreffzeile zu schreiben um dann ggf nichts mehr in den Beitrag selbst schreiben zu müssen.
Allerdings wird in der Betreffzeile ein LinkText nicht automatisch in einen anklickbaren Link umgewandelt, wie es im BeitragText passieren würde.
Du musst also denn Linktext kopieren und in die Suchzeile deines Browsers einfügen um auf die Seite zu gelangen.
Gruß Daniel
Anzeige
Schön, dass Du das Erklären übernimmst ...
17.12.2022 19:01:44
lupo1
... aber irgendwie werden die Frager immer unbedarfter.
Und die Antworter immer arroganter
17.12.2022 19:05:02
Daniel
...
ihr habt euch aber lieb :-(. owT
17.12.2022 19:14:15
Rudi
Hier ist es eindeutig, wer angefangen hat. :-)
17.12.2022 20:37:50
lupo1
AW: PLZ Trennen
17.12.2022 17:09:28
Herbert_Grom
Hallo Philipp,
warum willst du denn die Hausnr von der Straße trennen? Die gehören doch untrennbar zusammen und beim sortieren werden doch die Hsnrn korrekt einsortiert.
Servus
AW: PLZ Trennen
17.12.2022 18:24:58
snb
PLZ: In G4

=TRIM(RIGHT(SUBSTITUTE(RIGHT(F4;6);" ";"        ");8))
Strraße In H4

=TRIM(SUBSTITUTE(F4;G4;""))
Nur 2 'Fehler"
Postfach xx yy
Straße xx a
Mann sollte 365 'Vergleichen' mit ähnliche effiziente Formeln.

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige