Ir para o conteúdo principal

Como verificar se a célula contém um dos vários valores no Excel?

Supondo que você tenha uma lista de strings de texto na coluna A, agora, você deseja testar cada célula se ela contém um dos vários valores com base em outro intervalo D2: D7. Se ele contiver algum texto específico em D2: D7, exibirá True, caso contrário, exibirá False conforme a captura de tela a seguir. Neste artigo, falarei sobre como identificar uma célula se ela contiver um dos vários valores em outro intervalo.


Verifique se uma célula contém um dos vários valores de uma lista com fórmulas

Para verificar se o conteúdo de uma célula contém algum dos valores de texto em outro intervalo, as seguintes fórmulas podem ajudá-lo, faça o seguinte:

Insira a fórmula abaixo em uma célula em branco onde deseja localizar o resultado, B2, por exemplo, em seguida, arraste a alça de preenchimento para baixo até as células às quais deseja aplicar esta fórmula e se a célula tiver algum dos valores de texto em outro intervalo específico, se tornará True, caso contrário, se tornará False. Veja a imagem:

=SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$7,A2)))>0

Dicas:

1. Se desejar usar “Sim” ou “Não” para indicar o resultado, aplique a seguinte fórmula e obterá o seguinte resultado conforme necessário, consulte a captura de tela:

=IF(SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$6,A2))),"Yes","No")

2. Nas fórmulas acima, D2: D7 é o intervalo de dados específico no qual você deseja verificar a célula, e A2 é a célula que você deseja verificar.


Mostra as correspondências se a célula contiver um dos vários valores de uma lista com fórmulas

Às vezes, você pode querer verificar se uma célula contém um valor na lista e, em seguida, retornar esse valor; se vários valores corresponderem, todos os valores correspondentes na lista serão exibidos conforme a captura de tela abaixo. Como você poderia resolver essa tarefa no Excel?

Para exibir todos os valores correspondentes se a célula contiver um do texto específico, aplique a fórmula abaixo:

=TEXTJOIN(", ", TRUE, IF(COUNTIF(A2, "*"&$D$2:$D$7&"*"), $D$2:$D$7, ""))

Note: Na fórmula acima, D2: D7 é o intervalo de dados específico no qual você deseja verificar a célula, e A2 é a célula que você deseja verificar.

Então aperte Ctrl + Shift + Enter juntas para obter o primeiro resultado e, em seguida, arraste a alça de preenchimento até as células às quais deseja aplicar esta fórmula, veja a captura de tela:

Dicas:

A função TEXTJOIN acima está disponível apenas para Excel 2019 e Office 365, se você tiver versões anteriores do Excel, deve aplicar a fórmula abaixo:

=IFERROR(INDEX($D$2:$D$7, SMALL(IF(COUNTIF($A2, "*"&$D$2:$D$7&"*"), MATCH(ROW($D$2:$D$7), ROW($D$2:$D$7)), ""), COLUMNS($F$1:F1))), "")

Note: Na fórmula acima, D2: D7 é o intervalo de dados específico no qual você deseja verificar a célula, e A2 é a célula que você deseja verificar.

Então aperte Ctrl + Shift + Enter juntas para obter o primeiro resultado e, em seguida, arraste a célula da fórmula para o lado direito até que a célula em branco seja exibida e, em seguida, continue arrastando a alça de preenchimento para baixo para outras células, e todos os valores correspondentes foram exibidos conforme a captura de tela abaixo:


Destaque as correspondências se a célula contiver um dos vários valores de uma lista com um recurso útil

Se você quiser destacar uma cor de fonte específica para os valores correspondentes, se a célula contiver um dos vários valores de outra lista, nesta seção, apresentarei um recurso fácil, Marcar palavra-chave of Kutools for Excel, com esse utilitário, você pode destacar uma ou mais palavras-chave específicas de uma vez dentro das células.

Nota:Para aplicar estes Marcar palavra-chave recursos, em primeiro lugar, você deve baixar o Kutools for Excele, em seguida, aplique os recursos de forma rápida e fácil.

Depois de instalar Kutools for Excel, faça o seguinte:

1. Clique Kutools > Texto > Marcar palavra-chave, veja a captura de tela:

2. No Marcar palavra-chave caixa de diálogo, execute as seguintes operações:

  • Selecione o intervalo de dados que você deseja destacar os textos correspondentes do Variação caixa de texto;
  • Selecione as células que contêm as palavras-chave que você deseja realçar com base, você também pode inserir as palavras-chave manualmente (separadas por vírgula) no Palavra-chave caixa de texto
  • Por fim, você deve especificar uma cor de fonte para destacar os textos por cheque Marque as cores da palavra-chave opção. 

3. Então clique Ok botão, todos os textos correspondentes foram destacados como a imagem abaixo mostrada:


Mais artigos relativos:

  • Compare duas ou mais strings de texto no Excel
  • Se você quiser comparar duas ou mais strings de texto em uma planilha com ou sem distinção entre maiúsculas e minúsculas, conforme a captura de tela a seguir mostrada, neste artigo, falarei sobre algumas fórmulas úteis para você lidar com essa tarefa no Excel.
  • Se a célula contiver texto, será exibida no Excel
  • Se você tiver uma lista de sequências de texto na coluna A e uma linha de palavras-chave, agora, você precisa verificar se as palavras-chave aparecem na sequência de texto. Se as palavras-chave aparecem na célula, exibindo-a, caso contrário, a célula em branco é exibida conforme a captura de tela a seguir.
  • Encontre e substitua vários valores no Excel
  • Normalmente, o recurso Localizar e Substituir pode ajudá-lo a localizar um texto específico e substituí-lo por outro, mas, às vezes, você pode precisar localizar e substituir vários valores simultaneamente. Por exemplo, para substituir todo o texto "Excel" por "Excel 2019", "Outlook" por "Outlook2019" e assim por diante, conforme a captura de tela abaixo mostrada. Neste artigo, vou apresentar uma fórmula para resolver essa tarefa no Excel.

Melhores ferramentas de produtividade de escritório

Recursos mais comuns: Encontre, destaque ou identifique duplicatas   |  Excluir linhas em branco   |  Combine colunas ou células sem perder dados   |   Rodada sem Fórmula ...
Super pesquisa: VLookup de múltiplos critérios    VLookup de múltiplos valores  |   VLookup em várias planilhas   |   Pesquisa Difusa ....
Lista suspensa avançada: Crie rapidamente uma lista suspensa   |  Lista suspensa de dependentes   |  Lista suspensa de seleção múltipla ....
Gerenciador de colunas: Adicione um número específico de colunas  |  Mover colunas  |  Alternar status de visibilidade de colunas ocultas  |  Compare intervalos e colunas ...
Recursos em destaque: Foco da Grade   |  Vista de Design   |   Grande Barra de Fórmula    Gerenciador de pastas de trabalho e planilhas   |  Biblioteca (Auto texto)   |  Data Picker   |  Combinar planilhas   |  Criptografar/Descriptografar Células    Enviar e-mails por lista   |  Super Filtro   |   Filtro Especial (filtro negrito/itálico/tachado...) ...
15 principais conjuntos de ferramentas12 Texto Ferramentas (Adicionar texto, Remover Personagens, ...)   |   50+ de cores Tipos (Gráfico de Gantt, ...)   |   Mais de 40 práticos Fórmulas (Calcule a idade com base no aniversário, ...)   |   19 Inclusão Ferramentas (Insira o código QR, Inserir imagem do caminho, ...)   |   12 Conversão Ferramentas (Números para Palavras, Conversão de moedas, ...)   |   7 Unir e dividir Ferramentas (Combinar linhas avançadas, Dividir células, ...)   |   ... e mais

Aprimore suas habilidades de Excel com o Kutools para Excel e experimente uma eficiência como nunca antes. Kutools para Excel oferece mais de 300 recursos avançados para aumentar a produtividade e economizar tempo.  Clique aqui para obter o recurso que você mais precisa...

guia kte 201905


Office Tab traz interface com guias para o Office e torna seu trabalho muito mais fácil

  • Habilite a edição e leitura com guias em Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
  • Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
  • Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse para você todos os dias!
Comments (56)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hello,

I have spreadsheet that have some cell with following test $5655 (BIT001), $4445 (BIT002). I would like to extract every BITxxx and display in another cell with BIT001, BIT002. I'm using MID and FIND but it work only with one BIT001 and BIT002 seems to not search. How would you do it guys?
This comment was minimized by the moderator on the site
You can use text to column > Deliminate > ("("), - I will spit the text from Open Bracket .. like BIT001), then you can remove the closing bracket with a find and replace option.
This comment was minimized by the moderator on the site
Hi all,

