Fehler in Excel-Adressliste finden: Funktion gesucht!

...komplette Frage anzeigen

2 Antworten

wenn Strasse, Hausnummer und Plz verschmolzen sind
(da müsste aber dann auch die Spalte daneben anders aussehen!):

{=Max(Istfehler(Wert(Links(C1:C111;1)))*Zeile(C1:C111)*Nicht(Istleer(C1:C111)))}

Achtung: Dies ist eine Matrixformel!
Die geschweiften {Klammern} NICHT miteingeben, sondern die Formel anstatt mit einfachem Enter mit der Kombination Strg+Shift+Enter abschließen (nach jeder Neubearbeitung wieder!). Die {Klammern} entstehen dann automatisch.
Schreibtechnisch nimmst Du am besten die rechte Strg- und Umschalttaste, dann gehts mit einer Hand und lässt sich auch leichter merken!

Die Formel gibt die jeweils unterste Zeile wieder, wo in Sp.C das erste Zeichen KEINE Zahl ist.
Die Formel am besten in einer "eingefrorenen" Titelzeile unterbringen, da die Datei von unten her korrigiert werden muss (sonst würde es um einiges komplizierter!).

Wenn nur Hausnummer und PLZ(=immer fümpf Zeichen) verschmolzen sind:

{=MAX((C1:C9>99999)*ZEILE(C1:C111)*ISTZAHL(C1:C111))}

oder

{=MAX(Länge(C1:C111)>5)*ZEILE(C1:C111))}

je nachdem, ob die PLZ-Spalte auf Standard=Zahl oder auf Text formatiert ist.

Oder ist die auszuwertende Situation nochmals anders, als ich das verstanden habe?

Britzcontrol 26.06.2014, 08:20

@ Iamiam

kleine Korrektur:

Das Zahlwort für die "5" ist nicht wie hier vermerkt: fümpf sondern

fümf

Laut hier anzuwendender Phonetik ist das bei Manchen als gehört zu vermutende "p" jedoch ein stummes "p" und wird nicht geschrieben, sondern nur gedacht. Also ein "denk-p".

Gruß aus Berlin

1
Txsdhzut 26.06.2014, 09:19
@Britzcontrol

Vielen Dank für deine Antwort! Leider gibt es keine leeren Zellen - entschuldige bitte, dass ich mich nicht korrekt ausgedrückt habe. Genauer gesagt steht in den falschen Zeilen:

Peter Müller (Spalte) Torweg 612345 Neudorf (Spalte) 612345 Neudorf

D.h., in den falschen Zeilen ist die PLZ/Ort mit der Straße/Hausnummer in der Spalte B verschmolzen, aber PLZ/Ort stehen dort dennoch in zusätzlich in der Spalte C. Sonst wäre es in der Tat einfach, die leeren Zeilen aufzuspüren.

0
Iamiam 26.06.2014, 15:28
@Txsdhzut

@ Britzcontrol: Ein Berliner kann da überhaupt nicht mitreden, auch wenn er alle fünnef Sinne beisammen hat! Hieß die Postwerbung für die PLZ-Umstellung nicht :
Fümpf ist Trümpf?
Das p ist die lautbildnerische Konsequenz aus dem m , das -ef die aus dem n Hier haben Nord-und Süddeutschland verschiedene Wege eingeschlagen, FÜNF selbst ist ein UNDING, fast unsprechbar.
Ist aber noch eine Schulweisheit, stimmt vllt gar nicht (mehr?).
Potzbritz und Gruß nach Bärlin, iamiam

@ Txs...:auch da kann geholfen werden, aber Du musst mir genau sagen, was richtigerweise in welcher Zelle steht, zB:
soll:
A2:
Peter Müller
B2:
Torweg 6
C2:
12345 Neudorf <<mit Leerzeichen?
ist und falsch:
A2: Peter Müller
B2: Torweg 612345 Neudorf <<mit Leerzeichen?!
C2: 612345 Neudorf
oder heisst es vllt:
C2:
612345
D2:
Neudorf
??? Man muss da sehr genaue infos haben, wenn nicht die ganze Arbeit vergeblich sein soll!
Übrigens ist die Zusammenfassung von Vor- und Schreibnamen zu EINEM Text nicht glücklich, da man meist nach Schreibnamen sortieren will. Da es aber azch Vornamen wie Hans Peter gibt, könnte auch eine derartige Korrektur nur halbautomatisch erfolgen (wäre aber auch möglich)
Ich schaue heute spät abends nochmal hier rein!

