ich habe zwar nun schon eine ganze Zeit hier im Forum „gewühlt“ aber leider nichts passendes (bzw. für mich verständliches) gefunden - wie z.B. das hier
in VBA gibt es die Funktion split
a = Split(Cells(a14).Value, " ")
die einen String in mehrere Teile zerschneidet
und in ein Array abspeichert._
Mein „Problem“ ist, dass ich in über 6000 Zeilen Straße & HausNr. in einem habe. Das müsste jedoch auf zwei Spalten aufgeteilt und somit getrennt werden, was aus dem Grund nicht so einfach ist, da es auch Straßennamen gibt, die keine od. mehrere Leerzeichen vor der HausNr. enthalten und das Ergebnis somit in mehrere Spalten landet od. gar nicht getrennt werden. Ist es mittels Formel irgendwie möglich, den Text so zu trennen, dass automatisch eine neue Spalte verwendet wird, wenn es sich um eine Zahl handelt.
Beispiel:
Goestingerstr. 132A
Eggenbergerguert.39/
Muenzgrabenguertel24
Huettenbrennerg.49
Wiener Str. 162A/16
Hans-Resel-G.27/110
Kaerntner Str. 218/4
Kaerntner Str.212/47
Gerh.Hauptm.Str.30/1
ich hoffe wirklich, dass das Thema nicht schon einmal beantwortet wurde…
anders angehen…
also nachdem du denke ich mal maximal 4 Zeichen für die Hausnummer haben wirst würde ich Dir einfach raten die letzten 4 Zeichen zu überprüfen und danach dann abzutrennen…
im prinzip ist das eine verschachtelte Wenn-Formel…
WENN(ISTZAHL(WERT(TEIL(A1;LÄNGE(A1)-3;1)));TEIL(A1;LÄNGE(A1)-3;4); nächstes wenn ; „keine Zahl gefunden“)
damit würde dann in den letzten 4 Ziffern nach einer Zahl gesucht und wenn diese gefunden würde der Reststring als Hausnummer ausgegeben werden. Eine Hausnummer „150a“ wäre dann auch noch nachvollziehbar.
Das WERT() ist wichtig, weil Zahl sonst als Text interprätiert würde…
hmmm, also ich bin ja kein genie - habs aber zumindest nun probiert.
mit der ganze formel kommt die meldung „zu viele argumente“, lasse ich das "nächstes wenn ; „keine Zahl gefunden“ weg und schreibe nur die Formel:
=WENN(ISTZAHL(WERT(TEIL(AG2;LÄNGE(AG2)-3;1)));TEIL(AG2;LÄNGE(AG2)-3;4))
komme ich auf folgende ergebnisse:
Unter dem großen Menü „Daten“ bietet Excel bereits die Möglichkeit, die Daten aus einer Spalte in zwei oder mehr Spalten aufzuteilen. Dann braucht man keine Formeln.
Daten -> Text in Spalten -> Schritt1 mit „weiter“ bestätigen
-> Schritt2 als Trennzeichen „Leerzeichen“ eingeben, dann sieht man schon mal ne Vorschau
-> Schritt3 die Formate der Spalten nicht ändern, nur „fertig stellen“
Das Ergebnis ist dann zwar noch nicht optimal, aber über das Durchsuchen nach Zahlen
=wenn(istzahl(…);…;wenn(istzahl…
lassen sich sicherlich schon mal die Hausnummern per Formel in eine Spalte bringen - notfalls manuell über Autofilter gucken, bei welchen Hausnummern dies nicht passiert ist, könnte ich mir z.B. bei „34/36“ vorstellen.
Und anschließend
entweder
mit den Formeln RECHTS oder LINKS in Verbindung mit * aus der Ursprungszelle die Buchstaben herausziehen.
* steht für eine mir nicht bekannte Formel zum zählen, wieviele Zeichen in einer Zelle sind.
oder
über VERKETTEN und ISTTEXT (nicht ISTZAHL oder ISTWERT) die Straßennamen zusammenfügen.
ja… du hast vergessen mitzudenken…
ein „nächstes Wenn“ erfordert von Dir, dass Du die Formel weiterbastelst für die Fälle mit weniger als 4 Zahlen am Ende…
Aber ich denke die Lösung auf der excelformel-Seite ist einfacher für dich
zuerst einmal danke für die antworten! mitdenken tue ich schon, soweit es geht natürlich - nur für einen „laien“ ist es manchmal leider doch ein bisschen spanisch…
die formel auf der excelformeln.de seite funktioniert nicht so richtig. es wird bei der ersten formel straße & nr. übernommen, wenn ich die zweite formel in c1 für die nr. anwende, bleibt das feld leer - kein ergebnis!!!
die formel auf der excelformeln.de seite funktioniert nicht so
richtig. es wird bei der ersten formel straße & nr.
übernommen, wenn ich die zweite formel in c1 für die nr.
anwende, bleibt das feld leer - kein ergebnis!!!
Hi Mario,
die Formeln von excelformeln.de funktionieren schon, letztlich immer, aber halt nicht bei deinem chaotischen Spaltenaufbau, mal nix als Trennzeichen, mal ein Punkt, mal einige Leerzeichen, wahlweise mit Punkt davor.
Möglicherweise stehen in der Liste auch Adressen wie Wiesenstraße o.Nr. o.ä.
Wenn dem nicht so ist könnte man pro Eintrag nach dem ersten Zeichen „fahnden“ was eine Ziffer ist, und dementsprechend dann trennen.
Vielleicht machbar in Excel direkt mit Formeln, aber mir zu stressig,
ich kann dir anbieten, eine Vba-Lösung zu basteln.
Wenn dem nicht so ist könnte man pro Eintrag nach dem ersten
Zeichen „fahnden“ was eine Ziffer ist, und dementsprechend
dann trennen.
Vielleicht machbar in Excel direkt mit Formeln, aber mir zu
stressig,
ich kann dir anbieten, eine Vba-Lösung zu basteln.
Gruß
Reinhard
hmm, ja leider - aber die daten habe ich so chatoisch bekommen und müsste diese (um sie dann entprechend weiterverarbeiten zu können eben alle händisch trennen). Es muss ja nicht 100%ig funktionieren, aber wenn ich mir bei über 6000 adressen schon mal ein wenig ersparen könnte, wäre ich dir sehr dankbar über so eine lösung (vorausgesetzt, es ist nicht all zu umständlich). wiegesagt ich bin kein fachmann auf dem gebiet!!
also…
ich hab weil ich ja so ein netter Mensch bin und so auf Sternchen stehe (die ich eh nicht bekomme ) die Formel komplettiert…
Bei mir stehen die Strassen in Spalte A (Im Beispiel in Zeile 1)
Formel für Spalte B(1)
=WENN(C1=„keine Zahl gefunden“;A1;LINKS(A1;LÄNGE(A1)-LÄNGE(C1)))
Formel für Spalte C(1)
=WENN(ISTZAHL(WERT(TEIL(A1;LÄNGE(A1)-3;1)));TEIL(A1;LÄNGE(A1)-3;4);WENN(ISTZAHL(WERT(TEIL(A1;LÄNGE(A1)-2;1)));TEIL(A1;LÄNGE(A1)-2;4);WENN(ISTZAHL(WERT(TEIL(A1;LÄNGE(A1)-1;1)));TEIL(A1;LÄNGE(A1)-1;4);WENN(ISTZAHL(WERT(TEIL(A1;LÄNGE(A1);1)));TEIL(A1;LÄNGE(A1);4);„keine Zahl gefunden“))))
Prima, das sieht ja schon mal echt spitze aus - haut aber trotzdem noch nicht ganz hin. Zahlen vor den Schrägstrichen werden nicht alle erkannt.
z.B:
5/31 - hier wird nur 31 erkannt. bei 56/3 funktionierts komischerweise wieder, hmmm…?? (wenn ich statt den ersten beiden „-3“ ein „-4“ setze, schreibt es auch das „5/“ in Spalte c allerdings lässt er die „5“ in der spalte b stehen…
bei 211/10 - wird z.b. nur 10 hingeschrieben
bei 162A/16 - wird z.b. nur 16 hingeschrieben
aber ich würde einmal sagen - 40% der Adressen werden nun korrekt dargestellt (schon mal eine wesentliche Erleichterung). Bekommt man das andere auch noch hin?
vielen vielen DANK einmal!!
ich würde dir einen großen Stern verleihen, wenn ich könnte…
lg
[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]
bis zu sieben Stellen sollte man hinbekommen können - mehr ist so nicht drin.
Aber da bist jetzt Du dran!
Die Logik sollte ja hoffentlich jetzt klar sein…
ich kann dir anbieten, eine Vba-Lösung zu basteln.
hmm, ja leider - aber die daten habe ich so chatoisch bekommen
und müsste diese (um sie dann entprechend weiterverarbeiten zu
können eben alle händisch trennen). Es muss ja nicht 100%ig
funktionieren, aber wenn ich mir bei über 6000 adressen schon
mal ein wenig ersparen könnte, wäre ich dir sehr dankbar über
so eine lösung (vorausgesetzt, es ist nicht all zu
umständlich). wiegesagt ich bin kein fachmann auf dem gebiet!!
Hi Mario,
Alt+F11, Einfügen–Modul, Code reinkopieren, Cursor beliebig irgendwo in den Code stellen und F5 drücken, Editor schließen.
Leichte Probleme bekommst du wenn die Addressen 5thAvenue12 lauten
Option Explicit
Sub tt()
Dim Zei As Long, Pos As Integer
For Zei = 1 To ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
For Pos = 1 To Len(Cells(Zei, 1))
If Mid(Cells(Zei, 1), Pos, 1) \>= "0" And Mid(Cells(Zei, 1), Pos, 1)
Rückmeldung wäre nett.
Gruß
Reinhard
) die Formel komplettiert…