Any suggestions...

I have a VALUE next to the SPECIFIC TEXT column F. I need a RESULT in Column B with the VALUE next to SPECIFIC TEXT.
This comment was minimized by the moderator on the site
In option "Display the matches if cell contains one of several values from a list with formulas" I'm having issues in "Result" when the "Specific Text" values is similar to others.

For example, if I have in "Specific Text" the values 'remove' and 'remove lines', I need to get in "Result" the correct value.

Thanks
This comment was minimized by the moderator on the site
I find an option in other webpage:

=TEXTJOIN(" ",1,IF(ISNUMBER(FIND(" "&$F$2:$F$6&" "," "&B2&" ")),$F$2:$F$6,""))
This comment was minimized by the moderator on the site
In the option "Display the matches if cell contains one of several values from a list with formulas" I'm having issues if the word to search is similar to other word. For example if in "Specif text" I have: Remove and Remove line, I need to get/recognise in "Result" the different values ("Remove" for 1 line and "Remove line" in other line). The problem is that always I'm getting "Remove"
This comment was minimized by the moderator on the site
Bonjour, j'essaye cette formule sur une très grande liste de mot et la recherche fait un contient, mais pas au mot entier.
Vous avez une solution ?

Par exemple, le mot de ma liste à rechercher est "aire". Cependant, le mot "horaire" m'indique vrai... avec la formule.
Alors oui "horaire" contient "aire" mais pas en mot entier...

Autre exemple : le mot de ma liste à rechercher est "ri". Cependant, le mot "clé dynamométrique" m'indique vrai...

Merci
This comment was minimized by the moderator on the site
Hello, Greg,
Sorry, I can't understand you clearly.
Could you explain your problem in English? Or you can upload a file or screenshot of your problem here.
Thank you!
This comment was minimized by the moderator on the site
=IF(SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$6,A2))),"Yes","No")

Is there a way of amending the formula to return the number of words appear in the text examined?

e.g. returned answer is not whether one of three words (a given list) are found, but if 1, 2, or 3 words are found.

Hope this makes sense................

Thanks



Alan
This comment was minimized by the moderator on the site
Hello, Alan,
To solve your problem, the following array formula may help you: (Note: After inserting the formula, press Ctrl + Shift + Enter keys together to get the first result.)
=SUM((LEN(A2)-LEN(SUBSTITUTE(A2,$D$2:$D$5,"")))/LEN($D$2:$D$5))


Please have a try, hope it can help you!
This comment was minimized by the moderator on the site
help someone. I have a spreadsheet that has example below. I need a formula that will search and return the following based on what is in the cell

A-05-002F: Air Conditioning Unit or Heat Pump Split System - Qtrly Filter
A-11-025: Air Handler Unit - Annual
E-42-001: Emergency Generators - Weekly #4

The above is in C2 I want E2 to display Q if cell contains Qtrly, A if cell contains Annual, W if cell contains weekly
This comment was minimized by the moderator on the site
Hi, Lynda,
To solve your problem, please apply the following formula:
=IF(ISERROR(SEARCH("Qtrly",C2)),IF(ISERROR(SEARCH("Annual",C2)),IF(ISERROR(SEARCH("weekly",C2)),"","W"),"A"), "Q")

Please have a try, hope it can help you!
This comment was minimized by the moderator on the site
Correction the above information is in C2 C3 C4
This comment was minimized by the moderator on the site
RE: above example  =IFERROR(INDEX($D$2:$D$7, SMALL(IF(COUNTIF($A2, "*"&$D$2:$D$7&"*"), MATCH(ROW($D$2:$D$7), ROW($D$2:$D$7)), ""), COLUMNS($F$1:F1))), "")I keep error with Excel 2010.  Same with example for Excel 2019, in Excel 2019 on a different PC. Tried the one  Is it Wish it was available in my Kutools  
This comment was minimized by the moderator on the site
Multiple search value in a single cell, and return as a Common Value
This comment was minimized by the moderator on the site
Is it possible to do this with a partial match of the keywords? This method is only an exact match
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations