Como posso encontrar texto de substring duplicado entre duas colunas no Excel?

Question

Eu tenho duas colunas de dados. A coluna 1 contém uma lista de domínios de endereço de e-mail, como gmail.com, yahoo.com, mydomain.edu, myotherdomain.org etc. A segunda coluna contém uma lista de endereços de e-mail completos, ex. %código%.

Desejo destacar todos os endereços de e-mail na coluna B que tenham uma correspondência de substring de um dos domínios na coluna A.

Por exemplo, na coluna B eu tenho o endereço [email protected] e a string [email protected] na coluna A. Esse endereço na coluna B deve ser destacado. Se eu tiver domain1.com na coluna B, mas a string [email protected] não aparecer na coluna A, o endereço de e-mail não deverá estar destacado.

Como posso fazer isso?

    

Answer 1

Use formatação condicional e insira essa fórmula para determinar quais células serão formatadas

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

Você pode verificar a fórmula colocando-a em uma coluna vazia e verificar seu resultado

Observação:emversõesdoExcelanterioresa2013 =IFNA() deve ser substituído por =IF(ISNA())

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

Sintaxe explicada do interior para as partes externas

• SEARCH("@",$B1) determinará a posição do @ -sign para cada célula da coluna B.
  O resultado para o exemplo acima será 8 e 5.

• MID($B1,[...]+1,999) irá cortar tudo diretamente do @ char. Observe que temos que adicionar +1 à posição inicial ou então o sinal @ será incluído. O resultado no nosso exemplo seria domain1.com e domain2.com .

  O parâmetro length não pode ser omitido, então escolhi 999 para ser seguro.

• MATCH([...],A:A,0) pesquisará a string do domínio na coluna A e retornará o índice da linha. (Droga, precisamos de falsas ou verdadeiras). Exemplos de resultados seriam 2 e #NA, pois o segundo e-mail não possui um domínio correspondente na coluna A.

• ISNA([...])) é usado como um pequeno truque para converter índices de linha em verdadeiro ou falso. Se tivermos uma correspondência válida, um índice de linha é entregue e isso não é #NA, por isso, obtemos false . Mas se a fórmula interna resultar em #NA, a fórmula ISNA nos dará a verdade.

  Espere, mas isso mudou! Sim, precisamos do contrário.

• =IF([...],FALSE,TRUE) apenas troca true e false para que possamos usá-lo como entrada para a fórmula condicional

Answer 2

Uma fórmula mais compacta para a regra de formatação condicional:

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

Esta fórmula funciona da seguinte forma, assumindo que o primeiro email está na célula 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).

Para usar a fórmula, selecione o intervalo a ser formatado, abra a caixa de diálogo de formatação condicional, escolha criar uma nova regra de formatação com base em uma fórmula, cole a fórmula e defina o formato que deseja destacar. correspondência de células.

O mecanismo de formatação condicional do Excel tratará como TRUE o 1 produzido pela fórmula para um nome de domínio correspondente e como FALSE o 0 para um não-correspondência. (Mesmo que haja duplicatas na lista de domínios e, portanto, contagens maiores que 1, a fórmula ainda funcionará.)

Esta fórmula é compatível com o Excel 2007 e versões posteriores. A seguinte fórmula alterada deve ser compatível com versões anteriores de todas as versões do ano anterior do Excel. Ele assume que a lista de domínios está no intervalo A1: A10.

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