Office: String oder .csv mit Trennzeichen per Formel auf Zellen aufteilen

Einleitung und Problematik

Irgendwann ca. 2012 (so bei Excelformeln.de in der 210, 301, 326 und 455 dokumentiert, aber eigentlich noch vorher) störte es mich, dass man mit langen (und pro Feld auch noch wechselnden) Funktionen wie SUCHEN und mit vielfacher Erwähnung des Aufteilstrings diesen umständlich auseinanderpflücken muss. Ich überlegte, welche Funktion man hier besser einsetzen könnte.

Idee und Lösung

Was, wenn man die Trenner aufspreizt, und in gleichgroßem Abstand nach Inhalten fischt? Die rutschen in dem "Intervall" dann nur immer mehr nach rechts. Ich illustriere mal wie folgt:

A1: |Schere Stein Papier|

Das sind ungefähr 20 Buchstaben (ohne Trennzeichen, nicht genau gezählt). Um meine Formel wiedererkennbar zu machen, nehme ich als Aufspreizungsfaktor Zahlen mit 9 in der Einerstelle, hier also 19.

B1: |Schere_____________|_____Stein_________|__________Papier____|

=WECHSELN($A1;" ";WIEDERHOLEN(" ";19)) sorgt dafür. Wichtig ist, dass die 19 nicht kleiner ist, als die Addition aller Zeichen abzüglich der Trenner. Häufiger wird man also eine 99, eine 299 oder gar 999 hier finden. Für dieses Beispiel reicht die 19.

Nun wollen wir aber an den senkrechten Strichen auch tatsächlich teilen:

B1[:D1]: =TEIL(WECHSELN($A1;" ";WIEDERHOLEN(" ";19));SPALTE(A1)*19-18;19)

schreibt

B1: |Schere_____________|
C1: |_____Stein_________|
D1: |_________Papier____|

in die Zellen.

Bitte beachte, dass sich die Zahl 19 insgesamt 4 Mal wiederfindet, davon einmal um 1 auf 18 reduziert!

Die Leerzeichen, hier dargestellt durch den Unterstrich, müssen noch weg:

B1[:D1]: =GLÄTTEN(TEIL(WECHSELN($A1;" ";WIEDERHOLEN(" ";19));SPALTE(A1)*19-18;19))

ergibt endgültig

B1: Schere
C1: Stein
D1: Papier

Wir haben unser Ziel erreicht!

Vorteil 1: Auf $A1 wird nur ein einziges Mal Bezug genommen! Das bedeutet, dass man dafür auch beliebig lange Ausdrücke einsetzen kann, ohne dass man Hilfszellen verwalten müsste (weil der Ausdruck in der Formel nämlich nur einmal rechnen muss).

Vorteil 2: Die Formel ist in alle Zellen kopierbar. Jeder Modellentwickler weiß das zu schätzen!

Vorteil 3: Die Formel ist relativ kurz, und sie besitzt Wiedererkennungswert. Die alten SUCHEN()-Konstrukte waren hingegen immer höchst individuell und überhaupt nicht "zu lesen".

Vorteil 4: Nimmt man statt $A1 ein A$1, und ersetzt SPALTE(A1) durch ZEILE(A1), hat man die Ergebnisse untereinander, statt nebeneinander.

Vorteil 5: Die Formel kann in die verbleibende Dimension gespillt werden (oder als Formelarray abgeschlossen werden).

Mit Parametern wird die gerade genannte Formel vielleicht noch etwas deutlicher:

B1: =GLÄTTEN(TEIL(WECHSELN($A1;" ";WIEDERHOLEN(" ";
LängeDesStringsOhneTrenner));AbWievieltemWort*
LängeDesStringsOhneTrenner-(
LängeDesStringsOhneTrenner-1);
LängeDesStringsOhneTrenner*
WievieleWörterZusammenInEinerZelleTrotzTrenner))

Die Länge eines Formelergebnisses darf seit xl2007 32k Zeichen betragen. Dies ist nun das Produkt aus zulässiger verketteter Aufteilstringlänge (ohne Trenner) und Anzahl der Wörter darin, welches nicht überschritten werden darf.
_______________________
EDIT 18.07.2020 xl2013+: Counting Dates - Microsoft Tech Community hat einen unbeschränkten SPLITTEN-Ersatz (aufgespürt von <shift-del>):

B1: =XMLFILTERN("<a><b>"&WECHSELN(A1;"/";"</b><b>")&"</b></a>";"//b")
B1: =XMLFILTERN(WECHSELN(a&A1&c;" ";b);d) mit sinnvoll ausgegliederten Konstanten (Zelle oder benannte Fml)!

Allerdings hat die XMLFILTERN-Lösung nicht den Vorteil 5 von der o.g. GLÄWEXWDH-Lösung.

Anwendungsfeld

Ich habe in den Foren bestimmt schon 100 Mal mit der Formel geantwortet. Aus dem Beispiel oben kann man den Standardfall erkennen. Aber man kann damit auch Zellinhalte rechnerisch auswerten:

A1: 2 -3 3,4 Saft -1,2% -,3 Milch 1001 -0,00001 ergibt 1003,08799 mit

B1: {=SUMME(WENNFEHLER(--TEIL(WECHSELN(A1;" ";WIEDERHOLEN(" ";99));ZEILE($1:$19)*99-98;99);0))}

