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!
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 |
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]
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
Vielen lieben Dank für die schnellen Antworten. Ihr seid suuuper!