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.
Die besten Tools für die Office-Produktivität
Kutools für Excel - Hilft Ihnen, sich von der Masse abzuheben
Kutools für Excel bietet über 300 Funktionen, Stellen Sie sicher, dass das, was Sie brauchen, nur einen Klick entfernt ist ...
Office-Registerkarte - Aktivieren Sie das Lesen und Bearbeiten von Registerkarten in Microsoft Office (einschließlich Excel).
- Eine Sekunde, um zwischen Dutzenden offener Dokumente zu wechseln!
- Reduzieren Sie jeden Tag Hunderte von Mausklicks für Sie und verabschieden Sie sich von der Maushand.
- Erhöht Ihre Produktivität um 50 % beim Anzeigen und Bearbeiten mehrerer Dokumente.
- Bringt effiziente Tabs in Office (einschließlich Excel), genau wie Chrome, Edge und Firefox.
