Fórmula genérica
=LOWER(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"("," "),")"," "),"-"," "),":"," "),";"," "),"!"," "),","," "),"."," ")))
Resumo
Para remover parte da complexidade natural do texto (tirar pontuação, normalizar maiúsculas e minúsculas, remover espaços extras), você pode usar uma fórmula baseada na função SUBSTITUTE, com a ajuda das funções TRIM e LOWER.
Contexto
Pode haver momentos em que você precise remover parte da variabilidade do texto antes de outro processamento. Um exemplo é quando você deseja contar palavras específicas dentro de strings de texto maiores. Como o Excel não oferece suporte para expressões regulares, você não pode construir correspondências precisas. Por exemplo, se você quiser contar quantas vezes a palavra "raposa" aparece em uma célula, acabará contando "raposas". Você pode procurar por "raposa" (com um espaço), mas isso falhará com "raposa" ou "raposa". Uma solução alternativa é simplificar o texto primeiro com uma fórmula em uma coluna auxiliar e, em seguida, executar contagens na versão simplificada. O exemplo nesta página mostra uma maneira de fazer isso.
Explicação
A fórmula mostrada neste exemplo usa uma série de funções SUBSTITUTE aninhadas para remover parênteses, hífens, dois pontos, ponto e vírgula, pontos de exclamação, vírgulas e pontos. O processo é executado de dentro para fora, com cada SUBSTITUTO substituindo um caractere por um único espaço e, em seguida, transferindo para o próximo SUBSTITUTO. O SUBSTITUTO mais interno remove os parênteses esquerdos e o resultado é passado para o próximo SUBSTITUTO, que remove os parênteses direitos e assim por diante.
Na versão abaixo, as quebras de linha foram adicionadas para facilitar a leitura e para facilitar a edição das substituições. O Excel não se preocupa com quebras de linha em fórmulas, portanto, você pode usar a fórmula no estado em que se encontra.
= LOWER( TRIM( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( A1, "("," "), ")"," "), "-"," "), ":"," "), ";"," "), "!"," "), ","," "), "."," ")))
Depois que todas as substituições são concluídas, o resultado é executado por meio de TRIM para normalizar os espaços e, em seguida, a função LOWER para forçar todo o texto para minúsculas.
Observação: você precisará ajustar as substituições reais de acordo com seus dados.
Adicionando um espaço à esquerda e à direita
Em alguns casos, você pode querer adicionar um caractere de espaço no início e no final do texto limpo. Por exemplo, se você deseja contar palavras com precisão, você pode querer procurar a palavra entre espaços (ou seja, pesquisar por "raposa", "mapa") para evitar correspondências falsas. Para adicionar um espaço à esquerda e à direita, basta concatenar um espaço ("") no início e no final:
=" "&formula&" "
Onde "fórmula" é a fórmula mais longa acima.
