Процедуры из статьи доступны в !SEMTools Lite и Pro, часть — только в Pro.
Вытащить слово из текста ячейки Excel можно формулами: первое слово — =ЛЕВСИМВ(A2;ПОИСК(" ";A2&" ")-1), последнее в Excel 365 — =ТЕКСТПОСЛЕ(A2;" ";-1;;;A2), слово по номеру (например, третье) — =СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(A2;" ";ПОВТОР(" ";100));(3-1)*100+1;100)). Слова по списку, по паттерну, по частям речи или маркерам извлекают процедуры !SEMTools.
Меню потребуется в случаях, когда нужно извлечь, вытащить слова из ячеек.
При разработке надстройки словам уделялось максимальное внимание. Можно извлекать из строк слова по множеству условий, о которых ниже.
Извлечь слова целиком
- извлечь одно слово во всем диапазоне;
- извлечь собственный список слов;
- встроенные маркеры: определенные части речи, маркеры покупки, аренды, отзывов.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Запрос | Слова из списка | Список | |
| 2 | купить диван и кресло | диван кресло=ОБЪЕДИНИТЬ(" ";ИСТИНА;ЕСЛИ(ЕЧИСЛО(ПОИСК(" "&$D$2:$D$4&" ";" "&A2&" "));$D$2:$D$4;"")) | диван | |
| 3 | стол обеденный | стол=ОБЪЕДИНИТЬ(" ";ИСТИНА;ЕСЛИ(ЕЧИСЛО(ПОИСК(" "&$D$2:$D$4&" ";" "&A3&" "));$D$2:$D$4;"")) | кресло | |
| 4 | стул | =ОБЪЕДИНИТЬ(" ";ИСТИНА;ЕСЛИ(ЕЧИСЛО(ПОИСК(" "&$D$2:$D$4&" ";" "&A4&" "));$D$2:$D$4;"")) | стол |
Пробелы вокруг слов не дают найти «стол» внутри «столовая». ОБЪЕДИНИТЬ с ИСТИНА пропускает пустые результаты. Подробнее — в статье извлечь из текста список слов.
В английском Excel: =TEXTJOIN(" ",TRUE,IF(ISNUMBER(SEARCH(" "&$D$2:$D$4&" "," "&A2&" ")),$D$2:$D$4,""))
Извлечь слова по паттернам
Слова, содержащие текстовый паттерн или список паттернов:
- в начале,
- в конце,
- в любом месте слова.
Паттерны могут выражаться не только простыми произвольными сочетаниями символов — можно задавать их в виде регулярных выражений (для сложных случаев).
| A | B | |
|---|---|---|
| 1 | Текст | Слово с «кож» |
| 2 | диван из экокожи | экокожи=РЕГИЗВЛЕЧЬ(A2;"\S*кож\S*") |
| 3 | кожаный диван | кожаный=РЕГИЗВЛЕЧЬ(A3;"\S*кож\S*") |
| 4 | тканевый диван | #Н/Д=РЕГИЗВЛЕЧЬ(A4;"\S*кож\S*") |
\S* — любые символы, кроме пробела, поэтому шаблон захватывает слово целиком вокруг «кож». Если такого слова нет, функция вернёт #Н/Д.
А иногда требуется извлечь слова, содержащие заглавные буквы. Это можно сделать с помощью синтаксиса регулярных выражений, но можно и проще: при помощи !SEMTools задача решается буквально в один клик.
| A | B | |
|---|---|---|
| 1 | Текст | Первое слово с заглавной |
| 2 | купить диван IKEA | IKEA=РЕГИЗВЛЕЧЬ(A2;"\S*[A-ZА-ЯЁ]\S*") |
| 3 | отзывы о телефоне Samsung Galaxy | Samsung=РЕГИЗВЛЕЧЬ(A3;"\S*[A-ZА-ЯЁ]\S*") |
РЕГИЗВЛЕЧЬ (Excel 365) различает регистр и возвращает первое совпадение, а с третьим аргументом 1 — все совпадения, они разольются в соседние ячейки. В Excel 2010–2024 используйте REGEXEXTRACT из !SEMTools: с двумя аргументами она работает так же, а с третьим аргументом ИСТИНА возвращает все совпадения в одной ячейке через разделитель из четвёртого аргумента.
Извлечь слова по положению в ячейке
- извлечь первое слово, первые два, три или любое задаваемое пользователем количество первых слов;
- аналогично, последние одно, два, три или N слов.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Текст | Первые 2 слова | Последние 2 слова | 3-е слово |
| 2 | купить угловой диван недорого | купить угловой=ТЕКСТДО(A2;" ";2;;;A2) | диван недорого=ТЕКСТПОСЛЕ(A2;" ";-2;;;A2) | диван=ИНДЕКС(ТЕКСТРАЗД(A2;" ");3) |
| 3 | диван кровать | диван кровать=ТЕКСТДО(A3;" ";2;;;A3) | диван кровать=ТЕКСТПОСЛЕ(A3;" ";-2;;;A3) | #ССЫЛКА!=ИНДЕКС(ТЕКСТРАЗД(A3;" ");3) |
Последний аргумент ТЕКСТДО и ТЕКСТПОСЛЕ — что вернуть, если пробелов меньше: здесь вся ячейка. ИНДЕКС за пределами слов даёт ошибку — оберните в ЕСЛИОШИБКА. Функции есть в Excel 365.
В английском Excel: =TEXTBEFORE(A2," ",2,,,A2)
Формулы для извлечения слов
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Текст | Первое | Последнее | Третье |
| 2 | купить диван угловой недорого | купить=ЛЕВСИМВ(A2;ПОИСК(" ";A2&" ")-1) | недорого=ТЕКСТПОСЛЕ(A2;" ";-1;;;A2) | угловой=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(A2;" ";ПОВТОР(" ";100));(3-1)*100+1;100)) |
| 3 | диван-кровать в спальню | диван-кровать=ЛЕВСИМВ(A3;ПОИСК(" ";A3&" ")-1) | спальню=ТЕКСТПОСЛЕ(A3;" ";-1;;;A3) | спальню=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(A3;" ";ПОВТОР(" ";100));(3-1)*100+1;100)) |
Формула для N-го слова заменяет каждый пробел на 100 пробелов, берёт N-й «отрезок» по 100 символов и убирает лишние пробелы. Число 3 в (3-1) — номер слова. Работает в любой версии, если слова короче 100 символов. В Excel 365 проще: =ИНДЕКС(ТЕКСТРАЗД(A2;" ");3).
В английском Excel: =TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",100)),(3-1)*100+1,100))
Извлечение слов — частые вопросы
Как вытащить слово из текста в Excel?
Первое — ЛЕВСИМВ + ПОИСК, последнее — ТЕКСТПОСЛЕ с номером −1, N-е — =ИНДЕКС(ТЕКСТРАЗД(A1;" ");N) в Excel 365.
Как извлечь слова из своего списка?
Подробно — в статье извлечь из текста список слов.
Как извлечь первые N слов?
Смотрите как извлечь первые слова из ячеек.
Извлечь слова по списку, паттерну, положению, части речи или маркерам покупки и аренды сразу во всём диапазоне можно в пару кликов с !SEMTools.
!SEMTools решает эту и сотни других задач в пару кликов!
Итоги: как извлечь слова в Excel
Надеюсь, на этой странице вы нашли то, что искали. Слова по положению достают формулы, а слова по списку, паттерну и частям речи — процедуры раздела. Вас наверняка заинтересуют и похожие разделы: удалить слова и найти слова.