Anzeige
Archiv - Navigation
520to524
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
520to524
520to524
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Inhaltsverzeichnis

Straße und Hausnummer trennen auch mit leerzeichen

Straße und Hausnummer trennen auch mit leerzeichen
20.11.2004 10:40:39
Olli
Hallo ich dringend Hilfe zu folgenden Script:
####################
Sub Trennen() Dim lngZeile As Long Dim intZeichen As Integer lngZeile = 0 With ActiveCell Do While IsEmpty(Cells(.Row + lngZeile, .Column)) = False For intZeichen = 1 To Len(Cells(.Row + lngZeile, .Column)) If IsNumeric(Right(Cells(.Row + lngZeile, .Column), intZeichen)) = False _ Or Left(Right(Cells(.Row + lngZeile, .Column), intZeichen), 1) = "." Then Exit For End If Next intZeichen Cells(.Row + lngZeile, .Column + 1) = _ Left(Cells(.Row + lngZeile, .Column), Len(Cells(.Row + lngZeile, .Column)) - intZeichen + 1) If Left(Right(Cells(.Row + lngZeile, .Column), intZeichen - 1), 1) = " " Then Cells(.Row + lngZeile, .Column + 2) = _ Right(Cells(.Row + lngZeile, .Column), intZeichen - 2) Else Cells(.Row + lngZeile, .Column + 2) = _ Right(Cells(.Row + lngZeile, .Column), intZeichen - 1) End If lngZeile = lngZeile + 1 Loop End With End Sub
###################
und das ist das Ergebnins:
Weg1 Weg 1
Weg 1 Weg 1
Allee Str.3 Allee Str. 3
Allee Str. 3 Allee Str. 3
Dr- Paul- Straße 2 Dr- Paul- Straße 2
weg 2 weg 2
Straße 1-4 Straße 1 -4
Die probleme sind, wenn hinter der Hausnummer noch ein leerzeichen ist.Es wär super wenn mir jemann helfen könnte
PS an alle die mir schon ( vorallem mit diesem Script) geholfen haben.
Olli

5
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Straße und Hausnummer trennen auch mit leerzeichen
20.11.2004 11:06:54
Hajo_Zi
Hallo Olli,
warum über Makro, benutze doch Formeln.
Straße von Hausnummer trennen
in D1 für die Hausnummer steht also die Array-Formel:
{=TEIL(A1;VERGLEICH(WAHR;ISTZAHL(TEIL(A1;SPALTE(1:1);1)*1);0);LÄNGE(A1))*1}
in C1 für den Straßennamen steht:
=LINKS(A1;LÄNGE(A1)-LÄNGE(D1))
ARRAY-Formel {=geschweifte Klammern} nicht eingeben;
Abschluss der Formel mit gleichzeitig Strg / Shift / Enter; - das erzeugt sie.
Von WF
Straße des 17. Juni 23
dann nimm:
{=TEIL(RECHTS(A1;4);VERGLEICH(WAHR;ISTZAHL(TEIL(RECHTS(A1;4);SPALTE(1:1);1)*1);0);LÄNGE(RECHTS(A1;4)))*1}
Über 4-stellige Hausnummern gibt’s ja wohl nicht; und die Formel versagt dann nur, wenn der "Name" mit einer Zahl endet.
in C1 für den Straßennamen steht:
=LINKS(A1;LÄNGE(A1)-LÄNGE(D1))
ARRAY-Formel {=geschweifte Klammern} nicht eingeben;
Abschluss der Formel mit gleichzeitig Strg / Shift / Enter; - das erzeugt sie.
Von WF
Bitte keine Mail, Probleme sollten im Forum gelöst werden.
Microsoft MVP für Excel
Das Forum lebt auch von den Rückmeldungen.
Betriebssystem XP Home SP2 und Excel Version 2003 SP1.


Anzeige
AW: Straße und Hausnummer trennen auch mit leerzeichen
20.11.2004 11:55:23
olli
danke für deine hilfe, aber brauche eine formel die ich in der tab. runterziehen kann.
und bei der "straße 1-7" schreibt er eine größe 4 stellige zahl.
vielleicht hast ´du ja noch was
danke
AW: Straße und Hausnummer trennen auch mit leerzeichen
20.11.2004 12:06:49
Hajo_Zi
Hallo Olli,
die Formel kann runtergezogen werden. Entferne das "*1"
Tabelle1
 ABCD
