Das Portal zum Thema Excel-Formeln

Aus einem Text (Worte getrennt durch Leerzeichen) soll ein Wort an der Stelle X von vorne bzw. von hinten ausgegeben werden.

Du sollst nicht ehebrechen bevor der Eimer da ist

erstes Wort = "Du"

=LINKS(A1;FINDEN(" ";A1)-1)

zweites Wort = "sollst"

=TEIL(A1;FINDEN(" ";A1)+1;FINDEN(" ";A1;FINDEN(" ";A1)+1)-FINDEN(" ";A1)-1)

oder (analog den Folgewörtern)

=TEIL(A1;FINDEN("##";WECHSELN(A1;" ";"##";1))+1;FINDEN("##";WECHSELN(A1;" ";"##";2))-FINDEN("##";WECHSELN(A1;" ";"##";1))-1)

drittes Wort = "nicht"

=TEIL(A1;FINDEN("##";WECHSELN(A1;" ";"##";2))+1;FINDEN("##";WECHSELN(A1;" ";"##";3))-FINDEN("##";WECHSELN(A1;" ";"##";2))-1)

dann: 2 3 2 jeweils um 1 erhöhen usw.

letztes Wort = "ist"

=TEIL(A1;FINDEN("##";WECHSELN(A1;" ";"##";LÄNGE(A1)-LÄNGE(WECHSELN(A1;" ";))))+1;50)

oder kürzer:

=TEIL(A1;VERWEIS(9^9;FINDEN(" ";A1;ZEILE(1:999)))+1;50)

vorletztes Wort = "da"

=LINKS(TEIL(A1;FINDEN("##";WECHSELN(A1;" ";"##";LÄNGE(A1)-LÄNGE(WECHSELN(A1;" ";))-1))+1;999);FINDEN(" ";TEIL(A1;FINDEN("##";WECHSELN(A1;" ";"##";LÄNGE(A1)-LÄNGE(WECHSELN(A1;" ";))-1))+1;999))-1)

drittletztes Wort = "Eimer"

=LINKS(TEIL(A1;FINDEN("##";WECHSELN(A1;" ";"##";LÄNGE(A1)-LÄNGE(WECHSELN(A1;" ";))-2))+1;999);FINDEN(" ";TEIL(A1;FINDEN("##";WECHSELN(A1;" ";"##";LÄNGE(A1)-LÄNGE(WECHSELN(A1;" ";))-2))+1;999))-1)

dann: -2 zweimal auf -3 vermindern usw.

automatisiert von vorne oder hinten
In E1 steht das/die Trennzeichen (also auch eine einzige Leerstelle) und in F1 die Zahl des gewünschten Wortes/Blocks (positiv: von vorne; negativ: von hinten).
In E2 steht:

=WENN(ABS(F1)>LÄNGE(A1)-LÄNGE(WECHSELN(A1;E1;))+1;99;WENN(F1<0;LÄNGE(A1)-LÄNGE(WECHSELN(A1;E1;))+2+F1;F1))

bzw. die (positive) Zahl, wenn es nur von vorne interessiert (F1 ist dann überflüssig).

=WENN(E2=0;A1;WENN(E2=1;WENN(ISTFEHLER(FINDEN(E1;A1));A1;LINKS(A1;FINDEN(E1;A1)-1));WENN((LÄNGE(A1)-LÄNGE(WECHSELN(A1;E1;)))/LÄNGE(E1)+1=E2;TEIL(A1;VERWEIS(9^9;FINDEN(E1;A1;ZEILE(1:999)))+LÄNGE(E1);9^9);WENN((LÄNGE(A1)-LÄNGE(WECHSELN(A1;E1;)))/LÄNGE(E1)+1 < E2;"es gibt nur "&(LÄNGE(A1)-LÄNGE(WECHSELN(A1;E1;)))/LÄNGE(E1)+1&" Worte/Blöcke";TEIL(A1;FINDEN("##";WECHSELN(A1;E1;"##";E2-1))+LÄNGE(E1);FINDEN("##";WECHSELN(A1;E1;"##";E2))-FINDEN("##";WECHSELN(A1;E1;"##";E2-1))-LÄNGE(E1))))))

extrem kurze Alternative von lupo1

=GLÄTTEN(TEIL(WECHSELN(GLÄTTEN(A1);E1;WIEDERHOLEN(" ";199));E2*199-198;199))

Ohne Einbau der obigen Warnung, damit sie kurz bleibt.

Statt "##" kann jedes andere Zeichen/Zeichenfolge verwendet werden; - es muß nur sichergestellt sein, das es im Ursprungstext nicht vorkommt.

Können auch mehrere Leerzeichen hintereinander vorkommen, muß jedesmal A1 geglättet werden. Das geht auf einen Rutsch mit Bearbeiten / Ersetzen: suchen nach A1 ersetzen durch GLÄTTEN(A1)

Die Formel für das drittletzte Wort z.B. wird dann zu:

=LINKS(TEIL(GLÄTTEN(A1);FINDEN("##";WECHSELN(GLÄTTEN(A1);" ";"##";LÄNGE(GLÄTTEN(A1))-LÄNGE(WECHSELN(A1;" ";))-2))+1;999);FINDEN(" ";TEIL(GLÄTTEN(A1);FINDEN("##";WECHSELN(GLÄTTEN(A1);" ";"##";LÄNGE(GLÄTTEN(A1))-LÄNGE(WECHSELN(A1;" ";))-2))+1;999))-1)

Formelerklärung - aufgedröselt für das drittletzte Wort (ohne GLÄTTEN)

Schreibe in B1 bis B6 folgende Formeln

=LÄNGE(A1)-LÄNGE(WECHSELN(A1;" ";))-2

=WECHSELN(A1;" ";"##";B1)

=FINDEN("##";B2)

=TEIL(A1;B3+1;999)

=FINDEN(" ";B4)

=LINKS(B4;B5-1)

in B1 wird die Anzahl der Leerstellen ermittelt (abzüglich 2 für das drittletzte Wort):
6

in B2 wird die drittletzte Leerstelle durch ## ersetzt:
Du sollst nicht ehebrechen bevor der##Eimer da ist.

in B3 wird die Position von ## ermittelt:
37

in B4 wird der Teil des Satzes nach ## ausgegeben:
Eimer da ist.

in B5 wird die Position des ersten Leerzeichens in diesem Teilsatz ermittelt:
6

in B6 wird der Teil bis zum ersten Leerzeichen ausgegeben:
Eimer

In B6 dann B4 und B5 durch die entsprechenden Formeln ersetzen und dann weiter rückwärts B3, B2 und B1 durch die entspr. Formeln, bis nur noch der Zellbezug auf A1 besteht; - das ist dann die Endformel.

WF