Teile aus Zelltext an unterschiedlicher Stelle auslesen möglich?

[Giftfee]

Hallo zusammen,
ich habe einen großen Datensatz erhalten, der u.a. Adressen enthält. Diese sind allerdings innerhalb einer Zelle hinterlegt (Beispielstraße 5 12345 Beispielort). Ich möchte diesen Datensatz nach der PLZ sortieren. Da durch die unterschiedliche Länge der Straßennamen die PLZ stets an einer unterschiedlichen Stelle steht, komme ich mit der Funktion =Teil(A1;6;10) nicht weiter. Gibt es eine Funktion, die eine funfstellige Zahlenabfolge erkennt, die dann ausgelesen werden kann?

Über Hilfe bin ich sehr dankbar!

[Peter]

Hallo,

nur mit dem einen Beispielswert getestet:

=TEIL(A1;MAX(WENN(ISTZAHL(--TEIL(A1;SPALTE(1:1);1));SPALTE(1:1)))-4;5)

Die Formel muss mit Strg-Shift-Enter abgeschlossen werden.

Das ist eine Matrixformel - die Berechnung dürfte bei einem großen Datensatz vermutlich einige Zeit dauern. Wenn das eine einmalige Geschichte ist sollte das aber nicht unbedingt ein Problem sein.

Nach der Berechnung:

- den Bereich mit den Formeln markieren
- mit Strg-C in die Zwischenablage kopieren
- rechte Maus - Inhalte einfügen - Werte - Ok

Dadurch werden die Formeln im markierten Bereich in Werte umgewandelt.

Alternativ die folgende benutzerdefinierte Funktion die ich von 4 auf 5 geändert habe.

Code:

Function Zahl_5stellig(strText As String) As Variant
'sucht die erste Zahl mit 4 Ziffern in einem String
Dim lng As Long
Application.Volatile
Zahl_5stellig = ""
If strText Like "*#####*" Then 'es ist eine 4-stellige Zahl vorhanden
    For lng = 1 To Len(strText) - 4 ' jetzt Zeichenweise
        If Mid(strText, lng, 5) Like "#####" Then
            Zahl_5stellig = CInt(Mid(strText, lng, 5))
            Exit For
        End If
    Next lng
End If
End Function

Der Aufruf erfolgt mit =Zahl_5stellig(A1)

Auch diese Funktion wurde nur an dem einen Beispielssatz getestet.

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C
1	Beispielstraße 5 12345 Beispielort	12345	12345

Zelle	Formel
B1	{=TEIL(A1;MAX(WENN(ISTZAHL(--TEIL(A1;SPALTE(1:1);1));SPALTE(1:1)))-4;5)}
C1	=Zahl_5stellig(A1)

Achtung, Matrixformel enthalten!

Die geschweiften Klammern{} werden nicht eingegeben.

Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.

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

[BoskoBiati]

Hallo,


guckst Du hier!

Angepasst auf Deine Version:


[html]

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E
1	abcd 5 12345 dfsdag			12345	12345
2	abcd 12345 dfsdag			12345	12345
3	abcd 5 1234 dfsdag

Zelle	Formel
D1	{=TEIL(LINKS(A1;MAX(ISTZAHL(TEIL(WECHSELN(A1;" ";"#");SPALTE(1:1);1)*1)*SPALTE(1:1)));VERGLEICH(1;ISTZAHL(TEIL(WECHSELN(A1;" ";"#")&0;SPALTE(1:1);5)*1)*1;0);LÄNGE(A1))}
E1	{=WENNFEHLER(VERWEIS(9^9;1*TEIL(A1;MIN(WENN(ISTZAHL(1*TEIL(WECHSELN(A1;" ";"#");SPALTE(1:1);5));SPALTE(1:1)));SPALTE(1:1)));"")}

Achtung, Matrixformel enthalten!

Die geschweiften Klammern{} werden nicht eingegeben.

Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.

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

[/html]

[Fennek]

Hallo,

es gibt so wenige Einsatzmöglichkeiten für RegEx, deshalb alternatv:

Code:

Function RegEx_PLZ(Tx As String)
Dim RegEx As Object

Set RegEx = CreateObject("vbscript.regexp")

RegEx.Pattern = "\s(\d{5})\s"
RegEx_PLZ = Trim(RegEx.Execute(Tx)(0))
End Function

mfg

[Giftfee]

Vielen lieben Dank für die schnellen Antworten. Ihr seid suuuper!