Live-Forum - Die aktuellen Beiträge
Datum
Titel
16.05.2024 23:36:08
IFNA Funktion in WENNNV übertragen
16.05.2024 19:31:55
Zellenfarbe beim.Filtern ändern
16.05.2024 19:29:08
Mit INDIREKT() auf andere Tabellenblätter referenzieren
16.05.2024 19:06:22
Monatssumme / Zahlweise / Kalenderjahr
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1964to1968
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
1964to1968
1964to1968
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Formel und Postleitzahl und Ort zu extrahieren

Formel und Postleitzahl und Ort zu extrahieren
20.02.2024 16:10:55
Marcel
Hallo Excel-Forum,

ich habe einen Exceldatei, da sind PLZ und Ort in einer Zelle, aber irgendwie noch die Hausnummern aus einer anderen Zelle rein gerutscht. Ich bin auf der Suche nach einer Formel die alles Links vor der Postleitzahl löscht. Am Ende soll nur PLZ und Ort übrig bleiben. Da die PLZ immer an einer anderen Stelle steht, kriege ich das nicht hin.

So sehen die einzelnen Zellen aus....

2a 76848 Schwanheim, Pfalz
272 66538 Neunkirchen, Saar
5 67433 Neustadt an der Weinstraße
13 55232 Alzey
47 55296 Gau-Bischofsheim
8a 56179 Vallendar
14 - 16 54296 Trier - Kürenz
14 - 16 54296 Trier - Kürenz
Str. 88 - 100 67547 Worms
10a 56070 Koblenz am Rhein

Hat jemand eine Idee?

Danke und LG
Marcel

6
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Formel und Postleitzahl und Ort zu extrahieren
20.02.2024 16:15:16
Fennek
Hallo,

sagt Dir RegEx etwas?

Ohne Prüfung sollte diese Suche wirken: 


RegEx.Pattern = "\d{5}\s\w+"


mfg
AW: Formel und Postleitzahl und Ort zu extrahieren
20.02.2024 16:40:50
Yal
Hallo,

Bei "VBA-nein" gehe ich davon aus, dass RegEx kein Begriff ist.

öffne den Visual Basic Editor mit Alt+F11
füge einen neuen Modul: menü "Einfügen", "Modul"
in diesem Modul (im Kopfbereich steht "[Modul1(Code)]"), füge diesen Code 

Public Function PLZ(ByVal DatString As String) As String

Dim R



    Set R = CreateObject("VBScript.RegExp")

    R.Pattern = "(\d{5})" 'finde 5 Zahlen nacheinander 

    PLZ = R.Execute(DatString)(0).SubMatches(0)

End Function


dann zurück in Excel, füge die Formel
=PLZ(A1)
(in A1 steht den Kauderwelch)
es sollte den jeweiligen Postleitzahl herausgeben.
Dann daraus den Teil und danach extrahieren
=Glätten(Links(A1, Finden(PLZ(A1), A1))
und
=Glätten(Rechts(A1, Finden(PLZ(A1), A1) + 5)

VG
Yal
Anzeige
AW: Formel und Postleitzahl und Ort zu extrahieren
20.02.2024 19:26:05
Marcel
Vielen Dank Fennek, habe von VBA nicht wirklich Ahnung, aber die Formeln weiter unten von den Kollegen funktionieren. Danke!
AW: Formel und Postleitzahl und Ort zu extrahieren
20.02.2024 16:42:47
daniel
Hi
diese Formel sucht nach der ersten Zeichenfolge, die aus 5 Ziffern besteht.
für Deutschland sollte das passen:

=LET(Torg;A1;T;WECHSELN(Torg;" ";"x");x;LÄNGE(T)-4;P;SEQUENZ(x);Pos;AGGREGAT(15;6;P/ISTZAHL(--TEIL(T;P;5));1);TEIL(Torg;Pos;999))

Gruß Daniel
AW: Formel und Postleitzahl und Ort zu extrahieren
20.02.2024 16:50:22
hary
Moin 
=TEIL(A1;SUCHEN(VERWEIS(9^9;1*TEIL(A1&"#";SPALTE(1:1);6));A1);9^9)

gruss hary
Anzeige

Links zu Excel-Dialogen

Frame

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige