Удалить из текста ячеек слова из списка в Excel 365 можно формулой =ОБЪЕДИНИТЬ(" ";ИСТИНА;ФИЛЬТР(ТЕКСТРАЗД(A2;" ");ЕНД(ПОИСКПОЗ(ТЕКСТРАЗД(A2;" ");$D$2:$D$4;0));"")): ТЕКСТРАЗД раскладывает фразу на слова, ПОИСКПОЗ проверяет каждое по списку D2:D4, ФИЛЬТР оставляет только те, которых в списке нет, а ОБЪЕДИНИТЬ склеивает их обратно. Удаляются только целые слова, части других слов не страдают. В любой версии Excel удалить список слов из тысяч ячеек можно процедурой !SEMTools.
Периодически возникает необходимость удалить слова из текста в ячейках. У вас есть список слов, и нужно удалить их все из списка фраз, если они в нем присутствуют. Зачастую такое приходит в голову после того, как произведен поиск в Excel по словам, и/или удалось извлечь из текста список слов и понять, что они — лишние.
- Например, вы — контент-менеджер интернет-магазина. Ваш каталог представляет собой Excel-файл. В нём товары представляют собой массив ячеек, содержащий названия продукта и фирмы-производителя. У вас есть список фирм-производителей, а ваша задача – удалить их и оставить только названия продуктов.
- Или у вас каталог автомобилей в виде одной строки – марка и модель. Вы хотите разбить его на марки и модели, и все что у вас есть – список из ~30 марок.
В обоих случаях можно было бы воспользоваться массовой заменой нескольких значений в ячейках Excel на пустоту. Но это, очевидно, займет довольно много времени, особенно, если каталог большой и каждая замена не происходит моментально.
Для таких случаев может пригодиться макрос удаления списка слов из ячеек. Макрос находится в группе «УДАЛИТЬ» в меню «Удалить слова».
Как и все макросы, работающие со словами, он определяет целые слова и удаляет строго только их, пропуская простые вхождения. Это позволяет избежать непреднамеренных удалений частей слов и «повреждения» данных. Однако, если в вашем списке не только слова, но и словосочетания, он вам не подойдет. Смотрите как удалить список фраз из ячеек.
Удалить слова из списка формулой
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Название товара | Без брендов | Список слов | |
| 2 | Samsung Galaxy S24 | Galaxy S24=ОБЪЕДИНИТЬ(" ";ИСТИНА;ФИЛЬТР(ТЕКСТРАЗД(A2;" ");ЕНД(ПОИСКПОЗ(ТЕКСТРАЗД(A2;" ");$D$2:$D$4;0));"")) | Samsung | |
| 3 | смартфон Apple iPhone 15 | смартфон iPhone 15=ОБЪЕДИНИТЬ(" ";ИСТИНА;ФИЛЬТР(ТЕКСТРАЗД(A3;" ");ЕНД(ПОИСКПОЗ(ТЕКСТРАЗД(A3;" ");$D$2:$D$4;0));"")) | Apple | |
| 4 | чехол для Xiaomi | чехол для=ОБЪЕДИНИТЬ(" ";ИСТИНА;ФИЛЬТР(ТЕКСТРАЗД(A4;" ");ЕНД(ПОИСКПОЗ(ТЕКСТРАЗД(A4;" ");$D$2:$D$4;0));"")) | Xiaomi | |
| 5 | наушники беспроводные | наушники беспроводные=ОБЪЕДИНИТЬ(" ";ИСТИНА;ФИЛЬТР(ТЕКСТРАЗД(A5;" ");ЕНД(ПОИСКПОЗ(ТЕКСТРАЗД(A5;" ");$D$2:$D$4;0));"")) |
ПОИСКПОЗ не различает регистр, поэтому «apple» и «Apple» удалятся одинаково. Последний аргумент ФИЛЬТР ("") не даёт формуле вернуть ошибку, если из ячейки удалены все слова.
В английском Excel: =TEXTJOIN(" ",TRUE,FILTER(TEXTSPLIT(A2," "),ISNA(MATCH(TEXTSPLIT(A2," "),$D$2:$D$4,0)),""))
Удалить одно слово в любой версии Excel
Если слов немного, а ТЕКСТРАЗД нет, используйте ПОДСТАВИТЬ с пробелами по краям, вкладывая её столько раз, сколько слов в списке: =СЖПРОБЕЛЫ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(" "&A2&" ";" Samsung ";" ");" Apple ";" ")). ПОДСТАВИТЬ различает регистр — при необходимости приведите текст к одному регистру функцией СТРОЧН.
Как удалить из текста список слов с помощью !SEMTools?
Все очень просто. Выделяем список фраз, вызываем макрос из меню, он предлагает выделить список слов, выделяем его и жмем «ОК». Вуаля!
Важно! Слова удаляются в исходном диапазоне и навсегда. Рекомендуется копировать столбец, оставляя исходные данные, как показано на видеоинструкции ниже, если не уверены в результате:
Удалить слова во всех словоформах
Если стоит задача удалить слова во всех их словоформах, а перебирать их вручную не очень хочется, есть пара вариантов:
- Можно воспользоваться макросом удаления слов по условию "начинается с". Такой вариант менее предпочтителен, если вы не уверены, что в массиве нет других слов, начинающихся с указанной последовательности. И точно непригоден для удаления коротких слов.
- Можно воспользоваться модулем морфологии и быстро просклонять слова в Excel автоматически, не прикладывая лишних усилий. Но и тут есть примечание. Модуль Морфология в !SEMTools на текущий момент работает только с существительными и прилагательными.
Почему слова из списка не удаляются
- Слово стоит рядом со знаком препинания. «Samsung,» и «Samsung» — разные слова. Сначала удалите пунктуацию.
- В списке словосочетание. Формула и процедура удаляют отдельные слова. Для фраз используйте удаление списка фраз.
- Удалилась часть слова. Использовали «Найти и заменить» без пробелов вокруг слова: замена «ум» на пустоту испортит «умный» и «костюм».
- Формула даёт #ИМЯ?. ТЕКСТРАЗД есть только в Microsoft 365 и Excel 2024 — используйте ПОДСТАВИТЬ или процедуру !SEMTools.
Удаление слов из текста — частые вопросы
Как удалить слова из текста в Excel?
Одно слово — через Ctrl+H или ПОДСТАВИТЬ с пробелами по краям. Список слов — формулой с ТЕКСТРАЗД и ФИЛЬТР (Excel 365) или процедурой !SEMTools «Удалить слова из списка».
Как удалить из названий список брендов?
Запишите бренды в отдельный столбец и используйте формулу из статьи или процедуру !SEMTools, указав этот столбец как список слов.
Удаляются ли части слов?
Нет: и формула, и процедура удаляют только целые слова. «Apple» не удалится из «Applewood».
Как удалить слова во всех словоформах?
Через удаление слов по условию «начинается с» или предварительно просклоняв слова модулем морфологии !SEMTools.
Удалить из тысяч ячеек любые слова из вашего списка — только целые слова, без повреждения остального текста — можно в пару кликов с !SEMTools.
!SEMTools решает эту и сотни других задач в пару кликов!
Итоги: как удалить список слов из текста
| Задача | Способ |
|---|---|
| Одно слово | ПОДСТАВИТЬ с пробелами или Ctrl+H |
| Список слов (Excel 365) | ОБЪЕДИНИТЬ + ФИЛЬТР + ТЕКСТРАЗД + ПОИСКПОЗ |
| Список слов в любой версии | Процедура !SEMTools |
| Список фраз | Удаление списка фраз !SEMTools |
Надеюсь, на этой странице вы нашли то, что искали. Вас наверняка заинтересуют и похожие операции: извлечь слова из списка и удалить повторяющиеся слова.