Формула Excel: разделите предложение на слова
В Excel, как мы знаем, Text to Columns может разбивать предложение на ячейки на основе разделителя. Но в некоторых случаях вы можете использовать формулу для решения задачи. В этом руководстве приводится формула массива, которая может с этим справиться.
Общая формула:
| =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 + Ввод нажмите клавишу, чтобы извлечь первое слово, затем перетащите маркер заполнения вправо, пока не будут извлечены все слова.
объяснение
MID функция извлекает подстроку из текстовой строки на основе указанной начальной позиции и количества символов.
ROW функция возвращает номер строки, здесь мы устанавливаем его для возврата массива от 1 до 99.
Тогда формула MID ("" & $ B2 & "", СТРОКА (1 $: 99 $), 1)
= MID («Ядро лилии класса A, 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 функция возвращает расположение определенного символа или текста из заданной текстовой строки. Если строка не найдена, функция возвращает значение ошибки.
Формула ПОИСК (MID ("" & $ 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 функция возвращает ИСТИНА, если значение является ошибкой.
Тогда формула ISERROR (SEARCH (MID ("" & $ B3 & ""; ROW ($ 1: $ 99); 1), ""))
= {FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; 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 функция тесты на конкретное состояние. Здесь у него три аргумента, первый должен быть логическим выражением, если первый аргумент - «ИСТИНА», то отображается второй аргумент, а если первый аргумент - «ЛОЖЬ», отображается аргумент 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 функция возвращает номер столбца, в котором появляется формула, или возвращает номер столбца данной ссылки.
Затем
МАЛЕНЬКИЙ (ЕСЛИ (ЕСТЬ ОШИБКА (ПОИСК (СРЕДНЕЕ ("" & $ B3 & "", СТРОКА (1 $: 99 $); 1), "")), "", СТРОКА (1 $: 99 $)), COLUMN (A1))
= {1}
Тогда формула
МАЛЕНЬКИЙ (ЕСЛИ (ЕСТЬ ОШИБКА (ПОИСК (СРЕДНЕЕ ("" & $ B3 & "", СТРОКА (1 долл. США), 99), "")), "", СТРОКА (1 долл. США: 1 долл. США)), COLUMN (A99) +1 ) - МАЛЕНЬКИЙ (ЕСЛИ (ЕСТЬ ОШИБКА (ПОИСК (СРЕДНЕЕ ("" & $ B1 & "", СТРОКА (3 $: 1 $), 99), "")), "", СТРОКА (1 $: 1 $)), COLUMN (A99) )
= {7-1}
= {6}
Тогда формула
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), "")), "", СТРОКА (1 $: 99 $)), КОЛОННА (A1)) - 1)
= MID ("" & $ B3 & "", 2,6-1)
= {Класс}
Файл примера
Нажмите, чтобы загрузить образец файла
Относительные формулы
- Отрывок справа до символа
Здесь вводится формула для извлечения текста с правой стороны заданного текста до указанного символа. - Обрезать текст до N слов
Здесь вводится формула для извлечения n слов из левой части текстовой строки. - Добавьте конечные нули, чтобы зафиксировать длину
В этом руководстве представлены формулы для добавления нуля в начале, чтобы текст имел одинаковую длину. - Разделить доллары и центы
Чтобы разделить доллары и центы на два столбца с формулами в Excel.
Относительные функции
- Функция MID
Извлечь часть текста из значения ячейки в зависимости от местоположения и длины. - КОЛОНКА функция
Возвращает номер столбца, в котором отображается формула, или номер столбца. - СТРОКА функция
Получите номер строки. - ПОИСК функция
Найдите позицию определенного символа или подстроки в заданной текстовой строке. - Функция IF
Проверьте конкретное состояние.
Лучшие инструменты для работы в офисе
Kutools for Excel - Помогает вам выделиться из толпы
Kutools for Excel Имеет более 300 функций, Гарантия того, что то, что вам нужно, находится на расстоянии одного клика...
Office Tab - Включить чтение и редактирование с вкладками в Microsoft Office (включая Excel)
- Одна секунда для переключения между десятками открытых документов!
- Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
- Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.