(diese Formel bitte nur ohne {} eingeben, und statt mit EINGABE erweitert mit STRG-UMSCHALT-EINGABE abschließen!)

Wir sehen daran:

1. Eingestreute Texte in A1 sind unschädlich.

2. -,3 statt -0,3 ist als "Summand" möglich. Ebenso ein angehängtes %-Zeichen für /100 ... usw. (jedoch nicht wie bei Formeln mehrere % hintereinander).

3. In der Formel B1 kann bei Zahlenauswertung auf GLÄTTEN verzichtet werden, weil Excel die Leerzeichen beim Umwandlungsversuch mit dem Doppelminus von selbst wegnimmt.

In Verbindung mit meiner WENNNULLODERNEGATIV-Funktion kann man beispielsweise nur die positiven Zahlen zu 1006,4 addieren:

{=SUMME(WENNFEHLER(EXP(LN(TEIL(WECHSELN(A1;" ";WIEDERHOLEN(" ";99));ZEILE($1:$19)*99-98;99)));""))}

Der Mittelwert der nur negativen ergibt sich zu -0,8280025 durch zwei eingefügte Spiegelungen um Null herum aus

{=MITTELWERT(WENNFEHLER(-EXP(LN(-TEIL(WECHSELN(A1;" ";WIEDERHOLEN(" ";99));ZEILE($1:$19)*99-98;99)));""))}

Wichtig dabei: Bei den beiden letzten Auswertungen mit Bedingung muss statt 0 das "" als 2. WENNFEHLER-Argument verwendet werden, da die 0 sonst in den MITTELWERT mit eingeht!

Im Konstruktionsmakro unten findet man auch noch die Formel für Einträge von rechts.

Fazit

Die o.g. Formel löst in den meisten Fällen das Problem der Stringaufteilung (etwa bei Textimporten) mit einer aufgrund einer schmutzigen Idee standardisierten Formel. Außerdem nimmt sie den eifrigen "Zahlen-in-einer-Zelle"-Sammlern, die es immer noch gibt, die Arbeit ab, diese Zahlen z.B. mit SUMME() auszuwerten.

Die Formel ist jedoch nicht unbegrenzt anwendbar, da sie drei Limitationen hat: Stringlänge einer Zelle, Anzahl Aufteilungen und maximale Länge eines Teils. VBA - Zeilenumbruch in extra Zeilen - MS-Office-Forum schafft hier Abhilfe:

Schere-Stein-Papier-Beispiel: B1[:D1]: =TEIL($A1;
FINDEN("#";WECHSELN(" "&$A1&"#";" ";"#";SPALTE(A1)+0));
FINDEN("#";WECHSELN(" "&$A1&"#";" ";"#";SPALTE(A1)+1))-
FINDEN("#";WECHSELN(" "&$A1&"#";" ";"#";SPALTE(A1)+0)))

Baue Dir die Datei selbst

Sub ZellaufteilungsformelAehnlichSplit()
Workbooks.Add xlWorksheet
Kern = "MID(SUBSTITUTE(R1C1,"" "",REPT("" "",99))," 'Kernteil der Formel
Eintrag = "ROW(R1:R19)*99-98,99)" 'einzelner Eintrag
Anzahl = "LEN(R1C1)-LEN(SUBSTITUTE(R1C1,"" "",))+1" 'Anzahl Verkettungseintraege
Quelle = "Office-Hilfe" 'Ort der Doku
[A1] = "2 -3 3,4 Saft -1,2% -,3 Milch 1001 -0,00001"
[A3] = "Spaltenaufteilung:"
[A4:J4].FormulaR1C1 = "=TRIM(" & Kern & "COLUMN(RC)*99-98,99))"
[A6] = "Viert- und drittletzter Eintrag aus A1:"
[A7:B7].FormulaR1C1 = "=TRIM(" & Kern & "(" & Anzahl & "-4+COLUMN(RC))*99-98,99))"
[A9] = "Summe der Zahlen in der Zelle A1:"
[A10].FormulaArray = "=SUM(IFERROR(--" & Kern & Eintrag & ",0))"
[A12] = "beschränkt auf die positiven Zahlen:"
[A13].FormulaArray = "=SUM(IFERROR(EXP(LN(" & Kern & Eintrag & ")),""""))"
[A15] = "Mittelwert nur der negativen Zahlen in der Zelle A1:"
[A16].FormulaArray = "=AVERAGE(IFERROR(-EXP(LN(-" & Kern & Eintrag & ")),""""))"
[A18] = "Quelle Konstruktionsmakro:": [A19] = Quelle & 22254
[A21] = "Quelle WENNNULLODERNEGATIV:": [A22] = Quelle & 21725
End Sub


Beispiele der Formel in verschiedenen Foren

2017.10.03 Mit den Referenzdaten a) einer Spalte oder b) einer anderen einzelnen Zelle übereinstimmende Zahlen einer Zelle addieren
2017.10.04 Alle Zahlen in einer Zelle (mit Leerzeichen und CR) addieren
2017.10.04 Die Zahlen einer Zelle, die zu einem bestimmten Namen gehören, für mehrere solche Zellen namensbezogen addieren
2018.04.27 Nur bei 3 Leerzeichen aufteilen, nicht bei weniger!
2018.09.24 Mit + getrennte Zahlen in einer Text-Zelle - und dies für mehrere Zellen zusammen - addieren[/B]