Формула Excel: Розбийте речення на слова
Як відомо, в Excel текст до стовпців може розділяти речення на клітинки на основі роздільника. Але іноді для вирішення завдання потрібно використовувати формулу. У цьому посібнику він містить формулу масиву, яка може з цим впоратися.
Загальна формула:
| =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) |
Аргументи
| Number: the sentence or text string you want to split to words in separated cells. |
Як працює ця формула
Візьміть клітинку B3 як приклад, ви можете використовувати формулу нижче:
| =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) |
прес Shift + Ctrl + Enter для вилучення першого слова, а потім перетягніть маркер заповнення вправо, доки всі слова не будуть вилучені.
Пояснення
MID функція витягує підрядок з текстового рядка на основі вказаної вихідної позиції та кількості символів.
ROW функція повертає номер рядка, тут ми встановлюємо для нього повернення масиву від 1 до 99.
Потім формула MID ("" & $ B2 & "", ROW ($ 1: $ 99), 1)
= MID ("Серцевина лілії 3 класу", {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 функція повертає розташування конкретного символу або тексту із заданого текстового рядка. Якщо рядок не знайдено, функція повертає значення помилки.
Формула ПОШУК (СЕРЕДНЯ ("" & $ B3 & "", РЯД ($ 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 функція повертає TRUE, якщо значення є помилкою.
Тоді формула ISERROR (ПОШУК (ПОСЕРЕДНИЙ ("" & $ B3 & "", РЯД ($ 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;
ПОМИЛКОВИЙ}
IF функція тести на конкретний стан. Тут він має три аргументи, перший повинен бути логічним виразом, якщо перший аргумент “TRUE”, тоді відображається другий аргумент, а якщо перший аргумент “FALSE”, відображається аргумент 3.
Формула 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 функція потім повертає найменше значення в масиві.
COLUMN функція повертає номер стовпця, формула якого з'являється, або повертає номер стовпця заданого посилання.
Потім
SMALL (IF (ISERROR (SEARCH (MID ("" & $ B3 & "", ROW ($ 1: $ 99), 1), "")), "", ROW ($ 1: $ 99)), Стовпець (A1))
= {1}
Потім формула
SMALL (IF (ISERROR (SEARCH (MID ("" & $ B3 & "", ROW ($ 1: $ 99), 1), "")), "", ROW ($ 1: $ 99)), Стовпець (A1) +1 ) - SMALL (IF (ISERROR (SEARCH (MID ("" & $ B3 & "", ROW ($ 1: $ 99), 1), "")), "", ROW ($ 1: $ 99)), СТОЛБКА (A1) )
= {7-1}
= {6}
Тоді формула
MID ("" & $ B3 & "", SMALL (IF (ISERROR (SEARCH (MID ("" & $ B3 & "", ROW ($ 1: $ 99), 1), "")), "", ROW ($ 1: $ 99) +1), Стовпець (A1)), МАЛИЙ (ЯКЩО (ISERROR (ПОШУК (MID ("" & $ B3 & "", ROW ($ 1: $ 99), 1), "")), "", ROW ( $ 1: $ 99)), СТОЛЬБА (A1) +1) -МАЛЬКА (ЯКЩО (ISERROR (ПОШУК (MID ("" & $ B3 & "", РЯД ($ 1: $ 99), 1), "")), "", РЯДОК ($ 1: $ 99)), Стовпець (A1)) - 1)
= MID ("" & $ B3 & "", 2,6-1)
= {Клас}
Зразок файлу
Клацніть, щоб завантажити зразок файлу
Відносні формули
- Витяг справа від символу
Тут вводиться формула для вилучення тексту з правої сторони даного тексту до вказаного символу. - Обрізати текст на N слів
Тут вводиться формула для вилучення n слів з лівого боку текстового рядка. - Додайте похилі нулі, щоб зафіксувати довжину
Цей підручник містить формули для додавання початкового нуля, щоб зробити текст однаковою довжиною. - Спліт доларів і центів
Розбити долари та центи на дві колонки з формулами в Excel.
Відносні функції
- Функція MID
Витягніть частину тексту із значення комірки на основі розташування та довжини. - Функція COLUMN
Повертає номер стовпця, формула якого відображається, або повертає номер стовпця. - Функція ROW
Отримайте номер рядка. - Функція ПОШУК
Знайдіть позицію конкретного символу або підрядка із заданого текстового рядка. - Функція IF
Тест на конкретний стан.
Найкращі інструменти для підвищення продуктивності офісу
Kutools для Excel - допомагає виділитися з натовпу
Kutools для Excel має понад 300 функцій, Переконайтеся, що те, що вам потрібно, знаходиться лише на відстані одного кліка...
Вкладка Office - увімкніть читання та редагування вкладок у Microsoft Office (включаючи Excel)
- Одна секунда для перемикання між десятками відкритих документів!
- Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
- Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
- Додає ефективні вкладки в Office (включно з Excel), як у Chrome, Edge та Firefox.
