Как найти повторяющийся текст подстроки между двумя столбцами в Excel?

Question

У меня есть два столбца данных. Столбец 1 содержит список доменов адресов электронной почты, таких как gmail.com, yahoo.com, mydomain.edu, myotherdomain.org и т.д. Во втором столбце содержится список полных адресов электронной почты, напр. myemailaddress@domain.com .

Я хочу выделить каждый адрес электронной почты в столбце B, у которого есть совпадение подстроки одного из доменов в столбце A.

Например, в столбце BI есть адрес test123@domain1.com и строка domain1.com в столбце A. Этот адрес в столбце B должен быть выделен. Если у меня есть anothertest@domain2.com в столбце B , но строка domain2 не отображается в столбце А, адрес электронной почты , не должен быть выделен.

Как я могу это сделать?

Answer 1

Используйте условное форматирование и вставьте эту формулу, чтобы определить ячейки для форматирования

=IFNA(MATCH(MID($B1,SEARCH("@",$B1)+1,999),A:A,0)>0,FALSE)

Вы можете дважды проверить формулу, поместив ее в столбец emtpy и проверить ее результат

Примечание. В версиях Excel, предшествующих 2013 г. =IFNA() необходимо заменить на =IF(ISNA())

=IF(ISNA(MATCH(MID($B1,SEARCH("@",$B1)+1,999),A:A,0)),FALSE,TRUE)

---

Синтаксис объясняется от внутренней к внешней части

• SEARCH("@",$B1) будет определять положение знака @ для каждой ячейки столбца B.
  Результат для примера выше будет 8 и 5.

• MID($B1,[...]+1,999) будет вырезать все прямо из @ char. Обратите внимание, что мы должны добавить +1 к стартовой позиции, иначе будет добавлен знак @. Результатом в нашем примере будет domain1.com и domain2.com .

  Параметр длины не может быть пропущен, поэтому я выбрал 999, чтобы быть безопасным.

• MATCH([...],A:A,0) будет искать строку домена в столбце A и возвращать индекс строки. (Черт возьми, нам нужно ложь или правда). Пример результатов будет 2 и #NA, так как второе письмо не имеет соответствующего домена в столбце A.

• ISNA([...])) используется в качестве небольшого трюка для преобразования индексов строк в true или false. Если у нас есть правильное совпадение, будет получен индекс строки, и это не #NA, поэтому мы получим false . Но если внутренняя формула приведет к #NA, формула ISNA даст нам истину.

  Подождите, но это переключается! Да, нам нужно наоборот.

• =IF([...],FALSE,TRUE) просто переключает true и false, чтобы мы могли использовать его в качестве входных данных для условной формулы

Answer 2

Более компактная формула для правила условного форматирования:

=COUNTIF(A:A,MID(B1,FIND("@",B1)+1,999))

Эта формула работает следующим образом, предполагая, что первое электронное письмо находится в ячейке B1:

  FIND("@",B1)+1           :  finds the first character of the domain name in 
                              an email address (which begins one character 
                              beyond the @)

  MID(B1,FIND(...)+1,999)  :  extracts the full domain name from the email 
                              address

  COUNTIF(A:A, MID(...))   :  counts up the number of matches in column A of
                              the domain name in the email address (which  will 
                              be at most 1 if the domain list contains no 
                              duplicates).

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

Механизм условного форматирования Excel будет обрабатывать как ИСТИНА 1, полученную по формуле для сопоставленного доменного имени, и как ЛОЖЬ 0 для несоответствия. (Даже если в списке доменов есть дубликаты и, следовательно, их число больше 1, формула все равно будет работать.)

Эта формула совместима с Excel 2007 и более поздними версиями. Следующая измененная формула должна быть обратно совместима со всеми предыдущими версиями Excel. Предполагается, что список доменов находится в диапазоне A1:A10.

=SUM(IF($A$1:$A$10=MID(B1,FIND("@",B1)+1,999),1,0))>0