Heute habe ich glaube ich die umständlichste Excel-Formel meines Lebens geschrieben. Worum ging's?
Gegeben sei ein Kollege "Lars" sowie ein Problem "Ich habe hier in der Exceltabelle Adressen und die muss ich in Straße und Hausnummer aufteilen".
Ich war gerade mal mit Nachdenken über den Trenner bei "am ersten Leerzeichen trennen reicht nicht" angekommen, da schießen mehrere andere Kollegen schon "Trennung an der ersten Ziffer im String" in den Raum. Einfach, aber effektiv. Da ist erstmal nichts gegen einzuwenden.
Also oblag mir nun die Aufgabe, das in Excel zu formulieren. Strings kann man mit TEIL() aufteilen, also ist erstmal die Trennstelle relevant. Aha, da gibt's FINDE(), das macht ungefähr sowas wie in index() in Basic oder substr() in Perl. Wenn man denn erkennt, dass der Parameter "Suchtext" die Nadel und "Text" der Heuhaufen ist und nicht andersherum. Wahnsinnig sprechende Parameterbezeichnungen. Na ja.
FINDE() erwartet einen festen Suchtext - das ist unpassend, da ja eine Ziffer beliebiger Art gesucht wird. Also mal weiterforschen - ah, es gibt auch SUCHE() (wieso fällt mir eigentlich erst jetzt beim Tippen auf, wie dämlich diese beiden Funktionsnamen sind, wenn man sie direkt gegenüberstellt?). SUCHE() hat den added benefit, dass man auch nach Platzhaltern suchen kann - namentlich ? und *. Das bringt uns im Ziffernproblem leider keinen Schritt weiter.
Das Allheilmittel "reguläre Ausdrücke" versagt leider: Die gesamten Office-Produkte kennen sowas wohl gerade mal in Suchen und Ersetzen, nicht aber in der Formelsprache. (Selbst OpenOffice, was sich ja ein wenig mehr im unixoiden Umfeld zu Hause ist, kann das wohl nicht.)
Hier folgt der erste eklige Gedankengang meinerseits: Na, dann suchen wir halt nach allen Ziffern nacheinander und nehmen den ersten Treffer. So ungefähr (G1 sei eine passende Zellenreferenz):
MIN( FINDE( 0; A2; 1); FINDE( 1; A2; 1); FINDE( 2; A2; 1); [...]
)
Schon ein netter Ansatz, das Ergebnis davon ist allerdings "#WERT", solange nicht alle Ziffern wenigstens einmal vorkommen. "nicht gefunden" expandiert also zu "#WERT" und "#WERT" mag die MIN()-Funktion nicht.
Also ist es Zeit für den zweiten ekligen Trick: Hängen wir doch mit VERKETTEN() hinter jeden zu durchsuchenden String noch einmal die gesuchte Ziffer, damit die Suche auch terminiert. Spätestens jetzt wird die Formel allerdings leicht unübersichtlich, so dass ich dann zu einem Stückchen Perl gegriffen habe, welches mir die Formel ausgespuckt hat. Perl ist ja so viel besser als Excel.
#!/usr/bin/perl -w
use strict;
my $feld = "G1";
print "=GLÄTTEN(LINKS($feld;MIN(";
foreach my $nr ( qw ( 0 1 2 3 4 5 6 7 8 9 ) ) {
print ";" if $nr;
print "FINDEN($nr;VERKETTEN($feld;\"$nr\";);1)";
}
Man beachte: hier ist auch gleich das Abschneiden an der Fundstelle über LINKS() und GLÄTTEN() (wtf! Funktion mit Umlaut! Und was für eine bescheuerte Übersetzung von TRIM()) realisiert. Außerdem ist ein überflüssiges Semikolon in VERKETTEN() enthalten, was noch einen Leerstring anhängt...
Das Ergebnis präsentiert sich dann so (Leerzeichen für den Umbruch eingebaut):
=GLÄTTEN(LINKS(G1;MIN(FINDEN(0;VERKETTEN(G1;"0";);1); FINDEN(1;VERKETTEN(G1;"1";);1); FINDEN(2;VERKETTEN(G1;"2";);1); FINDEN(3;VERKETTEN(G1;"3";);1); FINDEN(4;VERKETTEN(G1;"4";);1); FINDEN(5;VERKETTEN(G1;"5";);1); FINDEN(6;VERKETTEN(G1;"6";);1); FINDEN(7;VERKETTEN(G1;"7";);1); FINDEN(8;VERKETTEN(G1;"8";);1); FINDEN(9;VERKETTEN(G1;"9";);1))-1))
Und dieser Wahnsinn funktioniert. Über 3000 handelsübliche Adressen sauber aufgeteilt, die einfache Regel "erste Ziffer" haut für deutsche Adressen wohl wirklich gut hin.
Da das offensichtlich nur die Straße ausgibt: Umbau auf Ausgabe der Hausnummer left as an exercise to the reader.
Nun für für die Spezialisten da draußen: Wie macht man das richtig?
VBA mal außen vor, geht das mit Excel-Bordmitteln wirklich nur so eklig?
