Das grosse Archiv mit 637
VBA-freien Formellösungen und Tips
 
   
 
 ÜBER UNS   NEWS   FORMELN   TIPPS   KONTAKT   FORUM   LINKS 
   
Übersicht   Zauberbuch   Rätselbuch   Sparbuch   Bücher-Downloads / Errata  
 
Textfunktionen (2) ->

Bestimmte Worte aus einem String herauspicken - automatisiert (301)
 
 
Aufgabe
Aus einem Text (Worte getrennt durch Leerzeichen) soll ein Wort an der Stelle X von vorne bzw. von hinten ausgegeben werden.

Lösung
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.

Erläuterung
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



 
    zurück


 
powered by telltarget