Hi Leute,

jetzt hat mir Peter wieder den Ball zugespielt - der "Anstifter" muss das Gesamtwerk denn auch mal zusammenfassen und erläutern.

Nun denn - auf los geht´s los (wobei ich nur die =C-Lösung erläutere - ansonsten sitz ich noch morgen früh um 5 am PC)

Ausgangssituation - zur Erinnerung:

Gesucht war eine FORMELmöglichkeit, einen Zellinhalt in seine Einzelteile zu zerlegen - analog DATEN-TEXT IN SPALTEN.

Als zuverlässiges Trennzeichen ist immer EIN Leerzeichen zwischen den einzelnen Worten.

In A1 steht:

Dies ist das ultimative Excelforum

Ergebnis soll sein:

Dies / ist / das / ultimative / Excelforum

wobei "/" immer für ne neue Zelle steht (also B1 bis F1).

Der Grundgedanke von Peter:

Es muss doch möglich sein, den Text in ein Array zu packen, um auf dieses Array mittels der Funktion INDEX zurückzugreifen.

Da man bekanntermaßen mit den berühmten {geschweiften Klammern} ein Array darstellen kann (und das auch innerhalb einer Formel / Funktion, sieht das im Wunschergebnis so aus:

=INDEX({"Dies";"ist";"das";"ultimative";"Excelforum"};4)

In diesem Fall wird z.B. das 4. Wort indiziert - also "ultimative".

Nur: Wie bekomme ich den Ausgangstext in solch ein Array?

Und hier beginnt der geniale Gedanke:

Man wandelt den Text in einen neuen Text um - und zwar nutzt man hierfür den Operator "&" (zwecks Verkettung) sowie die Funktion WECHSELN, indem man "{" davor setzt, die Leerzeichen gegen ";" tauscht und die "}" hinten dranhängt:

="{"""&WECHSELN(Tabelle1!$A1;" ";""";""")&"""}"

Das Ergebnis dieser Formel ist:

{"Dies";"ist";"das";"ultimative";"Excelforum"}

Das alleine ist schon recht genial - denn " " wird durch """ weggewechselt - und im Ergebnis sind es dann ";"

So - jetzt ist das Array optisch als String vorhanden - doch es wird ja für die Funktion INDEX benötigt - und die braucht keinen String, sondern ein Wertearray.

Was tun? Nochmal in die Trickkiste greifen und die Excel4-Macrofunktion AUSWERTEN "ausgraben".

Diese Funktion ist ein Überbleibsel aus früheren Excel-Zeiten. In Tabellen steht sie nicht mehr zur Verfügung - wenn man sie aber in einen NAMEN einbindet (Einfügen-Namen-Festlegen/Definieren), dann ist sie wieder verwendbar.

Was macht die Funktion AUSWERTEN?

Ganz einfach: Sie wertet einen String aus und gibt das Ergebnis des Strings wieder.

Ein String "1+1" wird mittels der Funktion AUSWERTEN zu 2 - also zum Ergebnis.

In unserem Fall ist die Funktion in der Lage, das zusammengebastelte String-Array in ein WERTE-Array umzuwandeln, so dass man dieses auch - in unserem Fall - für die Funktion INDEX gebrauchen kann.

Also aus dem String

{"Dies";"ist";"das";"ultimative";"Excelforum"}

wird ein Wertearray - jetz kann man damit auch "rechnen".

Vorgehensweise:

Zelle B1 selektieren - das ist wichtig, da die Namensdefinition zeilenmäßig RELATIV erfolgt (spaltenmäßig ABSOLUT) - später mehr dazu.

Dann: Einfügen-Namen-Definieren/Festlegen, Name: C

Bezieht sich auf:

=INDEX(AUSWERTEN("{"""&WECHSELN(Tabelle1!$A1;" ";""";""")&"""}");SPALTE()-1)

So - hier hab ich jetzt mehrere Schritte auf einmal gemacht - zudem hab ich - gegenüber der Ursprungslösung - direkt alle 3 Namen in einem zusammengefasst.

Jetzt wird gleich auch deutlich, warum es wichtig war, vorher unbedingt die Zelle B1 zu aktivieren:

Mit

AUSWERTEN("{"""&WECHSELN(Tabelle1!$A1;" ";""";""")&"""}")

erhält man das WERTE-Array des Zellinhaltes aus Spalte A (absolut) und Zeile 1 (relativ), also $A1:

{"Dies";"ist";"das";"ultimative";"Excelforum"}

das ja erst als String zusammengebastelt wurde und jetzt mittels AUSWERTEN zum Wertearray gemacht wird.

Also übersetzt in der Formel:

=INDEX({"Dies";"ist";"das";"ultimative";"Excelforum"};SPALTE()-1)

SPALTE()-1 ergibt - aus Sicht der aktiven Zelle B1 - als Ergebnis 1 (denn Spalte B minus 1 = Spalte A = 1).

Also wieder übersetzt:

=INDEX({"Dies";"ist";"das";"ultimative";"Excelforum"};1)

Und im Klartext wird hier das 1. Wort ausgelesen - also "Dies"

Aus Sicht der Nachbarzelle C1 ergibt SPALTE()-1 = Spalte C minus 1 = Spalte B = 2.

Also:

=INDEX({"Dies";"ist";"das";"ultimative";"Excelforum"};2)

Also das Wort "ist".

In B2 wird sodann Bezug genommen auf A2, Wort 1, in C2 auf A2 Wort 2 etc. etc...

Daher wichtig: ´Spalte ABSOLUT - Zeile RELATIV.

Wenn man jetzt also in B1 eingibt

=C

und diese "Formel" nach rechts und nach unten kopiert, bekommt man die ganzen Einträge in Spalte A in die einzelnen Worte zerlegt.

Eine - wie ich finde - super starke Lösung !!!

So - ich hoffe, das war einigermaßen verständlich (für diejenigen, die überhaupt bis hier her gelesen haben ;-)

Grüße Boris