Формула Excel: Разделение предложения на слова

В Excel, как известно, функция «Текст по столбцам» позволяет разделять предложение по ячейкам на основе разделителя. Однако иногда требуется решить эту задачу с помощью формулы. В данном руководстве представлено массивное формула, которая с этим справляется.

Универсальная формула:

Аргументы

Как работает эта формула

Возьмём ячейку B3 в качестве примера. Вы можете воспользоваться следующей формулой:

Нажмите Shift + Ctrl + Enter, чтобы извлечь первое слово, затем перетащите маркер заполнения вправо, пока не будут извлечены все слова.

Пояснение

MIDФункция извлекает подстроку из текстовой строки, начиная с указанной позиции и включая заданное количество символов.

ROWФункция возвращает номер строки; здесь она настроена так, чтобы вернуть массив от 1 до 99.

Затем формула 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Функция возвращает позицию указанного символа или фрагмента текста в заданной строке. Если искомый текст не найден, функция возвращает ошибку.

Формула SEARCH(MID(« »&$B3&« », ROW($1:$99), 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;1;1;
1;1;1;1;1}

Функция ISERROR возвращает ИСТИНА, если значение является ошибкой.

Затем формула ISERROR(SEARCH(MID(« »&$B3&« », ROW($1:$99), 1), « »))
={ЛОЖЬ;ИСТИНА;ИСТИНА;ИСТИНА;ИСТИНА;ИСТИНА;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА;ИСТИНА;ИСТИНА;
ИСТИНА;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА;ИСТИНА;ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА;ИСТИНА;ИСТИНА;
ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;
ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;
ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;
ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;
ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;
ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;
ЛОЖЬ}

IFфункция проверяет, выполняется ли определённое условие. Она принимает три аргумента: первый должен быть логическим выражением; если оно равно «ИСТИНА», отображается второй аргумент, а если «ЛОЖЬ» — третий.

Формула 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)), COLUMN(A1))
={1}

Затем формула
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}

Затем формула
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}

Пример файла

Нажмите, чтобы скачать пример файла

Связанные формулы

• Извлечение справа до символа
  Здесь представлена формула для извлечения части текста справа от заданной строки вплоть до указанного символа.
• Обрезка текста до N слов
  Здесь представлена формула для извлечения первых N слов из начала текстовой строки.
• Добавление ведущих нулей для фиксированной длины
  В этом руководстве вы найдёте формулы для добавления ведущих нулей, чтобы привести текст к одинаковой длине.
• Разделение долларов и центов
  Узнайте, как с помощью формул в Excel разделить сумму на доллары и центы — и разместить их в двух отдельных столбцах.

Связанные функции

• Функция ПСТР (MID)
  Извлекает фрагмент текста из значения ячейки, начиная с указанной позиции и заданной длины.
• Функция СТОЛБЕЦ (COLUMN)
  Возвращает номер столбца, в котором расположена формула, или номер заданного столбца.
• Функция СТРОКА (ROW)
  Возвращает номер текущей строки.
• Функция ПОИСК (SEARCH)
  Находит позицию указанного символа или подстроки в заданной текстовой строке.
• Функция ЕСЛИ (IF)
  Проверяет, выполняется ли заданное условие.