Excel-Formel: Satz in Wörter aufteilen

In Excel können wir bekanntlich mithilfe der Funktion „Text in Spalten“ einen Satz anhand eines Trennzeichens auf mehrere Zellen aufteilen. In manchen Fällen möchten Sie diese Aufgabe jedoch lieber mit einer Formel erledigen – und genau dafür bietet Ihnen dieses Tutorial eine leistungsstarke Matrixformel.

Allgemeine Formel:

Argumente

Funktionsweise dieser Formel

Nehmen Sie beispielsweise Zelle B3 und verwenden Sie dazu die folgende Formel:

Drücken Sie Umschalt + Strg + Enter, um das erste Wort zu extrahieren, und ziehen Sie anschließend den Ausfüllkästchen nach rechts, bis alle Wörter extrahiert sind.

Erläuterung

MID-Funktion extrahiert einen Teilstring aus einer Textzeichenfolge – basierend auf der angegebenen Startposition und der Anzahl der Zeichen.

ROW-Funktion gibt die Zeilennummer zurück – hier so konfiguriert, dass ein Array von 1 bis 99 ausgegeben wird.

Die Formel MID(" "&$B2&„ ", ROW($1:$99), 1)
=MID(" Class A Grade 3 Lily Core", {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21; 22; 23; 24; 25; 26; 27; 28; 29; 30; 31; 32; 33; 34; 35; 36; 37; 38; 39; 40; 41; 42; 43; 44; 45; 46; 47; 48; 49; 50; 51; 52; 53; 54; 55; 56; 57; 58; 59; 60; 61; 62; 63; 64; 65; 66; 67; 68; 69; 70; 71; 72; 73; 74; 75; 76; 77; 78; 79; 80; 81; 82; 83; 84; 85; 86; 87; 88; 89; 90; 91; 92; 93; 94; 95; 96; 97; 98; 99}, 1)
={" ";"C";"l";"a";"s";"s";" ";"A";" ";"G";"r";"a";"d";"e";" ";"3";" ";"L";"i";"l";"y";" ";"C";"o";"r";"e";" ";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";„";
"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";„"}

SEARCH-Funktion gibt die Position eines bestimmten Zeichens oder Texts innerhalb der angegebenen Textzeichenfolge zurück. Wird der Text nicht gefunden, liefert die Funktion einen Fehlerwert.

Die Formel SEARCH(MID(" "&$B3&„ ", ROW($1:$99), 1), „ ")
={1;#WERT!;#WERT!;#WERT!;#WERT!;#WERT!;1;#WERT!;1;#WERT!;#WERT!;
#WERT!;#WERT!;#WERT!;1;#WERT!;1;#WERT!;#WERT!;#WERT!;#WERT!;1;
#WERT!;#WERT!;#WERT!;#WERT!;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;
1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;
1;1;1;1;1}

ISERROR-Funktion gibt WAHR zurück, wenn der Wert ein Fehler ist.

Die Formel ISERROR(SEARCH(MID(" "&$B3&„ ", ROW($1:$99), 1), „ "))
={FALSCH;WAHR;WAHR;WAHR;WAHR;WAHR;FALSCH;WAHR;FALSCH;WAHR;WAHR;WAHR;WAHR;
WAHR;FALSCH;WAHR;FALSCH;WAHR;WAHR;WAHR;WAHR;FALSCH;WAHR;WAHR;WAHR;WAHR;
FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;
FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;
FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;
FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;
FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;
FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;
FALSCH}

IF-Funktion prüft eine bestimmte Bedingung und verfügt dabei über drei Argumente: Das erste muss ein logischer Ausdruck sein. Ist das erste Argument „WAHR“, wird das zweite Argument angezeigt; ist es „FALSCH“, erscheint das dritte Argument.

Die Formel IF(ISERROR(SEARCH(MID(" "&$B3&„ ", ROW($1:$99), 1), " ")), „", ROW($1:$99))
={1;"";"";"";"";"";7;"";9;"";"";"";"";"";15;"";17;"";"";"";"";22;"";"";"";„";27;28;29;30;31;32;33;34;35;
36;37;38;39;40;41;42;43;44;45;46;47;48;49;50;51;52;53;54;55;56;57;58;59;60;61;62;63;
64;65;66;67;68;69;70;71;72;73;74;75;76;77;78;79;80;81;82;83;84;85;86;87;88;89;90;91;92;
93;94;95;96;97;98;99}

SMALL-Funktion gibt anschließend den kleinsten Wert im Array zurück.

COLUMN-Funktion gibt die Nummer der Spalte zurück, in der sich die Formel befindet, oder liefert die Spaltennummer einer angegebenen Referenz.

Dann
SMALL(IF(ISERROR(SEARCH(MID(" "&$B3&„ ", ROW($1:$99), 1), " ")), „", ROW($1:$99)), COLUMN(A1))
={1}

Die Formel
SMALL(IF(ISERROR(SEARCH(MID(" "&$B3&„ ", ROW($1:$99), 1), " ")), „", ROW($1:$99)), COLUMN(A1)+1)-  SMALL(IF(ISERROR(SEARCH(MID(" "&$B3&„ ", ROW($1:$99), 1), " ")), „", ROW($1:$99)), COLUMN(A1))
={7-1}
={6}

Die Formel
MID(" "&$B3&„ ", SMALL(IF(ISERROR(SEARCH(MID(" "&$B3&„ ", ROW($1:$99), 1), " ")), „", ROW($1:$99)+1), COLUMN(A1)), SMALL(IF(ISERROR(SEARCH(MID(" "&$B3&„ ", ROW($1:$99), 1), " ")), „", ROW($1:$99)), COLUMN(A1)+1)-SMALL(IF(ISERROR(SEARCH(MID(" "&$B3&„ ", ROW($1:$99), 1), " ")), „", ROW($1:$99)), COLUMN(A1))-1)
=MID(" "&$B3&„ ",2,6-1)
={Class}

Beispieldatei

Klicken Sie, um die Beispieldatei herunterzuladen

Verwandte Formeln

• Von rechts extrahieren bis zu einem Zeichen
  Hier stellen wir die Formel vor, mit der Sie Text von der rechten Seite einer Zeichenfolge bis zu einem bestimmten Zeichen extrahieren können.
• Text auf N Wörter kürzen
  Hier stellen wir die Formel vor, mit der Sie die ersten n Wörter einer Textzeichenfolge extrahieren können.
• Führende Nullen hinzufügen, um eine feste Länge zu erreichen
  Dieses Tutorial liefert praktische Formeln, mit denen Sie führende Nullen hinzufügen und Ihren Text so auf eine einheitliche Länge bringen können.
• Dollar und Cent aufteilen
  So trennen Sie Dollar und Cent mithilfe von Formeln in Excel in zwei separate Spalten.

Verwandte Funktionen

• MID-Funktion
  Extrahiert einen Textabschnitt aus einem Zellenwert anhand von Startposition und Länge.
• COLUMN-Funktion
  Gibt die Nummer der Spalte zurück, in der sich die Formel befindet, oder liefert die Spaltennummer einer angegebenen Referenz.
• ROW-Funktion
  Gibt die Zeilennummer zurück.
• SEARCH-Funktion
  Findet die Position eines bestimmten Zeichens oder Teilstrings innerhalb einer angegebenen Textzeichenfolge.
• IF-Funktion
  Prüft eine bestimmte Bedingung.