Excel-Formel: Satz in Wörter aufteilen

Wie wir wissen, kann der Text in Spalten in Excel den Satz basierend auf dem Trennzeichen in Zellen aufteilen. In einigen Fällen möchten Sie jedoch eine Formel verwenden, um den Job zu lösen. In diesem Tutorial wird eine Array-Formel bereitgestellt, die damit umgehen kann.

Generische Formel:

=MID(" "&$TXT&" ", SMALL(IF(ISERROR(SEARCH(MID(" "&$TXT&" ", ROW($1:$99), 1), " ")), "", ROW($1:$99)+1), COLUMN(A1)), SMALL(IF(ISERROR(SEARCH(MID(" "&$TXT&" ", ROW($1:$99), 1), " ")), "", ROW($1:$99)), COLUMN(A1)+1)-SMALL(IF(ISERROR(SEARCH(MID(" "&$TXT&" ", ROW($1:$99), 1), " ")), "", ROW($1:$99)), COLUMN(A1))-1)

Argumente

Number: the sentence or text string you want to split to words in separated cells.

Wie diese Formel funktioniert

Nehmen Sie als Beispiel Zelle B3, Sie können die folgende Formel verwenden:

=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)

Presse Umschalt + Strg + Eingabetaste Taste, um das erste Wort zu extrahieren, und ziehen Sie dann den Füllpunkt nach rechts, bis alle Wörter extrahiert sind.

Erläuterung

MID Funktion Extrahiert eine Teilzeichenfolge aus einer Textzeichenfolge basierend auf der angegebenen Startposition und der Anzahl der Zeichen.

ROW Funktion Gibt die Anzahl der Zeilen zurück. Hier setzen wir ein Array von 1 bis 99.

Dann die Formel MITTE ("" & $ B2 & "", REIHE ($ 1: $ 99), 1)
= MID ("Klasse A Grad 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 des bestimmten Zeichens oder Textes aus der angegebenen Textzeichenfolge zurück. Wird keine Zeichenfolge gefunden, gibt die Funktion einen Fehlerwert zurück.

Die Formel SUCHE (MITTE ("& $ B3 &" ", REIHE ($ 1: $ 99), 1)," ")
= {1; #VALUE !; # VALUE !; # VALUE !; # VALUE !; # VALUE !; 1; #VALUE !; 1; #VALUE !; # VALUE!;
#VALUE !; # VALUE !; # VALUE !; 1; #VALUE !; 1; #VALUE !; # VALUE !; # VALUE !; # VALUE !; 1;
#VALUE!;#VALUE!;#VALUE!;#VALUE!;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 TRUE zurück, wenn der Wert ein Fehler ist.

Dann Formel ISERROR (SUCHE (MITTE ("" & $ B3 & "", REIHE ($ 1: $ 99), 1), ""))
= {FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE;
TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE;
FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE;
FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE;
FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE;
FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE;
FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE;
FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE;
FALSCH}

IF Funktion Tests für eine bestimmte Bedingung. Hier hat es drei Argumente, das erste muss ein logischer Ausdruck sein, wenn das erste Argument "TRUE" ist, dann wird das zweite Argument angezeigt und wenn das erste Argument "FALSE" ist, wird das Argument 3 angezeigt.

Die Formel IF (ISERROR (SUCHE (MITTE ("" & $ B3 & "", REIHE ($ 1: $ 99), 1), "")), "", REIHE ($ 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 dann den kleinsten Wert im Array zurück.

COLUMN Funktion Gibt die Anzahl der Spalten zurück, in denen die Formel angezeigt wird, oder gibt die Spaltennummer der angegebenen Referenz zurück.

Dann
KLEIN (WENN (ISERROR (SUCHE (MITTE ("" & $ B3 & "", REIHE ($ 1: $ 99), 1), "")), "", REIHE ($ 1: $ 99)), SPALTE (A1))
= {1}

Dann die Formel
KLEIN (WENN (ISERROR (SUCHE (MITTE ("" & $ B3 & "", REIHE ($ 1: $ 99), 1), "")), "", REIHE ($ 1: $ 99)), SPALTE (A1) +1 ) - KLEIN (WENN (ISERROR (SUCHE (MITTE ("" & $ B3 & "", REIHE ($ 1: $ 99), 1), "")), "", REIHE ($ 1: $ 99)), SPALTE (A1) )
= {7-1}
= {6}

Dann Formel
MID ("" & $ B3 & "", SMALL (IF (ISERROR (SUCHE (MID ("& $ B3 &" ", ROW ($ 1: $ 99), 1)," "))," ", ROW ($ 1: $ 99) +1), SPALTE (A1)), KLEIN (WENN (ISERROR (SUCHE (MITTE ("& $ B3 &" ", REIHE ($ 1: $ 99), 1)," "))," ", REIHE ( $ 1: $ 99)), SPALTE (A1) +1) - KLEIN (WENN (ISERROR (SUCHE (MITTE ("& $ B3 &" ", REIHE ($ 1: $ 99), 1)," "))," ", REIHE ($ 1: $ 99)), SPALTE (A1)) - 1)
= MID ("& $ B3 &", 2,6-1)
= {Klasse}

Beispieldatei

Klicken Sie hier, um die Beispieldatei herunterzuladen

---

Relative Formeln

• Auszug von rechts bis zum Charakter
  Hier wird die Formel zum Extrahieren von Text von der rechten Seite eines bestimmten Textes bis zu einem bestimmten Zeichen eingeführt.
• Text auf N Wörter zuschneiden
  Hier wird die Formel zum Extrahieren von n Wörtern von der linken Seite einer Textzeichenfolge eingeführt.
• Fügen Sie Leaing-Nullen hinzu, um die Länge festzulegen
  Dieses Tutorial enthält Formeln zum Hinzufügen einer führenden Null, um den Text auf die gleiche Länge zu bringen.
• Geteilte Dollar und Cent
  Aufteilen von Dollar und Cent in zwei Spalten mit Formeln in Excel.

---

Relative Funktionen

• MID-Funktion
  Extrahieren Sie einen Teil des Textes aus einem Zellenwert basierend auf der Position und Länge.
• COLUMN-Funktion
  Gibt die Anzahl der Spalten zurück, in denen die Formel angezeigt wird, oder gibt die Spaltennummer zurück.
• ROW-Funktion
  Holen Sie sich die Anzahl der Zeilen.
• Suchfunktion
  Suchen Sie die Position eines bestimmten Zeichens oder Teilstrings aus der angegebenen Textzeichenfolge.
• WENN Funktion
  Test auf eine bestimmte Bedingung.