Удалить два или более слова в ячейке на основе значения поиска

Question

Я хочу удалить слова "FaKe TaBLet" в B2 в новые желаемые результаты, такие как в C2, который является "большим числом два" с использованием (насколько мне известно) обратного частичного поиска или другой формулы вместо использования "Substitute".

По сути, слова «FakE TaBLet» в B2 необходимо удалить на основе сопоставления списка поиска в A5 (исключая прописные или строчные буквы)

Я знаю, что это может быть легко удалено с помощью формулы = SUBSTITUTE, и я пробовал это. Проблема в том, что у меня есть массивные данные списка поиска, которые отличаются соответственно.

Я пробовал = TRIM(НИЖЕ (ИГРЕК (LOOKUP (1E+100, ПОИСК ($ A $ 6:$ A $ 9, B2,1), $ A $ 6:$ A $ 9), ""))) на основе обучения в https://youtu.be/Xts7oC3agMs, но, по-видимому, это неприменимо, если искомое слово содержит более одного слова.

Может кто-нибудь просветить по этому поводу? любой ответ будет очень признателен!

большое спасибо

 |   A          |         B                    |   C               |
1|              |   *Keyword                   |*Desired result    |
2|              | great FakE TaBLet number two | great number two  |
3|              |                              |                   |
4|              |                              |                   |
5|*lookup list  |                              |                   |
6| Fake         |                              |                   |
7| Fake Tablet  |                              |                   |
8| Fake Tab     |                              |                   |
9| Fake table   |                              |                   |

                     ︙

Answer 1

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

*lookup list
Fake
Fake Tab
Fake table
Fake Tablet

После того как вы внесли это изменение, эта формула возвращает последнюю строку:

=TRIM(SUBSTITUTE(LOWER(B2),TRIM(LOWER(IFERROR(LOOKUP(1E+100,SEARCH($A$6:$A$9,B2,1),$A$6:$A$9),""))),""))

Эта формула возвращает:

great number two

Эта формула имеет ограничение, что только одна строка из столбца A будет найдена и удалена. Вы можете найти и удалить больше, дублируя формулу в большем количестве столбцов, но, учитывая, что ваш список поиска "массивный", вероятно, не практично найти их все. Если вы хотите найти и удалить всех членов массивного списка поиска, я бы использовал VBA.