0
Iamiam 26.06.2014, 17:52
@Iamiam

wichtig ist mir noch, ob tatsächlich die falsche, 6-stellige PLZ anstatt der richtigen dasteht? (weil man das korrigieren müsste, um die Position der richtigen im falschen String zu finden, und überhaupt, sollte ja berichtigt werden.)
Gibt es Hausnummern wie 27a oder 26 RG oder 14 III.Stck etc und werden die auch vor der PLZ verschmolzen (nur wichtig, wenn das im String 612345 Neudorf, also der rechten Spalte, erfolgt!
Was ich plane, ist nämlich: PLZ berichtigen, identifizieren, nach der PLZ im verschmolzenen String suchen und alles links davon als Strasse/Hausnummer zu belassen.
Der längste Hausnummern-String in der PLZ/Ort-Spalte ist derjenige, der den Aufwand (Verschachtelungsebenen) bestimmt! (145 III.Stock12345 würde 13 oder 14 Verschachtelungsebenen erfordern!

0
Iamiam 27.06.2014, 12:18
@Iamiam

ich glaube, ich hab das jetzt auch ohne diese Infos hingekriegt: Folgende Formel isoliert die 5-stellige PLZ im gemischten String (nämlich die letzte 5ergruppe Ziffern):

String in F2, Fo in L2:

{=TEIL(F2;MAX(ISTZAHL(TEIL(WECHSELN(F2;" ";"µ");ZEILE(INDIREKT("a1:a"&LÄNGE(F2)));5)*1)*ZEILE(INDIREKT("a1:a"&LÄNGE(F2))));5)}

Achtung: Dies ist eine Matrixformel!
Die geschweiften {Klammern} NICHT miteingeben, sondern die Formel anstatt mit einfachem Enter mit der Kombination Strg+Shift+Enter abschließen (nach jeder Neubearbeitung wieder!). Die {Klammern} entstehen dann automatisch.
Schreibtechnisch nimmst Du am besten die rechte Strg- und Umschalttaste, dann gehts mit einer Hand und lässt sich auch leichter merken!

nun kann man den Teil rechts davon miteinbeziehen(Fo zB. in M2):

=TEIL(F2;FINDEN(L2;F2;1);99)

und andererseits den Text aus der linken Seite isolieren(zB in K2):

=LINKS(F2;FINDEN(L2;F2;1)-1)

Alle drei Formelzellen markieren und über die gesamte Länge der Liste runterkopieren.

Ganze Spalten (am Spaltenkopf, also da, wo K steht bzw. L) markieren, kopieren und in die jeweiligen falschen Datenspalten ALS WERTE einfügen ggf PLZ-Formelspalte löschen, die anderen beiden sowieso.

(mach Dir aber vorher eine Sicherheitskopie, das wird zwar funktionieren, aber Bedienungsfehler und Abstürze können immer vorkommen!)

PS: Das funktioniert auch im alten xl, in xlsx könnte man es ohne separate PLZ-Spalte machen, aber wozu? Gib bei Deiner nächsten Frage bitte nicht nur den Spaltenwechsel an, sondern gleich die echte Spalte, am besten auch die oberste Datenzeile der Liste, dann kann man alles gleich so schreiben, dass du es nicht transformieren musst!

0

So wie ich es lese, sind es bei den korrekt formatierten Adressen immer 3 gefüllte Zellen.

Die fehlerhaften müssten demnach mind. 1 leere Zelle haben.
Sortier doch einfach mal die Spalten auf- oder absteigend. Dann hast du die leeren untereinander und siehst die fehlerhaften Adressen sofort.

Txsdhzut 26.06.2014, 09:19

Vielen Dank für deine Antwort! Leider gibt es keine leeren Zellen - entschuldige bitte, dass ich mich nicht korrekt ausgedrückt habe. Genauer gesagt steht in den falschen Zeilen:

Peter Müller (Spalte) Torweg 612345 Neudorf (Spalte) 612345 Neudorf

D.h., in den falschen Zeilen ist die PLZ/Ort mit der Straße/Hausnummer in der Spalte B verschmolzen, aber PLZ/Ort stehen dort dennoch in zusätzlich in der Spalte C. Sonst wäre es in der Tat einfach, die leeren Zeilen aufzuspüren.

0

Was möchtest Du wissen?