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

Helfe beim Thema String oder .csv mit Trennzeichen per Formel auf Zellen aufteilen in Microsoft Excel Tutorials um das Problem gemeinsam zu lösen; Einleitung und Problematik Irgendwann ca. 2012 (so bei Excelformeln.de in der 210, 301, 326 und 455 dokumentiert, aber eigentlich noch vorher) störte... Dieses Thema im Forum "Microsoft Excel Tutorials" wurde erstellt von lupo1, 21. August 2017.

  1. lupo1
    lupo1 Tutorial Guru

    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]
     
    Zuletzt bearbeitet: 30. November 2022
Thema:

String oder .csv mit Trennzeichen per Formel auf Zellen aufteilen

Die Seite wird geladen...
  1. String oder .csv mit Trennzeichen per Formel auf Zellen aufteilen - Similar Threads - String csv Trennzeichen

  2. Leerzeichen abschließend im String

    in Microsoft Access Hilfe
    Leerzeichen abschließend im String: Hallo, ich habe hier eine DB, in der sich viele Leute textlich verwirklichen können. Teilweise mit unangenehmen Flüchtigkeitsfehlern (Montuer statt Monteur z.B.). Nun möchte ich, dass die...
  3. If-Then-Else...Verständnisfrage zu Kriterium String > Zahl

    in Microsoft Access Hilfe
    If-Then-Else...Verständnisfrage zu Kriterium String > Zahl: Hallo, ich habe eine Funktion erstellt. Diese wird mit Werten gefüttert, generell Zahlen als String und "echte" Strings. Damit ich mit den Zahlen Vergleiche anstellen kann wandele ich diese mit...
  4. Erlaubte Zeichen an bestimmten Stellen von Strings

    in Microsoft Excel Tutorials
    Erlaubte Zeichen an bestimmten Stellen von Strings: MATCH.CHAR benenne folgende Funktion: =LAMBDA(Bereich;z;[von];[Stellen];LET( v;1-WURDEAUSGELASSEN(von); s;WENN(WURDEAUSGELASSEN(Stellen);1;Stellen);...
  5. Mitarbeiterauslastung: Suchen nach String und addieren aller zugehörigen Zahlenwerte

    in Microsoft Excel Hilfe
    Mitarbeiterauslastung: Suchen nach String und addieren aller zugehörigen Zahlenwerte: Hallo zusammen, ich habe eine Liste mit Themen und Mitarbeitern die daran arbeiten, wo ich gerne die Gesamtauslastung der Mitarbeiter darstellen möchte: [ATTACH] Aktuell suche ich händisch nach...
  6. Mehrfach Werte in einem String separieren

    in Microsoft Access Hilfe
    Mehrfach Werte in einem String separieren: Hallo, ich brauche mal Eure Hilfe. Folgende Daten habe ich vorliegen: [ATTACH] Die Spalte "kein Product Type" enthält Artikelnummern, die mit Komma getrennt sind. Daraus sollen einzelne Werte...
  7. Suchen eins Strings

    in Microsoft Excel Hilfe
    Suchen eins Strings: Hallo, am Ende einer jeden Zeile in Excel2019 möchte ich gern eine Formel eingeben, die nach der Zelle in der jeweiligen Zeile sucht, die einen bestimmten String enthält. Dabei soll die Suche von...
  8. Alle Spalten einer Abfrage nach einem String filtern

    in Microsoft Access Hilfe
    Alle Spalten einer Abfrage nach einem String filtern: Hallo, Ich möchte in der Abfrage einen Filter mit LIKE '*Männlich*' setzen. Auf die GESAMTE Abfrage, nicht nur auf eine Spalte! Meine aktuell Lösung ist manuell die ganzen Spalten separat...
  1. Diese Seite verwendet Cookies, um Inhalte zu personalisieren, diese deiner Erfahrung anzupassen und dich nach der Registrierung angemeldet zu halten.
    Auf dieser Website werden Cookies für die Zugriffsanalyse und Anzeigenmessung verwendet.
    Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden