Ich habe eine lange Liste mit Straßen und deren Nummern innerhalb einer Zelle. Wie kann ich diese voneinander trennen?
Das Schwierige für mich ist eher, dass es nicht einheitlich ist. Es gibt: str. , straße , Strasse , Weg , Ring , usw. und darauf folgt dann immer eine Nummer,wie z.B. 3a oder App320
7 Antworten
zu meiner AW auf diese Frage:
www.gutefrage.net/frage/excel-funktion-wie-stellen
hat Oubyi eine MakroFunktion zugefügt, die vllt Dein Problem löst. Schau Dir das mal an! ist Spitze!
allerdings Straße 5 - 7 kann es glaube ich auch nicht, oder III oder 7a oder 42RG
Solche Schreibweisen sind m.W. nicht normgerecht.
ich hab aber doch eine ganz konventionelle Formel gebaut, die auch solche Sachen wie Platz des 17.Juni 12/II auszuwerten gestattet (max. 5 Stellen hier, ggf noch eine Zeile davor einfügen, da bestünde dann die "Gefahr", dass ohne Hausnummer die 7 vor Juni als Grenze missverstanden wird - in der Praxis kaum relevant, ich erwähn das nur der Vollständigkeit halber, 5.Arr.23 genauso. 12/III oder 101/II geht -unerweitert- schon nicht mehr, ebenso nicht 17 - 19 das kann aber häufiger auftreten. 12/IV.
Für 17 - 19 könnte man das in ein ein Wechseln(..Fo..;" ";"") einfügen, Glätten erfüllt diesen Zweck auch, sogar kürzer, weiß nicht, ob der Aufwand lohnt, kenne Deine Adressliste nicht)
in i1: Platz des 17.Juni 12/II
in j1: =WENN(ISTZAHL(WERT(TEIL(I1;LÄNGE(I1)-4;1)));RECHTS(I1;5);WENN(ISTZAHL(WERT(TEIL(I1;LÄNGE(I1)-3;1)));RECHTS(I1;4);WENN(ISTZAHL(WERT(TEIL(I1;LÄNGE(I1)-2;1)));RECHTS(I1;3);WENN(ISTZAHL(WERT(TEIL(I1;LÄNGE(I1)-1;1)));RECHTS(I1;2);WENN(ISTZAHL(WERT(RECHTS(I1;1)));RECHTS(I1;1);"")))))
in K1: =LINKS(I1;LÄNGE(I1)-LÄNGE(J1))
der 17.Juni war ein Feiertag (besser Gedenktag), der an einen Aufstand in Ostdeutschland erinnert, da gibts danach benannte Strassen, Plätze. Ist durch den 3. Oktober ersetzt worden, aber Oktober ist lang genug, um mehr auszuwertende Stellen zuzulassen. Auch andere Ereignisse werden manchmal so verwendet.
Danke sowas ähnliches habe ich gesucht.
Der kommentar von dem ist der Hammer xD
Die ganzen Handstände mit römischen Hausnummern, die es kaum gibt, haben wir jetzt weggelassen. Und in der Innenstadt von Mannheim wohnt sowieso keine Sau.
Häts mir denken können. Kommt leider sehr oft #WERT! raus. aber danke
Mir fällt für VBA als Alternative zur Suche nach Leerzeichen noch ein, von hinten her die Zahlen abzugreifen. Grober Ansatz (kann jemand sicher eleganter formulieren)
Sub hausnummer()
Dim i As Integer
Dim j As Integer
Dim hausnummer As String
i = 1
j = 1
While Tabelle1.Cells(i, 1).Value <> ""
For j = 1 To 3
hausnummer = Right(Tabelle1.Cells(i, 1).Value, j)
If IsNumeric(hausnummer) Then Tabelle1.Cells(i, 3).Value = hausnummer Else hausnummer = Right(hausnummer, j - 1)
Next
Tabelle1.Cells(i, 2).Value = Left(Tabelle1.Cells(i, 1).Value, Len(Tabelle1.Cells(i, 1).Value) - Len(hausnummer))
j = 1
i = i + 1
Wend
End Sub
Das schlägt aber auch bei bestimmten Fällen nicht an (z.B. 12a, weil das eben nicht nummerisch ist) bzw. müsste man noch etwas einbauen für die Fälle ohne Hausnummer.
Danke Ninombre!
Da Hausnummern und Straßen immer durch ein Leerzeichen getrennt wurden, habe ich es jetzt so gelöst, dass ich es zuerst die Spalte als Text in Spalten getrennt habe->bei jedem Leerzeichen trennen-> ausführen
Dann durchsortieren lassen habe.
Die Obersten zeilen sahen dann zB so aus:
Bauer- |Hermann- |Weg |3 | a| App | 330
Von da an wurden sie nurnoch kürzer...
dann habe ich rechts zwei Spalten eingefügt->
erste: =B2&B3&B4
zweite: =B5&" "&B6&" "&B7&" "&B8 (" "nur um Leerzeichen einzufügen)
Das musste ich halt nach unten hin immer wieder anpassen.
Am Ende nurnoch durch werte ersetzen und original überschreiben FERTIG
auch schön, hab für meine Lösung offensichtlich viel zu lang gebraucht!
Bei Deiner Lösung musst Du allerdings sehr viel klicken: ich empfehle Dir, die typischsten Kombinationen in mehreren Titelzeilen zu hinterlegen (die Zellen, auf die sich die Fo bezieht, farbig unterschiedlich zu hinterlegen)
Ach so, dirch das Sortieren kannst du die Formeln runterziehen, auch eine gute Lösung!
Jipp, versuchs möglichs simpel zu halten ;)
wenns kürzer sein darf:
i = 0
While Cells(i, 1).Value <> ""
i = i + 1
arr = Split(Cells(i, 1), " ")
anz = UBound(arr)
Cells(i, 3) = arr(anz)
arr(anz) = ""
Cells(i, 2) = trim(Join(arr))
Wend
Also ein Work-Around wäre folgender:
1) Du formatierst die Excel in eine CSV-Datei um
2) Öffne die CSV-Datei mit einem "mätigeren" Tool wie zb. Notepad++ (kostenlos)
3) Öffne die "Replace"-Funktion. Diese öffnest du mit: "Strg+F" und dann im Reiter auf "Replace"
4) Jetzt schreibst du bei "Find what" ein "," rein und bei "replace with" ein ";"
WICHTIG: bei "Find what" muss das Trennzeichen rein - also was eine Straße von der anderen trennt.
5) Speicher das Dokument und öffne es wieder mit Excel.
Jetzt hast du alles in seperaten Zellen.
Wenn du wieder eine Excel-Datei daraus haben willst, kannst du selbstverständlich das ganze wieder als .xlsx oder .xls abspeicher.
Das gibt es auch ganz normal als Funktion in Excel, Heißt: "Text für Spalten"
Da kann man sich die einzelnen Spalten auch markieren...
Stimmt! Die Funktion heist allerdings "Text in Spalten". Hatte ich komplett vergessen.
Hier findet Ihr eine gute Anleitung dazu : http://www.online-excel.de/excel/singsel.php?f=64
Das geht nur manuell.
Hilft vielen Leuten bestimmt noch weiter. Meine Liste hatte ja jetzt auch nur 2300 Zeilen, somit ging es doch schnell genug durch das sortieren.
Aber wieso schreibst du 17.Juni 12/II ??