Извлечь из текста список слов

Извлечь из ячейки слова, которые есть в вашем списке, можно формулой =ОБЪЕДИНИТЬ(" ";ИСТИНА;ЕСЛИ(ЕЧИСЛО(ПОИСК(" "&$D$2:$D$4&" ";" "&A2&" "));$D$2:$D$4;"")): ПОИСК проверяет каждое слово списка D2:D4 целиком (с пробелами по краям), ЕСЛИ оставляет найденные, а ОБЪЕДИНИТЬ склеивает их через пробел. Получить список всех слов из текста в Excel 365 можно функцией =ТЕКСТРАЗД(A2;;" "), а для сотен слов и тысяч строк, готовых списков маркеров и частей речи удобнее процедуры !SEMTools.

В работе с большими текстовыми массивами бывает необходимость найти в столбце слова из списка. Но не просто найти, а, если ячейка содержит слова из диапазона, указать сами эти слова. Если список слов невелик, эту задачу можно сделать с помощью расширенного фильтра, но если нет, задача превращается в трудновыполнимую. Особенно, если исходный текстовый массив содержит сотни-тысячи строк. Основные проблемы:

• Слова нельзя искать по обычному вхождению - короткие слова могут быть частью других слов. Например, "эко" входит в "эконом", а "эконом" - в "экономичный".
• Если задача состоит в том, чтобы найти все слова из списка, а не первое попавшееся, содержимое ячейки нельзя очищать от лишнего содержимого, пока не будет закончен поиск всех слов и не будут помечены найденные.

Далее подробнее о том, как с помощью надстройки !SEMTools решить эти задачи.

Извлечь слова из списка формулой

Формула работает в Excel 2019 и новее (в 2019 — ввод через Ctrl+Shift+Enter). Пробелы, добавленные к слову и к тексту ячейки, защищают от ложных совпадений: «эко» не найдётся внутри «эконом».

Если нужно только первое найденное слово, в Excel 365 подойдёт =ВЗЯТЬ(ФИЛЬТР($D$2:$D$4;ЕЧИСЛО(ПОИСК(" "&$D$2:$D$4&" ";" "&A2&" "));"");1). На тысячах строк и сотнях слов формулы ощутимо тормозят книгу — каждая ячейка сравнивается со всем списком.

Извлечь слова из списка в 2 клика с !SEMTools

Данный функционал позволяет моментально удалить из текста все слова, кроме тех, что находится в списке который вы ему укажете, таким образом извлекая эти слова. У процедуры есть близкие по смыслу - найти слова из списка и удалить из текста список слов.

Чтобы сохранить исходные данные, нужно скопировать столбец и извлекать слова из него. На примере ниже из более 5000 скопированных ячеек извлекаются несколько сотен слов.

Происходит следующее: если в ячейке имеется слово из диапазона, оно остается, если нет - удаляется. Таким образом, если в строке несколько слов, встречающиеся в диапазоне, останутся они все.

Для 100% эффективного поиска необходимо удалить все знаки препинания из ячеек. Процедура нечувствительна к регистру. Важно еще раз подчеркнуть - ищутся и извлекаются именно слова, сколь короткими бы они ни были. Если в диапазоне содержатся не только слова, но произвольный текст, нужно использовать похожий, но слегка иной, функционал извлечь словосочетания, т.к. любые двусловные, трехсловные и т.д. фразы в режиме извлечения слов будут пропущены при поиске.

Извлечь слова из готовых списков маркеров

Макросы этой группы позволяют извлекать маркеры аренды, продажи, отзывов, и будут пополняться новыми списками сущностей по мере развития надстройки. Они довольно популярны среди специалистов по SEO и контекстной рекламе. Ниже пример извлечения коммерческих маркеров.

Извлечь из ячейки определенные части речи

Иногда бывает полезным извлечь из строк глаголы, прилагательные, существительные или наречия. Во многих тематиках определенная часть речи является ярким паттерном сегмента аудитории, как целевого, так и нет.

Извлечь из текущего списка все уникальные слова

Подробно о том, как проанализировать список фраз и собрать уникальные слова из него, я рассказал в другой статье: N-gram анализ в Excel.

Список слов из текста формулой

В Excel 365 текст ячейки раскладывается на слова в столбец функцией =ТЕКСТРАЗД(A2;;" ") (две точки с запятой — разделитель строк, а не столбцов). Список уникальных слов из целого столбца даёт комбинация:

=УНИК(ТЕКСТРАЗД(ОБЪЕДИНИТЬ(" ";ИСТИНА;A2:A100);;" "))

Ограничение: ОБЪЕДИНИТЬ возвращает не больше 32 767 символов, поэтому на больших столбцах формула выдаст #ЗНАЧ!. Для таких объёмов, а также чтобы сразу посчитать частоту слов, используйте N-gram анализ.

Почему слова не извлекаются

• Слово стоит рядом со знаком препинания. В «купить, недорого» после слова запятая, и формула с пробелами его не найдёт. Сначала удалите знаки препинания.
• Извлекается слово внутри другого слова. В формуле нет пробелов вокруг искомого слова и текста ячейки: ПОИСК("эко";A2) найдёт и «эконом».
• Формула возвращает #ИМЯ?. ОБЪЕДИНИТЬ появилась в Excel 2019, ТЕКСТРАЗД, ВЗЯТЬ и УНИК — в Excel 365 и 2021. В старых версиях используйте процедуру !SEMTools.
• Не находятся словоформы. Список «диван» не извлечёт «диваны» и «дивана». Приведите слова к начальной форме лемматизацией или добавьте формы в список.

Извлечение слов в Excel — частые вопросы

Как извлечь из ячейки слова из списка?

Формулой =ОБЪЕДИНИТЬ(" ";ИСТИНА;ЕСЛИ(ЕЧИСЛО(ПОИСК(" "&$D$2:$D$4&" ";" "&A2&" "));$D$2:$D$4;"")) или процедурой !SEMTools «Извлечь слова из списка» — она работает быстрее на больших объёмах.

Как получить список всех слов из текста?

В Excel 365 — =ТЕКСТРАЗД(A2;;" ") для одной ячейки и =УНИК(ТЕКСТРАЗД(ОБЪЕДИНИТЬ(" ";ИСТИНА;A2:A100);;" ")) для уникальных слов целого столбца.

Как извлечь только первое слово?

Формулой =ЛЕВСИМВ(A2;ПОИСК(" ";A2&" ")-1) — подробно в статье как извлечь слова из ячейки.

Различает ли поиск регистр?

Нет: ПОИСК и процедура !SEMTools не различают регистр. Если регистр важен, замените ПОИСК на НАЙТИ.

Итоги: как извлечь слова из текста в Excel

Надеюсь, на этой странице вы нашли то, что искали. Вас наверняка заинтересуют и похожие операции: поиск по слову в ячейках и удалить список слов из текста.