Fórmula genérica
=(LEN(text)-LEN(SUBSTITUTE(text,word,"")))/LEN(word)
Resumo
Se você precisar contar quantas vezes uma palavra específica (ou qualquer substring) aparece dentro de uma célula, você pode usar uma fórmula que usa SUBSTITUTE e LEN. Na forma genérica da fórmula acima, "texto" representa uma célula que contém texto e "palavra" representa a palavra ou substring sendo contada. No exemplo, estamos usando esta fórmula:
=(LEN(B4)-LEN(SUBSTITUTE(B4,C4,"")))/LEN(C4)
Explicação
B4 é a célula na qual estamos contando as palavras e C4 contém a substring (palavra ou qualquer substring) que você está contando.
SUBSTITUTE remove a substring do texto original e LEN calcula o comprimento do texto sem a substring. Esse número é então subtraído do comprimento do texto original. O resultado é o número de caracteres que foram removidos por SUBSTITUTE.
Finalmente, o número de caracteres removidos é dividido pelo comprimento da substring. Portanto, se uma substring tiver 5 caracteres e houver 10 caracteres faltando depois de removida do texto original, sabemos que a substring apareceu duas vezes no texto original.
Maleta de manuseio
SUBSTITUTE é uma função que faz distinção entre maiúsculas e minúsculas, portanto, corresponderá a maiúsculas e minúsculas ao executar uma substituição. Se você precisar contar as ocorrências em maiúsculas e minúsculas de uma palavra ou substring, use a função UPPER em SUBSTITUTE para converter o texto em maiúsculas antes de executar a substituição:
=(LEN(B4)-LEN(SUBSTITUTE(UPPER(B4),UPPER(C4),"")))/LEN(C4)
Como essa fórmula converte a substring e o texto em maiúsculas antes de realizar a substituição, ela funcionará igualmente bem com texto em qualquer caso.
Problemas de manuseio
Contar palavras no Excel é complicado porque o Excel não oferece suporte a expressões regulares. Como resultado, é difícil direcionar as palavras que você deseja contar exatamente, enquanto ignora substrings e outras correspondências parciais (por exemplo, encontre "raposa", mas não "raposas"). A pontuação e as variações de maiúsculas e minúsculas tornam esse problema bastante desafiador.
Uma solução alternativa é usar outra fórmula em uma coluna auxiliar para "normalizar o texto" como uma primeira etapa. Em seguida, use a fórmula nesta página para contar palavras envolvidas em caracteres de espaço para obter uma contagem precisa (ou seja, você pode procurar por "raposa" no texto normalizado.
Observação: essa abordagem é tão boa quanto o texto normalizado que você é capaz de criar e pode ser necessário ajustar a fórmula de normalização várias vezes para obter o resultado de que precisa.
