Excel Straße und Hausnummer trennen ohne VBA

    Diese Seite verwendet Cookies. Durch die Nutzung unserer Seite erklären Sie sich damit einverstanden, dass wir Cookies setzen. Weitere Informationen

    • Excel Straße und Hausnummer trennen ohne VBA

      So wie versprochen jetzt meine ziemlich behinderte Lösung.

      Erstmal ein kleiner (ausgedachter) Beispieldatensatz:

      adresse
      Am Alten Haus 21
      amige 1 / 2
      post
      2+3+4
      Zur kleinen alten Burg 2



      "adresse" steht in A1

      Die Formel kommt rechts neben die erste Anschrift:

      Formel in B2

      Quellcode

      1. =WENN(UND(CODE(LINKS(A2;1))>47;CODE(LINKS(A2;1))<58);
      3. A2;
      4. WENN(
      5. UND(ODER(CODE(TEIL(A2;SUCHEN(" ";A2)-1;1))=46;
      6. UND(CODE(TEIL(A2;SUCHEN(" ";A2)-1;1))>64;CODE(TEIL(A2;SUCHEN(" ";A2)-1;1))<91);
      7. UND(CODE(TEIL(A2;SUCHEN(" ";A2)-1;1))>96;CODE(TEIL(A2;SUCHEN(" ";A2)-1;1))<123));
      8. UND(CODE(TEIL(A2;SUCHEN(" ";A2)+1;1))>47;CODE(TEIL(A2;SUCHEN(" ";A2)+1;1))<58));
      10. TEIL(A2;SUCHEN(" ";A2)+1;LÄNGE(A2)-SUCHEN(" ";A2));
      11. WECHSELN(A2;" ";"#";1)
      12. )
      13. )
      Alles anzeigen



      Obige Formel ist beliebig erweiterbar indem man mittels "Suchen und Ersetzen" den String "A2" durch "B2", "C2" usw erstetzt. Die neuen Formel werden in C2, D2 usw eingefügt.

      Mit 4 Formeln deckt man glaube ich fast alle langen Adressnamen ab.

      Das Ergebnis sieht dann so aus, das unter Formel 4 die fertigen Hausnummern stehen:


      adresse Formel1 Formel2 Formel3 Formel 4
      Am Alten Haus 21 Am#Alten Haus 21 Am#Alten#Haus 21 21 21
      amige 1 / 2 1 / 2 1 / 2 1 / 2 1 / 2
      post #WERT! #WERT! #WERT! #WERT!
      2+3+4 2+3+4 2+3+4 2+3+4 2+3+4
      Zur kleinen alten Burg 2 Zur#kleinen alten Burg 2 Zur#kleinen#alten Burg 2 Zur#kleinen#alten#Burg 2 2


      Jetzt noch die Erklärung was die Formel(n) macht für alle das nicht gleich rauslesen können.

      Erklärung


      Zuerst wird geschaut ob in A2 nicht schon eine Zahl steht:

      Quellcode

      1. WENN(UND(CODE(LINKS(A2;1))>47;CODE(LINKS(A2;1))<58);
      Falls ja, wird diese übernommen. Falls nicht, wird nach dem ersten Leerzeichen gesucht:

      Quellcode

      1. SUCHEN(" ";A2)

      und gleichzeitig wird die Position links vom Leerzeichen darauf überrpüft, ob da ein Punkt oder Buchstabe (a-z und A-Z) steht (dies geschieht über die ASCII-CODES):

      Quellcode

      1. UND(ODER(CODE(TEIL(A2;SUCHEN(" ";A2)-1;1))=46;
      2. UND(CODE(TEIL(A2;SUCHEN(" ";A2)-1;1))>64;CODE(TEIL(A2;SUCHEN(" ";A2)-1;1))<91);
      3. UND(CODE(TEIL(A2;SUCHEN(" ";A2)-1;1))>96;CODE(TEIL(A2;SUCHEN(" ";A2)-1;1))<123));

      Ebenso wird die Position rechts vom Leerzeichen darauf untersucht ob es sich um eine Zahl (0-9) handelt:

      Quellcode

      1. UND(CODE(TEIL(A2;SUCHEN(" ";A2)+1;1))>47;CODE(TEIL(A2;SUCHEN(" ";A2)+1;1))<58));

      Falls all das der Fall ist, dann wird alles rechts vom Leerzeichen ausgelesen:

      Quellcode

      1. TEIL(A2;SUCHEN(" ";A2)+1;LÄNGE(A2)-SUCHEN(" ";A2));

      Falls das nicht der Fall ist, dann wird das Leerzeichen durch # ersetzt.

      Quellcode

      1. WECHSELN(A2;" ";"#";1)

      Das hat zur Folge das die Formel rechts davon(z.B. in C2) dieses Leerzeichen nicht mehr findet und nach Weiteren sucht.





      Lediglich das Ergebnis in Zeile 3 ärgert mich noch, dort wäre es besser wenn die Zelle halt leer wird oder einen "_" erhält. Naja vllt. habe ich Montag noch mal Muße. (oder jemand von euch nen einfach Code?)

      HF beim testen!

      mfg
      coruscant
      Kommentar zur Krise xyz:
      Ich hatte mich schon gefragt welche nächste Sau durch's Dorf getrieben
      wird. Was wohl als nächstes kommt. Klimawandel oder vielleicht doch
      wieder Terrorismus ...

      Das der Mond auf die Erde stützt, DASS wäre mal was wirlich neues und
      sicher auch extrem verheerend. Alternativ tut es auch ein großer
      Meteorit.

      Ich kann es mir in Gedanken schon vorstellen. An Schweinegrippe
      erkrankt und vom Meteoriten erschlagen als der Kofferbomber gerade
      einen Block entfernt war ...

      Ja, das sind wahrhaft düstere Zeiten. Ich mach erst mal ein Bier auf ... Das ewige Leben wird sowieso keiner haben.

      Hier gehts lang zu Rätseln der gehobenen Schwierigkeitsklasse!