1Straße des 17. Juni 23Straße des 17. Juni 23ARRAY-Formel {=geschweifte Klammern} nicht eingeben; Abschluss der Formel mit gleichzeitig Strg / Shift / Enter; - das erzeugt sie. Von WF
2Straße 1-7Straß38169 
3    
4Straße des 17. Juni 23Straße des 17. Juni 23 
5Straße 1-7Straße 1-7 
6    
Formeln der Tabelle
B1 : =LINKS(A1;LÄNGE(A1)-LÄNGE(C1))
C1 : {=TEIL(RECHTS(A1;4);VERGLEICH(WAHR;ISTZAHL(TEIL(RECHTS(A1;4);SPALTE(1:1);1)*1);0);LÄNGE(RECHTS(A1;4)))*1}
B2 : =LINKS(A2;LÄNGE(A2)-LÄNGE(C2))
C2 : {=TEIL(RECHTS(A2;4);VERGLEICH(WAHR;ISTZAHL(TEIL(RECHTS(A2;4);SPALTE(2:2);1)*1);0);LÄNGE(RECHTS(A2;4)))*1}
B4 : =LINKS(A4;LÄNGE(A4)-LÄNGE(C4))
C4 : {=TEIL(RECHTS(A4;4);VERGLEICH(WAHR;ISTZAHL(TEIL(RECHTS(A4;4);SPALTE(4:4);1)*1);0);LÄNGE(RECHTS(A4;4)))}
B5 : =LINKS(A5;LÄNGE(A5)-LÄNGE(C5))
C5 : {=TEIL(RECHTS(A5;4);VERGLEICH(WAHR;ISTZAHL(TEIL(RECHTS(A5;4);SPALTE(5:5);1)*1);0);LÄNGE(RECHTS(A5;4)))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
 
Diagramm - Grafik - Excel Tabellen einfach im Web darstellen    Excel Jeanie HTML  3.0    Download  
Bitte keine Mail, Probleme sollten im Forum gelöst werden.
Microsoft MVP für Excel
Das Forum lebt auch von den Rückmeldungen.
Betriebssystem XP Home SP2 und Excel Version 2003 SP1.


Anzeige
AW: nicht schlecht! Aber...
FP
Hi Hajo,
... was ist, wenn jemand in der "Strasse des 17. Juni 99, 10. Stock, Tür 30" ( Schreibweise normalerweise "Strasse des 17. Juni 99/10/30" ) wohnt?
Tabelle1
 ABCD
1Strasse des 17.Juni 23Strasse des 17.Juni 2323
2Strasse des 17. Juni 1-7Strasse des 17. Juni 1-71-7
3Goethestr.17Goethestr.1717
4Goethestr. 17Goethestr. 1717
5Goethestr.17aGoethestr.17a17a
6Goethestr.17a/2/5Goethestr.17a/2/52/5
7Goethestr. 17a/2/5Goethestr. 17a/2/52/5
8Strasse des 17. Juni 99/10/30Strasse des 17. Juni 99/10/300/30
Formeln der Tabelle
B1 : =LINKS(A1;LÄNGE(A1)-LÄNGE(C1))
C1 : =TEIL(A1;SUCHEN("\";WECHSELN(A1;WENN(ANZAHL(SUCHEN(" ";A1))>0;" ";".");"\";LÄNGE(A1)-LÄNGE(WECHSELN(A1;WENN(ANZAHL(SUCHEN(" ";A1))>0;" ";".");""))))+1;99)
D1 : {=TEIL(RECHTS(A1;4);VERGLEICH(WAHR;ISTZAHL(TEIL(RECHTS(A1;4);SPALTE(1:1);1)*1);0);LÄNGE(RECHTS(A1;4)))}
B2 : =LINKS(A2;LÄNGE(A2)-LÄNGE(C2))
C2 : =TEIL(A2;SUCHEN("\";WECHSELN(A2;WENN(ANZAHL(SUCHEN(" ";A2))>0;" ";".");"\";LÄNGE(A2)-LÄNGE(WECHSELN(A2;WENN(ANZAHL(SUCHEN(" ";A2))>0;" ";".");""))))+1;99)
D2 : {=TEIL(RECHTS(A2;4);VERGLEICH(WAHR;ISTZAHL(TEIL(RECHTS(A2;4);SPALTE(2:2);1)*1);0);LÄNGE(RECHTS(A2;4)))}
B3 : =LINKS(A3;LÄNGE(A3)-LÄNGE(C3))
C3 : =TEIL(A3;SUCHEN("\";WECHSELN(A3;WENN(ANZAHL(SUCHEN(" ";A3))>0;" ";".");"\";LÄNGE(A3)-LÄNGE(WECHSELN(A3;WENN(ANZAHL(SUCHEN(" ";A3))>0;" ";".");""))))+1;99)
D3 : {=TEIL(RECHTS(A3;4);VERGLEICH(WAHR;ISTZAHL(TEIL(RECHTS(A3;4);SPALTE(3:3);1)*1);0);LÄNGE(RECHTS(A3;4)))}
B4 : =LINKS(A4;LÄNGE(A4)-LÄNGE(C4))
C4 : =TEIL(A4;SUCHEN("\";WECHSELN(A4;WENN(ANZAHL(SUCHEN(" ";A4))>0;" ";".");"\";LÄNGE(A4)-LÄNGE(WECHSELN(A4;WENN(ANZAHL(SUCHEN(" ";A4))>0;" ";".");""))))+1;99)
D4 : {=TEIL(RECHTS(A4;4);VERGLEICH(WAHR;ISTZAHL(TEIL(RECHTS(A4;4);SPALTE(4:4);1)*1);0);LÄNGE(RECHTS(A4;4)))}
B5 : =LINKS(A5;LÄNGE(A5)-LÄNGE(C5))
C5 : =TEIL(A5;SUCHEN("\";WECHSELN(A5;WENN(ANZAHL(SUCHEN(" ";A5))>0;" ";".");"\";LÄNGE(A5)-LÄNGE(WECHSELN(A5;WENN(ANZAHL(SUCHEN(" ";A5))>0;" ";".");""))))+1;99)
D5 : {=TEIL(RECHTS(A5;4);VERGLEICH(WAHR;ISTZAHL(TEIL(RECHTS(A5;4);SPALTE(5:5);1)*1);0);LÄNGE(RECHTS(A5;4)))}
B6 : =LINKS(A6;LÄNGE(A6)-LÄNGE(C6))
C6 : =TEIL(A6;SUCHEN("\";WECHSELN(A6;WENN(ANZAHL(SUCHEN(" ";A6))>0;" ";".");"\";LÄNGE(A6)-LÄNGE(WECHSELN(A6;WENN(ANZAHL(SUCHEN(" ";A6))>0;" ";".");""))))+1;99)
D6 : {=TEIL(RECHTS(A6;4);VERGLEICH(WAHR;ISTZAHL(TEIL(RECHTS(A6;4);SPALTE(6:6);1)*1);0);LÄNGE(RECHTS(A6;4)))}
B7 : =LINKS(A7;LÄNGE(A7)-LÄNGE(C7))
C7 : =TEIL(A7;SUCHEN("\";WECHSELN(A7;WENN(ANZAHL(SUCHEN(" ";A7))>0;" ";".");"\";LÄNGE(A7)-LÄNGE(WECHSELN(A7;WENN(ANZAHL(SUCHEN(" ";A7))>0;" ";".");""))))+1;99)
D7 : {=TEIL(RECHTS(A7;4);VERGLEICH(WAHR;ISTZAHL(TEIL(RECHTS(A7;4);SPALTE(7:7);1)*1);0);LÄNGE(RECHTS(A7;4)))}
B8 : =LINKS(A8;LÄNGE(A8)-LÄNGE(C8))
C8 : =TEIL(A8;SUCHEN("\";WECHSELN(A8;WENN(ANZAHL(SUCHEN(" ";A8))>0;" ";".");"\";LÄNGE(A8)-LÄNGE(WECHSELN(A8;WENN(ANZAHL(SUCHEN(" ";A8))>0;" ";".");""))))+1;99)
D8 : {=TEIL(RECHTS(A8;4);VERGLEICH(WAHR;ISTZAHL(TEIL(RECHTS(A8;4);SPALTE(8:8);1)*1);0);LÄNGE(RECHTS(A8;4)))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
 
Diagramm - Grafik - Excel Tabellen einfach im Web darstellen    Excel Jeanie HTML  3.0    Download  
Servus aus dem Salzkammergut
Franz
Anzeige
AW: etwas kürzer...
FP
Hi Hajo bzw. Olli,
geht es noch.
ohne Hilfsspalte:
=TEIL(A1;SUCHEN("\";WECHSELN(A1;WENN(ZÄHLENWENN(A1;"* *");" ";".");"\";LÄNGE(A1)-LÄNGE(WECHSELN(A1;WENN(ZÄHLENWENN(A1;"* *");" ";".");""))))+1;99)
mit Hilfsspalte D - welches Zeichen wird getauscht
in D1: =WENN(ZÄHLENWENN(A1;"* *");" ";".")
in C1: =TEIL(A1;SUCHEN("\";WECHSELN(A1;D1;"\";LÄNGE(A1)-LÄNGE(WECHSELN(A1;D1;""))))+1;99)
Tabelle1
 ABCDE
1Strasse des 17.Juni 23Strasse des 17.Juni 23 23
2Strasse des 17. Juni 1-7Strasse des 17. Juni 1-7 1-7
3Strasse des 17. Juni 99/10/30Strasse des 17. Juni 99/10/30 99/10/30
4Strasse des 17.Juni 99/10/30Strasse des 17.Juni 99/10/30 99/10/30
Formeln der Tabelle
B1 : =LINKS(A1;LÄNGE(A1)-LÄNGE(E1))
C1 : =TEIL(A1;SUCHEN("\";WECHSELN(A1;D1;"\";LÄNGE(A1)-LÄNGE(WECHSELN(A1;D1;""))))+1;99)
D1 : =WENN(ZÄHLENWENN(A1;"* *");" ";".")
E1 : =TEIL(A1;SUCHEN("\";WECHSELN(A1;WENN(ZÄHLENWENN(A1;"* *");" ";".");"\";LÄNGE(A1)-LÄNGE(WECHSELN(A1;WENN(ZÄHLENWENN(A1;"* *");" ";".");""))))+1;99)
B2 : =LINKS(A2;LÄNGE(A2)-LÄNGE(E2))
C2 : =TEIL(A2;SUCHEN("\";WECHSELN(A2;D2;"\";LÄNGE(A2)-LÄNGE(WECHSELN(A2;D2;""))))+1;99)
D2 : =WENN(ZÄHLENWENN(A2;"* *");" ";".")
E2 : =TEIL(A2;SUCHEN("\";WECHSELN(A2;WENN(ZÄHLENWENN(A2;"* *");" ";".");"\";LÄNGE(A2)-LÄNGE(WECHSELN(A2;WENN(ZÄHLENWENN(A2;"* *");" ";".");""))))+1;99)
B3 : =LINKS(A3;LÄNGE(A3)-LÄNGE(E3))
C3 : =TEIL(A3;SUCHEN("\";WECHSELN(A3;D3;"\";LÄNGE(A3)-LÄNGE(WECHSELN(A3;D3;""))))+1;99)
D3 : =WENN(ZÄHLENWENN(A3;"* *");" ";".")
E3 : =TEIL(A3;SUCHEN("\";WECHSELN(A3;WENN(ZÄHLENWENN(A3;"* *");" ";".");"\";LÄNGE(A3)-LÄNGE(WECHSELN(A3;WENN(ZÄHLENWENN(A3;"* *");" ";".");""))))+1;99)
B4 : =LINKS(A4;LÄNGE(A4)-LÄNGE(E4))
C4 : =TEIL(A4;SUCHEN("\";WECHSELN(A4;D4;"\";LÄNGE(A4)-LÄNGE(WECHSELN(A4;D4;""))))+1;99)
D4 : =WENN(ZÄHLENWENN(A4;"* *");" ";".")
E4 : =TEIL(A4;SUCHEN("\";WECHSELN(A4;WENN(ZÄHLENWENN(A4;"* *");" ";".");"\";LÄNGE(A4)-LÄNGE(WECHSELN(A4;WENN(ZÄHLENWENN(A4;"* *");" ";".");""))))+1;99)
 
Diagramm - Grafik - Excel Tabellen einfach im Web darstellen    Excel Jeanie HTML  3.0    Download  
Servus aus dem Salzkammergut
Franz
Anzeige

81 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige